在MySQL (v5.5)中,当选择视图的所有行时,numNotCast、numInFavor的值总是为0。如果select语句单独执行(而不是在视图中),它将按预期工作,返回投票列中等于'notcast‘和'infavor’值的行数的正确计数。
CREATE OR REPLACE
VIEW `Stats` AS
select
sum(case when `p`.`vote` = 'notcast' then 1 else 0 end) AS `numNotCast`,
sum(case when `p`.`vote` = 'infavor' then 1 else 0 end) AS `numInFavor`
from
(`Debate` `d`
join `Participant` `p` ON ((`d`.`debateId` = `p`.`debateId`)))
group by `d`.`debateId`这是对MySQL视图的限制吗?如何在视图中完成这个条件求和功能?
发布于 2014-05-25 15:35:32
那么,正如SQLFiddle所确认的那样,您的陈述是正确的,所以您的问题可能在其他地方。
CREATE OR REPLACE VIEW Stats AS
select
Debate.debateId,
sum(case when Participant.vote='notcast' then 1 else 0 end) as numNotCast,
sum(case when Participant.vote='infavor' then 1 else 0 end) as numInFavor
from
Debate
inner join
Participant on Participant.debateId = Debate.debateId
group by
Debate.debateId澄清后的更新
发布于 2014-05-25 15:35:40
MySQL视图有很多限制,但我不认为这是其中之一。
我觉得奇怪的是,视图的名字是单引号。这可能是允许的,但视图可能不是您想要的。试试这个:
CREATE OR REPLACE VIEW `Stats` AS
select sum(case when `p`.`vote` = 'notcast' then 1 else 0 end) AS `numNotCast`
from `Debate` `d` join
`Participant` `p`
ON `d`.`debateId` = `p`.`debateId`
group by `d`.`debateId`;顺便说一句,在MySQL中,您可以将select简化为:
select sum(p.vote = 'notcast') as numNotCasthttps://stackoverflow.com/questions/23856927
复制相似问题