在单个查询中添加多个where子句
在单个查询中添加多个where子句
提问于 2020-12-22 18:24:48
我想从Node.js运行一个SQL查询。我想要显示4个季度中每个季度具有特定状态的项目总数。
这是我的两个季度的代码：
SELECT
SUM(CurrentStatus = 'On Hold') onHold_Q1,
SUM(CurrentStatus = 'In Progress') inProgress_Q1,
SUM(CurrentStatus = 'Not Started') notStarted_Q1,
SUM(CurrentStatus = 'Completed') completed_Q1,
SUM(CurrentStatus = 'Routine Activity') routineActivity_Q1,
SUM(CurrentStatus = 'Done But Not Published') doneButNotPublished_Q1
FROM office.officedata
WHERE Quarter = 'Q1';
SELECT
SUM(CurrentStatus = 'On Hold') onHold_Q2,
SUM(CurrentStatus = 'In Progress') inProgress_Q2,
SUM(CurrentStatus = 'Not Started') notStarted_Q2,
SUM(CurrentStatus = 'Completed') completed_Q2,
SUM(CurrentStatus = 'Routine Activity') routineActivity_Q2,
SUM(CurrentStatus = 'Done But Not Published') doneButNotPublished_Q2
FROM office.officedata
WHERE Quarter = 'Q2'; 我想将上述两个查询合并为一个查询。由于我将从后端(Node.js)运行此查询，因此我希望在单个请求中一次性获得所有季度数据。我不想向mySQL发送多个请求。我该怎么做呢？
回答 2
Stack Overflow用户
回答已采纳
发布于 2020-12-22 18:26:28
您可以按季度分组：
SELECT Quarter,
SUM(CurrentStatus = 'On Hold') onHold,
SUM(CurrentStatus = 'In Progress') inProgress,
SUM(CurrentStatus = 'Not Started') notStarted,
SUM(CurrentStatus = 'Completed') completed,
SUM(CurrentStatus = 'Routine Activity') routineActivity,
SUM(CurrentStatus = 'Done But Not Published') doneButNotPublished
FROM office.officedata
WHERE Quarter in ('Q1', 'Q2')
GROUP BY Quarter;这将每季度生成一行，值在列中。虽然可以将所有内容放在同一行(输入更多！)，但我发现这个结果更有用。
Stack Overflow用户
发布于 2020-12-22 18:37:25
您想要使用UNION clause
SELECT
SUM(CurrentStatus = 'On Hold') onHold,
SUM(CurrentStatus = 'In Progress') inProgress,
SUM(CurrentStatus = 'Not Started') notStarted,
SUM(CurrentStatus = 'Completed') completed,
SUM(CurrentStatus = 'Routine Activity') routineActivity,
SUM(CurrentStatus = 'Done But Not Published') doneButNotPublished
FROM office.officedata
WHERE Quarter = 'Q1'
UNION SELECT
SUM(CurrentStatus = 'On Hold') onHold,
SUM(CurrentStatus = 'In Progress') inProgress,
SUM(CurrentStatus = 'Not Started') notStarted,
SUM(CurrentStatus = 'Completed') completed,
SUM(CurrentStatus = 'Routine Activity') routineActivity,
SUM(CurrentStatus = 'Done But Not Published') doneButNotPublished
FROM office.officedata
WHERE Quarter = 'Q2'; 页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/65406987
复制相关文章
相似问题
腾讯云开发者
