关于网友提出的“ 求助一个group by 汇总的sql写法”问题疑问，本网通过在网上对“ 求助一个group by 汇总的sql写法”有关的相关答案进行了整理，供用户进行参考，详细问题解答如下：
问题： 求助一个group by 汇总的sql写法
描述:
通过左边的数据 汇总右边的表格.
求sql写法.越简练,效率高越好
解决方案1:
if object_id('tempdb..#Tmp_Data') is not null
drop table #Tmp_Data
CREATE TABLE #Tmp_Data (
Check_Date datetime ,
Name nvarchar(20),
Item_No nvarchar(20),
Pass_Yn varchar(10))
Insert into #Tmp_Data
Select '2017-06-10',N'张三',N'项目1',N'是' union
Select '2017-06-10',N'张三',N'项目2',N'否' union
Select '2017-06-10',N'张三',N'项目3',N'是' union
Select '2017-06-10',N'张三',N'项目4',N'是' union
Select '2017-06-10',N'张三',N'项目5',N'是' union
Select '2017-06-10',N'张三',N'项目6',N'是' union
Select '2017-06-10',N'李四',N'项目1',N'是' union
Select '2017-06-10',N'李四',N'项目2',N'是' union
Select '2017-06-10',N'李四',N'项目3',N'否' union
Select '2017-06-10',N'李四',N'项目4',N'是' union
Select '2017-06-10',N'李四',N'项目5',N'是' union
Select '2017-06-10',N'李四',N'项目6',N'是' union
Select '2017-06-11',N'张三',N'项目1',N'是' union
Select '2017-06-11',N'张三',N'项目2',N'是' union
Select '2017-06-11',N'张三',N'项目3',N'是' union
Select '2017-06-11',N'张三',N'项目4',N'是' union
Select '2017-06-11',N'张三',N'项目5',N'是' union
Select '2017-06-11',N'张三',N'项目6',N'是'
--一个人在同一天, 如果一个项目不合格, 这个人的结果算为不合格
Select Check_Date,COUNT(distinct name) as name,
sum(case when cnt=2 then 0 else 1 end) as Pass,sum(case when cnt=2 then 1 else 0 end) as No_Pass
From (
Select Check_Date,Name,count(*) as cnt
From (
Select Check_Date,Name,Pass_Yn
From #Tmp_Data
Group By Check_Date,Name,Pass_Yn
)a
Group By Check_Date,Name
) x
Group By Check_Date,cnt
Order BY Check_Date
解决方案2:USE tempdb
GO
IF OBJECT_ID('test') IS NOT NULL
DROP TABLE test
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','是'
;WITH cte AS (
SELECT [日期]
,姓名
,SUM(CASE WHEN [是否合格]='是' THEN 1 ELSE 0 END) AS [合格]
,sum(CASE WHEN [是否合格] != '是' then 1 else 0 end) AS [不合格]
FROM test GROUP BY [日期],姓名
)
SELECT 日期,count(distinct 姓名) as 检查人数,
SUM(CASE WHEN 不合格>0 then 0 else 1 end) as 合格,
SUM(CASE WHEN 不合格>0 then 1 else 0 end) as 不合格
FROM cte GROUP BY 日期
/*
日期 检查人数 合格 不合格
2017-06-10 2 0 2
2017-06-11 1 1 0
*/
