表A:
name duty
qq 1
ww 1
qq 1
ee 1表B:
name
qq
ww
ee
cc
xx想的到
name duty
qq 2
ww 1
ee 1
cc 0
xx 0duty表示值班次数,我想用count(*),group by name 得到最下面的数据。求高人帮忙。谢谢
name duty
qq 1
ww 1
qq 1
ee 1表B:
name
ww
ee
cc
xx想的到
name duty
qq 2
ww 1
ee 1
cc 0
xx 0duty表示值班次数,我想用count(*),group by name 得到最下面的数据。求高人帮忙。谢谢
select b.name,count(*)
from b left join a on a.name = b.name
group by b.name
from b left join a on a.name = b.name
group by b.name
create table #表A
([name] nvarchar(10), duty int)
insert #表A
select 'qq',1 union all
select 'ww',1 union all
select 'qq',1 union all
select 'ee',1create table #表B
([name] nvarchar(10))
insert #表B
select 'qq' union all
select 'ww' union all
select 'ee' union all
select 'cc' union all
select 'xx'select b.[name],sum(isnull(duty,0)) as duty from #表B as b
left join #表A as a on b.[name]=a.[name] group by b.[name]
--第一种 对duty进行求和
select b.name,sum(b.duty) as duty
from b left join a on a.name = b.name
group by b.name
order by b.name--第二种 求条目数
select b.name,count(1) as duty
from b left join a on a.name = b.name
group by b.name
order by b.name
insert into @a
select 'qq', 1 union all
select 'ww', 1 union all
select 'qq', 1 union all
select 'ee', 1declare @b table(name1 varchar(10))
insert into @b
select 'qq' union all
select 'ww' union all
select 'ee' union all
select 'cc' union all
select 'xx'with t
as(select * from @a
union all
select *,duty=0 from @b)
select name1,duty=sum(duty) from t group by name1 order by duty desc