表一
num,class
001 1
002 2
003 3
表二
num,zhangjie,class
001 第三章 1
002 第四章 3
002 第三章 1
要得到的表:
class,第三章人数,第四章人数,共几人
1 1 1 2
2 0 0 0
3 0 1 1
num,class
001 1
002 2
003 3
表二
num,zhangjie,class
001 第三章 1
002 第四章 3
002 第三章 1
要得到的表:
class,第三章人数,第四章人数,共几人
1 1 1 2
2 0 0 0
3 0 1 1
(num int,
class int)
create table num2
(num int
,class int
,zhangjie nvarchar(13)
)insert into num1
select 001,1
union all
select 002, 2
union all
select 003, 3insert into num2
select 001,1,'diyizhang'
union all
select 002, 2,'dierzhang'
union all
select 003,3,'disanzhang'select * from num1
select * from num2
--------------num class
----------- -----------
1 1
2 2
3 3 (3 件処理されました)num class zhangjie
----------- ----------- -------------
1 1 diyizhang
2 2 dierzhang
3 3 disanzhang(3 件処理されました)select a.num
,a.class
,b.zhangjie
,b.number
from
num1 a
inner join
(select zhangjie
,num
,count(num) as number
from num2
group by
zhangjie
,num ) b
on
a.num=b.num
-------------------
num class zhangjie number
----------- ----------- ------------- -----------
1 1 diyizhang 1
2 2 dierzhang 1
3 3 disanzhang 1 (3 件処理されました)
select a.num
,a.class
,b.zhangjie
,b.number
from
num1 a
inner join
(select zhangjie
,class
,num
,count(num) as number
from num2
group by
zhangjie
,num
,class) b
on
a.num=b.num
and
a.class=b.class