时间戳

首页 > 解决方案 > 如何将这四个请求合二为一？

sql - 如何将这四个请求合二为一？

问题描述

如何将这四个请求合二为一？

Select COUNT(Table1.statusID) as result1 from Table1 where Table1.emplDesignerID = 1135 and Table1.cancelled = 0
Select COUNT(Table1.statusID) as result2 from Table1 where Table1.emplDesignerID = 26 and Table1.cancelled = 0
Select COUNT(Table1.statusID) as result3 from Table1 where Table1.emplDesignerID = 1086 and Table1.cancelled = 0
Select COUNT(Table1.statusID) as result4 from Table1 where Table1.emplDesignerID = 1089 and Table1.cancelled = 0

我正在使用 MS SQL Server。

标签： sqlsql-server

解决方案

SUM并且CASE可以在这里有用地组合：

Select
    SUM(CASE WHEN Table1.emplDesignerID = 1135 THEN 1 ELSE 0 END) as result1,
    SUM(CASE WHEN Table1.emplDesignerID = 26 THEN 1 ELSE 0 END) as result2,
    SUM(CASE WHEN Table1.emplDesignerID = 1086 THEN 1 ELSE 0 END) as result3,
    SUM(CASE WHEN Table1.emplDesignerID = 1089 THEN 1 ELSE 0 END) as result4
from Table1
where Table1.cancelled = 0
and Table1.emplDesignerID in (1135,26,1086,1089)

根据使用情况，您可能会或可能不会觉得子句IN中需要过滤器。WHERE还有其他可能的转换（emplDesignerID在SELECT子句中并GROUP在其上）可能使其更灵活。

推荐阅读