我正在开发一个民意调查应用程序。SQL模式为:
polls -> * options (poll_id) -> * answers (option_id)
或者:"民意调查有很多选项,选项可以有答案(又称投票)"
每个用户每次投票我只能允许一次投票。这是merge子句(显然不起作用):
merge into answers
using (select count(*)
from answers, options, polls
where answers.option_id = options.id
and options.poll_id = polls.id
and polls.id = {poll_id}
and answers.owner_id = {owner_id}) votes
on (votes = 0)
when matched then
insert into answers values (NULL, {option_id}, {owner_id}, NOW())
如果你从不想更新,那么一个简单的插入就可以了。我认为根本不需要合并:
insert into answers (some_value, option_id, owner_id, last_modified)
select null, {option_id}, {owner_id}, current_timestamp
from dual
where not exists (select 1
from answers a
join options o on o.id = a.option_id
join polls p on p.id = o.poll_id
where a.owner_id = {owner_id}
and p.id = {polls_id}
我明确列出了insert子句的列,因为不这样做的编码风格不好。当然,我只是猜测了你的列名,因为你没有向我们展示你的表定义。
试试这个:
MERGE INTO answers
USING
(SELECT options.id
FROM options, polls
WHERE options.poll_id = polls.id
AND polls.id = {poll_id}
) options
ON (answers.option_id = options.id AND answers.owner_id = {owner_id})
WHEN NOT MATCHED THEN
INSERT INTO answers VALUES (NULL, {option_id}, {owner_id}, SYSDATE)
WHEN MATCHED THEN
-- Do what you need to do for an update