时间戳

首页 > 解决方案 > 需要帮助来简化此 T-SQL 查询

sql - 需要帮助来简化此 T-SQL 查询

问题描述

我有这个有效的 T-SQL 查询，有什么方法可以简化它吗？

是重复的SELECT COUNT(*) FROM Tablename，只有每个查询的条件不同，所以有没有办法进一步简化呢？谢谢你

SELECT COUNT(*) FROM Table 
WHERE columnA = 'I' AND columnB = 'I' AND columnC =''

UNION ALL

SELECT COUNT(*) FROM Table 
WHERE columnA = 'I' AND columnB = 'I' AND columnC <>''

UNION ALL

SELECT COUNT(*) FROM Table
WHERE columnA IN ('A','R') AND columnB = 'I' AND columnD IN (SELECT columnE FROM Table2)

UNION ALL

SELECT COUNT(*) FROM Table 
WHERE columnA IN ('B') AND columnB = 'I'

UNION ALL

SELECT COUNT(*) FROM Table 
WHERE columnA IN ('R') AND columnB = 'S'

UNION ALL

SELECT COUNT(*) FROM Table 
WHERE columnA IN ('A') AND columnB = 'S'

这是工作查询，我的输出是 6 行，想要保持它

标签： sqlmssql-jdbc

解决方案

让我假设这BI_BRH是独一无二的STDHOST。这不是绝对必要的，但它正在简化。

然后你可以使用条件聚合。我认为将值放在列中是最简单的方法：

SELECT SUM(CASE WHEN STATUS = 'I' AND TRX_STATE = 'I' AND ASSIGN_TO = '' THEN 1 ELSE 0 END),
       SUM(CASE WHEN STATUS = 'I' AND TRX_STATE = 'I' AND ASSIGN_TO <>'' THEN 1 ELSE 0 END)
       SUM(CASE WHEN STATUS IN ('A','R') AND TRX_STATE = 'I' AND s.BI_BRH IS NOT NULL THEN 1 ELSE 0 END)
       SUM(CASE WHEN STATUS IN ('B') AND TRX_STATE = 'I' THEN 1 ELSE 0 END)
       SUM(CASE WHEN STATUS IN ('R') AND TRX_STATE = 'S' THEN 1 ELSE 0 END)
       SUM(CASE WHEN STATUS IN ('A') AND TRX_STATE = 'S' THEN 1 ELSE 0 END)
FROM ICSCHQINFO i LEFT JOIN
     STDHOST s
     ON s.BI_BRH = i.BRH_ASSIGN_TO;

推荐阅读