select
a.Code,
(select max(t.num) from (select num=count(*) from b group by Code) t)
-isnull(count(b.Code),0)
from
a
left join
b
on
a.Code=b.Code
a.Code,
(select max(t.num) from (select num=count(*) from b group by Code) t)
-isnull(count(b.Code),0)
from
a
left join
b
on
a.Code=b.Code
create table a (code int,name varchar(10))
insert into a select 1,'王1'
insert into a select 2,'张1'
insert into a select 3,'李1'
insert into a select 4,'赵1'
create table b (code int,时间 varchar(10))
insert into b select 1,'2005-01'
insert into b select 1,'2005-02'
insert into b select 1,'2005-03'
insert into b select 2,'2005-01'
insert into b select 2,'2005-02'
insert into b select 3,'2005-01'select code , (select max(t.num) from (select num=count(*) from b group by Code) t)-次数 as 次数
from
(select a.code,count(b.code) as 次数
from a left join b
on a.code = b.code
group by a.code) t1