修改一下
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
create table num1
(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, 3
insert 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