表A和表B,大致如下:A表:ID Name TypeID
1 a1 1
2 a2 1
3 a3 2
4 a4 2
5 a5 4B表:TypeID Type
1 t1
2 t2
3 t3
4 t4A,B通过TypeID关联,想统计每个Type的个数,但遇到A中没有的Type,不知如何处理。希望得到如下统计结果,请问SQL该如何写? Type Count
t1 2
t2 2
t3 0
t4 1
1 a1 1
2 a2 1
3 a3 2
4 a4 2
5 a5 4B表:TypeID Type
1 t1
2 t2
3 t3
4 t4A,B通过TypeID关联,想统计每个Type的个数,但遇到A中没有的Type,不知如何处理。希望得到如下统计结果,请问SQL该如何写? Type Count
t1 2
t2 2
t3 0
t4 1
B表:TypeID Type select b.type,isnull(count(1),0)
from tb b
left join ta a
on a.typeid = b.typeid
--> 测试数据:[A表]
if object_id('[A表]') is not null drop table [A表]
create table [A表]([ID] int,[Name] varchar(2),[TypeID] int)
insert [A表]
select 1,'a1',1 union all
select 2,'a2',1 union all
select 3,'a3',2 union all
select 4,'a4',2 union all
select 5,'a5',4
--> 测试数据:[B表]
if object_id('[B表]') is not null drop table [B表]
create table [B表]([TypeID] int,[Type] varchar(2))
insert [B表]
select 1,'t1' union all
select 2,'t2' union all
select 3,'t3' union all
select 4,'t4'select [Type],count(A.[TypeID]) from [B表] B left join [A表] A on A.[TypeID]=B.[TypeID]
group by [Type]
/*
Type
---- -----------
t1 2
t2 2
t3 0
t4 1
警告: 聚合或其他 SET 操作消除了空值。(4 行受影响)*/
drop table [B表],[A表]
b.[type],
isnull(Count(a.TypeID),'')
from
a
left join
b
on
a. TypeID=b. TypeID
from tb b
left join ta a
on a.typeid = b.typeid
group by b.type
select
b.[type],
isnull(Count(a.TypeID),0)
from
a
left join
b
on
a. TypeID=b. TypeID
select
b.[type],
isnull(Count(a.TypeID),0)
from
a
left join
b
on
a. TypeID=b. TypeID
group by
b.type
-- Author: flystone
-- Version:V1.001
-------------------------------------- Test Data: ta
If object_id('ta') is not null
Drop table ta
Go
Create table ta(ID int,Name nvarchar(2),TypeID int)
Go
Insert into ta
select 1,'a1',1 union all
select 2,'a2',1 union all
select 3,'a3',2 union all
select 4,'a4',2 union all
select 5,'a5',4
Go
-- Test Data: tB
If object_id('tB') is not null
Drop table tB
Go
Create table tB(TypeID int,Type nvarchar(2))
Go
Insert into tB
select 1,'t1' union all
select 2,'t2' union all
select 3,'t3' union all
select 4,'t4'
--Startselect b.type, count(a.id) as cnt
from tb b
left join ta a
on a.typeid = b.typeid
group by b.type--Result:
/*type cnt
---- -----------
t1 2
t2 2
t3 0
t4 1
*/
--End
from b
left join a
on a.typeid = b.typeid
b.[type],
isnull(Count(a.TypeID),0)
from
a
left join
b
on
a. TypeID=b. TypeID
group by b.[type]
没测试 SORRY
select
b.[type],
isnull(Count(a.TypeID),0)
from
b
left join
a
on
a. TypeID=b. TypeID
group by
b.type
-- Test Data: a
If object_id('a') is not null
Drop table a
Go
Create table a(ID int,Name nvarchar(2),TypeID int)
Go
Insert into a
select 1,'a1',1 union all
select 2,'a2',1 union all
select 3,'a3',2 union all
select 4,'a4',2 union all
select 5,'a5',4
Go
-- Test Data: b
If object_id('b') is not null
Drop table b
Go
Create table b(TypeID int,Type nvarchar(2))
Go
Insert into b
select 1,'t1' union all
select 2,'t2' union all
select 3,'t3' union all
select 4,'t4'
--Startselect
b.[type],
isnull(Count(a.TypeID),0) as TypeID
from
b
left join
a
on
a. TypeID=b. TypeID
group by
b.type
/*type TypeID
---- -----------
t1 2
t2 2
t3 0
t4 1
警告: 聚合或其他 SET 操作消除了空值。(4 行受影响)*/
from tb b
left join ta a
on a.typeid = b.typeid
group by b.type