统计唯一服务和按名称分组的SQL查询
统计唯一服务和按名称分组的SQL查询
提问于 2012-11-10 18:07:48
我对SQL还是个新手，我被一个查询卡住了。我有一个名为Appointment的表
NAME SERVICE DATE
-----------------------------
John Abc 2012-11-10
Michael Xyz 2012-11-10
Denis Xyz 2012-11-10
Denis Pqr 2012-11-06
John Abc 2012-11-06
Michael Abc 2012-11-05
Sofy Abc 2012-11-04
John Pqr 2012-11-01
Michael Pqr 2012-11-01
Michael Abc 2012-10-21
John Abc 2012-10-23
John HFD 2012-10-23
John BHU 2012-11-09
Michael KSD 2012-11-08我想要做的是计算在日期2012-11-01和2012-11-10之间为个人提供的服务。
以下是所需的输出
NAME Abc Pqr Xyz
--------------------------------
John 2 1 0
Michael 1 1 1
Denis 0 1 1
Sofy 1 0 0这是我尝试过的：
SELECT Name, COUNT(Distinct Service)
FROM [Appointment]
WHERE [DATE] between '2012-11-01 00:00:00.000' and '2012-11-10 23:59:59.999'
AND (Service = 'Abc' or Service = 'Pqr' or Service = 'Xyz')
GROUP BY Name提前谢谢你。
回答 1
Stack Overflow用户
回答已采纳
发布于 2012-11-10 19:26:35
SELECT Name,
SUM(CASE WHEN Service = 'Abc' THEN 1 ELSE 0 END) Abc,
SUM(CASE WHEN Service = 'Pqr' THEN 1 ELSE 0 END) Pqr,
SUM(CASE WHEN Service = 'Xyz' THEN 1 ELSE 0 END) Xyz
FROM TableName
WHERE [Date] BETWEEN CAST('2012-11-01' AS DATE) AND
CAST('2012-11-10' AS DATE)
GROUP BY Name或
SELECT Name, Abc, Pqr, Xyz
FROM
(
SELECT [Name], [Service]
FROM TableName
WHERE [Date] BETWEEN CAST('2012-11-01' AS DATE) AND
CAST('2012-11-10' AS DATE)
) p
PIVOT
(
COUNT ([service])
FOR [Service] IN ( [Abc], [Pqr], [Xyz] )
) AS s页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/13321086
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号