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 */
数据量大的话每个明细都要做 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 alluse 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 */
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
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
*/
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
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
*/
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