34,587
社区成员
发帖
与我相关
我的任务
分享
select B.名称,count(*) from B left join b_A on A.id=B.AID where f.jw_id=3 group by a.名称)
select A.名称,isnull(count(*),0)
from A left join B on A.id=B.AID and B.jw_id=3 group by A.名称
select
a.id,
a.name,
tongji=COUNT(b.jw_id)
from #a a
left join (SELECT * FROM #b where jw_id=3 ) b
on a.id=b.aid
group by a.id,a.name
order by a.id
select B.名称,count(*) from B left join b_A on A.id=B.AID where isnull(f.jw_id,3) = 3 group by a.名称)
select
a.id,a.name,count(b.jw_id) as tongji, min(b.name) as bname
from
a
left join
b
on
a.id=b.aid
group by
a.id,a.name
order by
a.id
declare @a table
(
id int not null,
name varchar(10) not null
)
insert into @a values(1,'蔬菜')
insert into @a values(2,'水果')
insert into @a values(3,'海鲜')
declare @b table
(
id int not null,
aid int not null,
name varchar(10) not null,
jw_id int
)
insert into @b values(1,1,'b1',1)
insert into @b values(2,1,'b2',null)
insert into @b values(3,3,'b3',null)
insert into @b values(4,2,'b4',3)
select
a.id,
a.name,
tongji=COUNT(b.jw_id),
bname = min(b.name)
from @a a
left join @b b
on a.id=b.aid
group by a.id,a.name
order by a.id
/*
id name tongji bname
1 蔬菜 1 b1
2 水果 1 b4
3 海鲜 0 b3
*/
declare @a table
(
id int not null,
name varchar(10) not null
)
insert into @a values(1,'shucai')
insert into @a values(2,'shuiguo')
insert into @a values(3,'haixian')
declare @b table
(
id int not null,
aid int not null,
name varchar(10) not null,
jw int
)
insert into @b values(1,1,'b1',1)
insert into @b values(2,1,'b2',null)
insert into @b values(3,2,'b3',null)
insert into @b values(4,3,'b4',3)
select a.name,COUNT(b.jw) as tongji,b.name from @a as a
left join @b as b on a.id=b.id
group by a.name,b.name
(1 行受影响)
(1 行受影响)
name tongji name
---------- ----------- ----------
shucai 1 b1
shuiguo 0 b2
haixian 0 b3
警告: 聚合或其他 SET 操作消除了 Null 值。
(3 行受影响)
按你的想得到你的要求。。