在 SELECT 语句中计算位置和如果

Question

嗨，我正在尝试用mysql编写堆叠条形图，首先我需要正确查询，并需要一些帮助。

目前我有：

SELECT
COUNT(TargetDate) AS Planned,
COUNT(StartDate) AS Completed,
MONTHNAME(TargetDate) AS `Month`,
StartDate,
AssessmentStatus
FROM AuditCycleAssessments
WHERE (Year(TargetDate) = Year(now()))
GROUP BY MONTHNAME(TargetDate)
ORDER BY Month(TargetDate)

这工作得很好，看起来像屏幕截图

但是我需要更改第 3 行以包含其自己的 where 语句，例如......

  (COUNT(StartDate) if AssessmentStatus=3) AS Completed,

所以第一，有人可以帮助我实现这一目标吗？

谢谢

Answer 1

使用 CASE 语句执行条件COUNT

SELECT
    COUNT(TargetDate) AS Planned,
    COUNT(CASE WHEN AssessmentStatus=3 THEN StartDate END) AS Completed,
    MONTHNAME(TargetDate) AS `Month`,
    StartDate,
    AssessmentStatus
FROM AuditCycleAssessments
WHERE (Year(TargetDate) = Year(now()))
GROUP BY MONTHNAME(TargetDate)
ORDER BY Month(TargetDate)

Answer 2

检查如何写 IF 但是在这里你不能写是否应该使用 CASE

SELECT
COUNT(TargetDate) AS Planned,
COUNT(CASE WHEN AssessmentStatus=3 THEN StartDate END) AS Completed,
MONTHNAME(TargetDate) AS `Month`,
StartDate,
AssessmentStatus
FROM AuditCycleAssessments
WHERE (Year(TargetDate) = Year(now()))
GROUP BY MONTHNAME(TargetDate)
ORDER BY Month(TargetDate)

Answer 3

类似以下内容的内容可能会为您服务，未经测试，但它应该可以工作。

Sum(IIf([AssessmentStatus]=3 AND [StartDate] NOT IS NULL,1,0)) AS Completed,