mysql - MySQL sum on condition在 View 中不起作用

在MySQL（v5.5）中，选择视图的所有行时，numNotCast，numInFavor的值始终为0。如果select语句是单独执行的（不在视图中执行），它将按预期工作，并返回对right列中等于“ 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视图的限制吗？您如何在视图中完成此条件求和功能？

最佳答案

然后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

澄清后更新