select
b.cname,b.cid,num=isnull(c.num,0)
from
b
left join
(select cid,num=count(*) from a group by cid) c
where
b.cid = c.cid
order by
isnull(c.num,0) desc
b.cname,b.cid,num=isnull(c.num,0)
from
b
left join
(select cid,num=count(*) from a group by cid) c
where
b.cid = c.cid
order by
isnull(c.num,0) desc
from b表 a left join a表 b on a.cid=b.cid
,b.cid
,count(1) as 'num'
from b
left join a on a.cid=b.cid
group by b.cname
,b.cid
from b表 a left join a表 b on a.cid=b.cid
create table #a(cid varchar(10),pname varchar(10),pcolor varchar(10),psize int)
insert into #a select '001','名1','红色',114
insert into #a select '001','名2','黑色',117
insert into #a select '002','名3','白色',115insert into #b(cid varchar(10),cname varchar(10))
insert into #b select '001','xxx'
insert into #b select '002','xxxx'
insert into #b select '003','xxxxx'
--执行查询
select
b.cname,b.cid,num=isnull(c.num,0)
from
#b b
left join
(select cid,num=count(*) from #a group by cid) c
where
b.cid = c.cid
order by
isnull(c.num,0) desc
--输出结果
/*
cname cid num
----- --- ---
xxx 001 2
xxxx 002 1
xxxxx 003 0
*/
,b.cid
,sum(case when a.cid is not null then 1 else 0 end) as 'num'
from b
left join a on a.cid=b.cid
group by b.cname
,b.cid
create table #a(cid varchar(10),pname varchar(10),pcolor varchar(10),psize int)
insert into #a select '001','名1','红色',114
insert into #a select '001','名2','黑色',117
insert into #a select '002','名3','白色',115insert into #b(cid varchar(10),cname varchar(10))
insert into #b select '001','xxx'
insert into #b select '002','xxxx'
insert into #b select '003','xxxxx'
--执行查询
select
b.cname,b.cid,num=(select isnull(count(*),0) from #a where cid = b.cid)
from
#b b
order by
num desc
--输出结果
/*
cname cid num
----- --- ---
xxx 001 2
xxxx 002 1
xxxxx 003 0
*/
create table #a(cid varchar(10),pname varchar(10),pcolor varchar(10),psize int)
insert into #a select '001','名1','红色',114
insert into #a select '001','名2','黑色',117
insert into #a select '002','名3','白色',115insert into #b(cid varchar(10),cname varchar(10))
insert into #b select '001','xxx'
insert into #b select '002','xxxx'
insert into #b select '003','xxxxx'
--执行查询
select
b.cname,b.cid,num=sum(case when a.cid is not null then 1 else 0 end)
from
#b b
left join
#a a
on
b.cid = a.cid
group by
b.cname,b.cid
order by
num desc
--输出结果
/*
cname cid num
----- --- ---
xxx 001 2
xxxx 002 1
xxxxx 003 0
*/
create table #a(cid varchar(10),pname varchar(10),pcolor varchar(10),psize int)
insert into #a select '001','名1','红色',114
insert into #a select '001','名2','黑色',117
insert into #a select '002','名3','白色',115create table #b(cid varchar(10),cname varchar(10))
insert into #b select '001','xxx'
insert into #b select '002','xxxx'
insert into #b select '003','xxxxx'
--查询
select b.cname
,b.cid
,sum(case when a.cid is not null then 1 else 0 end) as 'num'
from #b b
left join #a a on a.cid=b.cid
group by b.cname
,b.cid-- 删除测试环境
drop table #a,#b--结果
/*
cname cid num
---------- ---------- -----------
xxx 001 2
xxxx 002 1
xxxxx 003 0(所影响的行数为 3 行)
*/
select b.cname,b.cid, (select count(*) from a where a.cid=b.cid) from a,b group by b.cname,b.cid
create table a(cid char(10),pname char(10),pcolor char(10),psize int)
insert into a (cid,pname,pcolor,psize) values ('001','名1','红色',114)
insert into a (cid,pname,pcolor,psize) values ('001','名2','黑色',117)
insert into a (cid,pname,pcolor,psize) values ('002','名3','白色',115)create table b (cid char(10),cname char(10))
insert into b (cid,cname) values ('001','xxx')
insert into b (cid,cname) values ('002','xxxx')
insert into b (cid,cname) values ('003','xxxxx') select * from a
select * from b
--执行查询
select
b.cname,b.cid,num=(select isnull(count(*),0) from a where cid = b.cid)
from
b