表 [Class]
cid cname ...
1 aaa
2 bbb
3 ccc表 [New]
nid cid ...
1 1
2 1
3 1
4 3想要执行SQL查询之后显示如下1 aaa 3
2 bbb 0
3 ccc 1请问该怎么写?
cid cname ...
1 aaa
2 bbb
3 ccc表 [New]
nid cid ...
1 1
2 1
3 1
4 3想要执行SQL查询之后显示如下1 aaa 3
2 bbb 0
3 ccc 1请问该怎么写?
(
cid int identity(1,1) primary key,
cname nvarchar(20)
)
insert into #Class select 'aaa'
insert into #Class select 'bbb'
insert into #Class select 'ccc'create table #New
(
nid int identity(1,1) primary key,
cid int
)
insert into #New select 1
insert into #New select 1
insert into #New select 1
insert into #New select 3select C.*,isnull(N.cnt,0) from #Class C
full join
(
select cid,count(cid) cnt from #New group by cid
)N
on
C.cid=N.cid
(
cid int identity(1,1) primary key,
cname nvarchar(20)
)
insert into #Class select 'aaa'
insert into #Class select 'bbb'
insert into #Class select 'ccc'create table #New
(
nid int identity(1,1) primary key,
cid int
)
insert into #New select 1
insert into #New select 1
insert into #New select 1
insert into #New select 3select C.*,isnull(N.cnt,0) cnt from #Class C
full join
(
select cid,count(cid) cnt from #New group by cid
)N
on
C.cid=N.cidcid cname cnt
----------- -------------------- -----------
1 aaa 3
2 bbb 0
3 ccc 1(3 行受影响)
create table class
(cid int,
cname varchar(10))create table new
(nid int,cid int)insert class
select 1,'aaa' union all
select 2,'bbb' union all
select 3,'ccc' insert new
select 1,1 union all
select 2,1 union all
select 3,1 union all
select 4,3 select a.*,COUNT(b.cid) from [Class] a left join [New] b
on a.cid=b.cid
group by a.cid,a.cname
from class a left join
(select count(*) as num ,cid from new group by cid) b
on a.cid=b.cid
from #class c left join #new n
on c.cid = n.cid
group by c.cid,c.cname
if object_id('[Class]') is not null drop table [Class]
go
create table [Class] (cid int,cname nvarchar(6))
insert into [Class]
select 1,'aaa' union all
select 2,'bbb' union all
select 3,'ccc'
--> Title : Generating test data [New]
if object_id('[New]') is not null drop table [New]
go
create table [New] (nid int,cid int)
insert into [New]
select 1,1 union all
select 2,1 union all
select 3,1 union all
select 4,3
select a.*,
isnull(b.cnt,0)cnt
from [Class] a
left join
(select cid,count(*)cnt from [New] group by cid)b
on a.cid=b.cid
/*
cid cname cnt
----------- ------ -----------
1 aaa 3
2 bbb 0
3 ccc 1(3 個資料列受到影響)
*/
left join
(select count(1) from new where cid=class.cid) as 数量
from class/**
cid cname 数量
----------- ------ -----------
1 aaa 3
2 bbb 0
3 ccc 1(所影响的行数为 3 行)
**/
select a.*,isnull(b.cid_cnt,0) as cid_cnt
from class a
LEFT JOIN
(
select cid,count(nid) as cid_cnt
from New
group by cid
) b
ON a.cid = b.cid
(
cid int identity(1,1) primary key,
cname nvarchar(20)
)
insert into #Class select 'aaa'
insert into #Class select 'bbb'
insert into #Class select 'ccc'create table #New
(
nid int identity(1,1) primary key,
cid int
)
insert into #New select 1
insert into #New select 1
insert into #New select 1
insert into #New select 3select C.*,isnull(N.cnt,0) cnt from #Class C
full join
(
select cid,count(cid) cnt from #New group by cid
)N
on
C.cid=N.cidcid cname cnt
----------- -------------------- -----------
1 aaa 3
2 bbb 0
3 ccc 1(3 行受影响)
是啊,用left join 但是很多人都用到了子查询。。
if object_id('aa') is not null
drop table aacreate table aa(cid int,cname varchar(10))
insert into aa
select 1,'aaa' union all
select 2,'bbb' union all
select 3,'ccc'if object_id('bb') is not null
drop table bb
go
create table bb(nid int,cid int)
insert into bb
select 1,1 union all
select 2,1 union all
select 3,1 union all
select 4,3select a.*,count(b.nid) as num from aa a left join bb b on a.cid=b.cid
group by a.cid,a.cname
(cid int,
cname varchar(10))create table new
(nid int,cid int)insert class
select 1,'aaa' union all
select 2,'bbb' union all
select 3,'ccc' insert new
select 1,1 union all
select 2,1 union all
select 3,1 union all
select 4,3 (1)select a.*,COUNT(b.cid) from [Class] a left join [New] b
on a.cid=b.cid
group by a.cid,a.cname(2)select isnull(count(e.cid1),0)as num,e.cname from
(
(select * from class c
left join
(select nid as nid1,cid as cid1 from new ) n on
n.cid1=c.cid)
)
e group by e.cid,e.cname
select t1.cid, t1.cname, COUNT(t2.nid) num from t1
left join t2 on t1.cid = t2.cid
group by t1.cid, t1.cname
select a.cid,a.cname,count(b.nid) as cnt
from class a
left join new b
ON a.cid = b.cid
group by a.cid,a.cname
create table #Class
(
cid int,
cname varchar(30),
constraint PK_Class_1 primary key(cid)
)
-- table New
create table #New
(
nid int,
cid int
)
Insert into #Class(cid,cname)
Select 1,'aaa'
union all
Select 2,'bbb'
union all
Select 3,'ccc' Insert into #New(nid,cid)
Select 1,1
union all
Select 2,1
union all
Select 3,1
union all
Select 4,3
--- test
Select * from #Class
Select * from #New
select distinct
a.cid,
a.cname,
case when b.nid is null then 0
else COUNT(*) over(partition by a.cid) end as num
from #Class a left join #New b on a.cid=b.cid
insert into t1 values(1,'aaa')
insert into t1 values(2,'bbb')
insert into t1 values(3,'ccc')
select * from t1create table t2(nid int,cid int)
insert into t2 values(1,1)
insert into t2 values(2,1)
insert into t2 values(3,1)
insert into t2 values(4,3)
select * from t2select t1.cid,cname,count(nid)as 'total'
from t1 left join t2 on t1.cid=t2.cid
group by t1.cname,t1.cname,t1.cid