代码之家 › 专栏 › 技术社区 › odlan yer

这个select语句的正确sql脚本是什么?

pivot sql-server-2000 sql-server sql

0

odlan yer · 技术社区 · 11 年前

这是我的剧本

SELECT iBranch_num,
   CASE WHEN iPatient_typ=1 THEN COUNT(iPatient_num) ELSE 0 END AS [New Patient], 
   CASE WHEN iPatient_typ=2 THEN COUNT(iPatient_num) ELSE 0 END AS [Buying Patient],                
   CASE WHEN iPatient_typ=3 THEN COUNT(iPatient_num) ELSE 0 END AS [Active Patient],
   CASE WHEN iPatient_typ=4 THEN COUNT(iPatient_num) ELSE 0 END AS [Inactive Patient]
FROM tblsotransaction
WHERE iStatus_typ=1
   AND iApply_dt BETWEEN 20130401 AND 20130408 
   AND iBranch_num=14
GROUP BY iBranch_num, iPatient_typ

当前结果:

14, 25, 0,  0,  0
14, 0,  8,  0,  0
14, 0,  0,  97, 0
14, 0,  0,  0,  2

我希望结果是这样的

14, 25, 8,  9,  2

1 回复 | 直到 11 年前

1

2

Mahmoud Gamal 11 年前

包裹整个 CASE 聚合函数内部的表达式 COUNT ,然后删除 iPatient_typ 来自 GROUP BY 条款:

SELECT 
  iBranch_num,
  COUNT(CASE WHEN iPatient_typ=1 THEN iPatient_num ELSE 0 END) AS [New Patient], 
  COUNT(CASE WHEN iPatient_typ=2 THEN iPatient_num ELSE 0 END) AS [Buying Patient],                
  COUNT(CASE WHEN iPatient_typ=3 THEN iPatient_num ELSE 0 END) AS [Active Patient],
  COUNT(CASE WHEN iPatient_typ=4 THEN iPatient_num ELSE 0 END) AS [Inactive Patient]
FROM tblsotransaction
WHERE iStatus_typ = 1 
  AND iApply_dt BETWEEN 20130401 AND 20130408 
  AND iBranch_num = 14
GROUP BY iBranch_num;

或者: SUM :

SELECT 
  iBranch_num,
  SUM(CASE WHEN iPatient_typ = 1 THEN 1 ELSE 0 END) AS [New Patient], 
  SUM(CASE WHEN iPatient_typ = 2 THEN 1 ELSE 0 END) AS [Buying Patient],
  SUM(CASE WHEN iPatient_typ = 3 THEN 1 ELSE 0 END) AS [Active Patient],
  SUM(CASE WHEN iPatient_typ = 4 THEN 1 ELSE 0 END) AS [Inactive Patient]
FROM tblsotransaction
WHERE iStatus_typ = 1 
  AND iApply_dt BETWEEN 20130401 AND 20130408 
  AND iBranch_num = 14
GROUP BY iBranch_num;