共有两个表,table2通过ID1来连接table1：
table1:
ID, NAME
001, zhao
002, qian
003, sun
004, li
table2:
ID,ID1,SCORE
1,001, pass
2,001, fail
3,001, pass
4,002, pass
我想得到的结果是计算每个人的PASS和FAIL的数目(PASS/FAIL要int型)：
ID,NAME,PASS,FAIL
001,zhao,2,1
002,qian,1,0
003,sun,0,0
004,li,0,0
SQL code:
declare @table1 table(id char(10), name char(10))
insert @table1
select '001', 'zhao' union all
select '002', 'qian' union all
select '003', 'sun' union all
select '004', 'li'
declare @table2 table(id int, id1 char(10), score char(10))
insert @table2
select 1,'001', 'pass' union all
select 2,'001', 'fail' union all
select 3,'001', 'pass' union all
select 4,'002', 'pass'
select a.id, a.name, sum(case when b.score = 'pass' then 1 else 0 end) as pass,
sum(case when b.score = 'fail' then 1 else 0 end) as fail
from @table1 a left join @table2 b on a.id = b.id1
group by a.id, a.name
order by a.id
/*
id name pass fail
---------- ---------- ----------- -----------
001 zhao 2 1
002 qian 1 0
003 sun 0 0
004 li 0 0
*/
S
