我正在尝试获取提交的总数，我想要2个结果，1个具有特定状态的提交，1个具有所有状态：
SELECT submissions.campid,
COUNT(submissions.id) AS subsCountTotal,
(SELECT COUNT(submissions.id)
FROM submissions,campaings
WHERE submissions.campid = campaings.id
AND campaings.status = 1
and submissions.time >= campaings.startdate
and submissions.status = 10
GROUP BY submissions.campid) AS subsCountngood
FROM submissions, campaings
WHERE submissions.campid = campaings.id
AND campaings.status = 1
and CASE
WHEN campaings.enddate = '0000-00-00' THEN submissions.time >= campaings.startdate
ELSE submissions.time <= campaings.enddate
END
GROUP BY submissions.campid `
任何帮助将不胜感激：）
谢谢您。
最佳答案
您的案例应该会导致错误（或者这是一个特定于mysql的扩展）？
你只需要：
SELECT submissions.campid,
COUNT(*) AS subsCountTotal,
SUM(CASE WHEN submissions.status = 10 THEN 1 ELSE 0 END) AS subsCountngood
FROM submissions, campaings
WHERE submissions.campid = campaings.id
AND campaings.status = 1
AND
( submissions.time <= campaings.enddate
OR
(campaings.enddate = '0000-00-00' AND submissions.time >= campaings.startdate)
)
GROUP BY submissions.campid