关于网友提出的“ 求助一个group by 汇总的sql写法”问题疑问，本网通过在网上对“ 求助一个group by 汇总的sql写法”有关的相关答案进行了整理，供用户进行参考，详细问题解答如下：
问题： 求助一个group by 汇总的sql写法
解决方案3: 把楼上的改了一下
if not object_id(N'Tempdb..#test') is null
drop table #test
Go
GO
CREATE TABLE #test(日期 datetime,姓名 NVARCHAR(20),检查项目 nvarchar(20),是否合格 nchar(1))
SET NOCOUNT ON
INSERT INTO #test(日期,姓名 ,检查项目 ,是否合格)
SELECT '2017-06-10','张三','项目1','是' union all
SELECT '2017-06-10','张三','项目2','否' union all
SELECT '2017-06-10','张三','项目3','是' union all
SELECT '2017-06-10','张三','项目4','是' union all
SELECT '2017-06-10','张三','项目5','是' union all
SELECT '2017-06-10','张三','项目6','是' union all
SELECT '2017-06-10','李四','项目1','是' union all
SELECT '2017-06-10','李四','项目2','是' union all
SELECT '2017-06-10','李四','项目3','是' union all
SELECT '2017-06-10','李四','项目4','否' union all
SELECT '2017-06-10','李四','项目5','是' union all
SELECT '2017-06-10','李四','项目6','是' union all
SELECT '2017-06-11','张三','项目1','是' union all
SELECT '2017-06-11','张三','项目2','是' union all
SELECT '2017-06-11','张三','项目3','是' union all
SELECT '2017-06-11','张三','项目4','是' union all
SELECT '2017-06-11','张三','项目5','是' union all
SELECT '2017-06-11','张三','项目6','是'
SELECT [日期],
COUNT(DISTINCT 姓名) AS [检查人数],
SUM(CASE WHEN [是否合格] = 0 THEN 1 ELSE 0 END) AS [合格],
SUM(CASE WHEN [是否合格] > 0 THEN 1 ELSE 0 END) AS [不合格]
FROM (
SELECT [姓名],
[日期],
COUNT(DISTINCT 姓名) AS [检查人数],
SUM(CASE WHEN [是否合格] = '是' THEN 0 ELSE 1 END) AS [是否合格]
FROM #test
GROUP BY
[姓名],
[日期]
) a
GROUP BY
[日期]
以上介绍了“ 求助一个group by 汇总的sql写法”的问题解答，希望对有需要的网友有所帮助。
本文网址链接：http://www.codes51.com/itwd/4378136_2.html
