select name, [TrueCount]=sum(case when isOK='True' then 1 else 0 end), [FalseCount]=sum(case when isOK='False' then 1 else 0 end) from table1 group by name
--> --> (Roy)生成測試數據
if not object_id('Tempdb..#T') is null drop table #T Go Create table #T([id] int,[name] nvarchar(5),[isOK] nvarchar(5),[reason] nvarchar(2)) Insert #T select 1,N'jack',N'False',N'a' union all select 2,N'jack',N'False',N'b' union all select 3,N'jack',N'True',N'c' union all select 4,N'jack',N'True',N'd' union all select 5,N'marry',N'True',N'ss' union all select 6,N'marry',N'True',N'se' union all select 7,N'marry',N'False',N'sd' Goselect name, [TrueCount]=sum(case when isOK='True' then 1 else 0 end), [FalseCount]=sum(case when isOK='False' then 1 else 0 end) from #T group by name /* name TrueCount FalseCount jack 2 2 marry 2 1 */
create table tb(id int,name nvarchar(10),isOK varchar(10),reason varchar(10)) --------------------------------------- insert into tb select 1,'jack','False','a' insert into tb select 2,'jack','False','b' insert into tb select 3,'jack','True','c' insert into tb select 4,'jack','True','d' insert into tb select 5,'marry','True','ss' insert into tb select 6,'marry','True','se' insert into tb select 7,'marry','False','sd' go select name, SUM(case when isOK='True' then 1 else 0 end)TrueCount, SUM(case when isOK='True' then 0 else 1 end)FalseCount from tb group by name /* name TrueCount FalseCount ---------- ----------- ----------- jack 2 2 marry 2 1(2 行受影响) */ go drop table tb
select name, sum(case when isOK='True' then 1 else 0 end) as TrueCount, sum(case when isOK='True' then 0 else 1 end) as FalseCount from tb group by name
name,
[TrueCount]=sum(case when isOK='True' then 1 else 0 end),
[FalseCount]=sum(case when isOK='False' then 1 else 0 end)
from table1
group by name
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([id] int,[name] nvarchar(5),[isOK] nvarchar(5),[reason] nvarchar(2))
Insert #T
select 1,N'jack',N'False',N'a' union all
select 2,N'jack',N'False',N'b' union all
select 3,N'jack',N'True',N'c' union all
select 4,N'jack',N'True',N'd' union all
select 5,N'marry',N'True',N'ss' union all
select 6,N'marry',N'True',N'se' union all
select 7,N'marry',N'False',N'sd'
Goselect
name,
[TrueCount]=sum(case when isOK='True' then 1 else 0 end),
[FalseCount]=sum(case when isOK='False' then 1 else 0 end)
from #T
group by name
/*
name TrueCount FalseCount
jack 2 2
marry 2 1
*/
---------------------------------------
insert into tb select 1,'jack','False','a'
insert into tb select 2,'jack','False','b'
insert into tb select 3,'jack','True','c'
insert into tb select 4,'jack','True','d'
insert into tb select 5,'marry','True','ss'
insert into tb select 6,'marry','True','se'
insert into tb select 7,'marry','False','sd'
go
select name,
SUM(case when isOK='True' then 1 else 0 end)TrueCount,
SUM(case when isOK='True' then 0 else 1 end)FalseCount
from tb group by name
/*
name TrueCount FalseCount
---------- ----------- -----------
jack 2 2
marry 2 1(2 行受影响)
*/
go
drop table tb
name,
sum(case when isOK='True' then 1 else 0 end) as TrueCount,
sum(case when isOK='True' then 0 else 1 end) as FalseCount
from
tb
group by
name