我想从Node.js运行SQL查询。我目前正在显示四个季度中每个阶段具有特定状态的项目总数。我现在想做的是显示相同的结果,但同时增加一个条件,即会计年度。
这是我四个季度的代码:
SELECT
SUM(CurrentStatus = 'On Hold') onHold_Q,
SUM(CurrentStatus = 'In Progress') inProgress_Q,
SUM(CurrentStatus = 'Not Started') notStarted_Q,
SUM(CurrentStatus = 'Completed') completed_Q,
SUM(CurrentStatus = 'Routine Activity') routineActivity_Q,
SUM(CurrentStatus = 'Done But Not Published') doneButNotPublished_Q
FROM office.officedata
WHERE Quarter in ('Q1', 'Q2', 'Q3', 'Q4') //here I want to add one more condition FiscalYear in ('2016-17')
GROUP BY Quarter
ORDER BY Quarter;
这将输出打印在4个不同的行中,这正是我想要的。但是,当我再添加一个条件时,它确实会执行,但是仅打印一行,因为该年剩下的三个季度中的其他项目都不存在。我想以某种方式在每个会计年度为季度打印4个不同的行。如果一年中该季度没有任何项目,则查询应在行中打印0。我怎样才能做到这一点?我的SQL不那么强壮,如果有人可以帮助我,那将很棒。
您能以编程方式告诉我该怎么做吗?
SELECT years.FiscalYear, quarters.Quarter,
SUM(CurrentStatus = 'On Hold') onHold_Q,
SUM(CurrentStatus = 'In Progress') inProgress_Q,
SUM(CurrentStatus = 'Not Started') notStarted_Q,
SUM(CurrentStatus = 'Completed') completed_Q,
SUM(CurrentStatus = 'Routine Activity') routineActivity_Q,
SUM(CurrentStatus = 'Done But Not Published') doneButNotPublished_Q
FROM (SELECT 2016 FiscalYear UNION SELECT 2017) years
CROSS JOIN (SELECT 'Q1' Quarter UNION SELECT 'Q2' UNION SELECT 'Q3' UNION SELECT 'Q4') quarters
LEFT JOIN office.officedata ON office.FiscalYear = years.FiscalYear AND office.Quarter = quarters.Quarter
GROUP BY years.FiscalYear, quarters.Quarter
ORDER BY years.FiscalYear, quarters.Quarter;
也许必须用COALESCE()包装SUM才能将NULL替换为零。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句