SQL server 求一个sql,看似简单哟

[电灯泡]

tableA
id pass
1 y
2 n
3 n
3 y
3 y

求pass等于y时id所对应的数量
结果集：
id total
1 1
2 0
3 2

[chen357313771]

SELECT id,SUM(CASE pass WHEN 'y' THEN 1 ELSE 0 END) [total] 
FROM t
GROUP BY id

[电灯泡]

谢谢哈，我也想出来了 select id,sum(num) from (SELECT id,(case when pass='y' then 1 else 0 end) as num from table ) a GROUP BY id

[还在加载中灬]

--模拟数据
;WITH tableA(id,pass)AS(
	SELECT 1,'y'
	UNION ALL SELECT 2,'n'
	UNION ALL SELECT 3,'n'
	UNION ALL SELECT 3,'y'
	UNION ALL SELECT 3,'y'
)
--查询
SELECT id,COUNT(CASE pass WHEN 'y' THEN pass END)total FROM tableA GROUP BY id
--结果
/*
1	1
2	0
3	2
*/

[AngleElina]

引用 3 楼 ky_min 的回复:

--模拟数据
;WITH tableA(id,pass)AS(
	SELECT 1,'y'
	UNION ALL SELECT 2,'n'
	UNION ALL SELECT 3,'n'
	UNION ALL SELECT 3,'y'
	UNION ALL SELECT 3,'y'
)
--查询
SELECT id,COUNT(CASE pass WHEN 'y' THEN pass END)total FROM tableA GROUP BY id
--结果
/*
1	1
2	0
3	2
*/

大神，哪里都能看到你

[还在加载中灬]

我也是菜鸟，多逛多练

[Tiger_Zhao]

数据量大的话每个明细都要做 CASE 运算不划算。

    SELECT a.id, ISNULL(b.total,0) total
      FROM (SELECT DISTINCT id FROM table1) a
 LEFT JOIN (
                SELECT id, count(*) total
                  FROM table1
                 WHERE pass = 'y'
              GROUP BY id
           ) b
        ON b.id = a.id

[中国风]

这样用group by all

use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#tableA') is null
	drop table #tableA
Go
Create table #tableA([id] int,[pass] nvarchar(1))
Insert #tableA
select 1,N'y' union all
select 2,N'n' union all
select 3,N'n' union all
select 3,N'y' union all
select 3,N'y'
Go
Select [id],COUNT(1) AS  total  from #tableA WHERE [pass]='y' GROUP BY ALL [id]
/*
id	total
1	1
2	0
3	2
*/

[Trouble___Maker]

with
tb(id,pass) as
(
    SELECT 1,'y' UNION ALL
	SELECT 2,'n' UNION ALL
	SELECT 3,'n' UNION ALL
	SELECT 3,'y' UNION ALL
	SELECT 3,'y'
)

SELECT id, SUM(CASE pass WHEN 'y' THEN 1 ELSE 0 END) AS COUNT FROM tb GROUP BY id

[Tiger_Zhao]

引用 7 楼 roy_88 的回复:

这样用group by all

[电灯泡]

引用 7 楼 roy_88 的回复:

这样用group by all

use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#tableA') is null
	drop table #tableA
Go
Create table #tableA([id] int,[pass] nvarchar(1))
Insert #tableA
select 1,N'y' union all
select 2,N'n' union all
select 3,N'n' union all
select 3,N'y' union all
select 3,N'y'
Go
Select [id],COUNT(1) AS  total  from #tableA WHERE [pass]='y' GROUP BY ALL [id]
/*
id	total
1	1
2	0
3	2
*/

艾玛，太cool了