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 */
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 as a left join table2 b on a.id = b.id1 group by a.id, a.name order by a.id
/* 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 */ ;with hgo as ( select ID1, sum(case when SCORE='pass' then 1 end 0 end) 'pass', sum(case when SCORE='fail' then 1 end 0 end) 'fail' from table2 group by table2 b2 ) select h.ID1 as ID,b1.Name,isnull(h.pass,0),isnull(h.fail,0) from hgo h left join table1 b1 on b1.ID=h.ID1
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
*/
from table1 as a left join table2 b on a.id = b.id1
group by a.id, a.name
order by a.id
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
*/
;with hgo as
(
select ID1,
sum(case when SCORE='pass' then 1 end 0 end) 'pass',
sum(case when SCORE='fail' then 1 end 0 end) 'fail'
from table2 group by table2 b2
)
select h.ID1 as ID,b1.Name,isnull(h.pass,0),isnull(h.fail,0) from hgo h left join table1 b1
on b1.ID=h.ID1
上面的2000可以