有两个表:
ProductCategory(Id, Name)
Product(Id, Name, CategoryId)CategoryId是外键,引用ProductCategory的id现在要统计每种产品的数量,得出以下这样的结果Id CategoryName Count
1 aaaa 10
2 bbbb 0
3 cccc 0
4 dddd 20第二、第三即使没有这类产品也要返回一条记录,count为0现在以上要求我通过左外连接和分组做到了。
但接着的问题就是,现在要在Product表加一个字段IsNew,表示产品是不是新产品
然后统计每种产品的非新产品的数量,并且如果某类产品没有非新产品,也是要返回一条记录,Count为0
我尝试过用where,结果那些一个产品都没有的类别就会返回一个0,但那些只有新产品的类别就不会返回0,被where语句去掉了
ProductCategory(Id, Name)
Product(Id, Name, CategoryId)CategoryId是外键,引用ProductCategory的id现在要统计每种产品的数量,得出以下这样的结果Id CategoryName Count
1 aaaa 10
2 bbbb 0
3 cccc 0
4 dddd 20第二、第三即使没有这类产品也要返回一条记录,count为0现在以上要求我通过左外连接和分组做到了。
但接着的问题就是,现在要在Product表加一个字段IsNew,表示产品是不是新产品
然后统计每种产品的非新产品的数量,并且如果某类产品没有非新产品,也是要返回一条记录,Count为0
我尝试过用where,结果那些一个产品都没有的类别就会返回一个0,但那些只有新产品的类别就不会返回0,被where语句去掉了
也就是这样,承接上面的例子,如果aaaa类别中10个都是新产品,dddd类别中有5个是新产品
如何统计出如下结果:Id CategoryName Count
1 aaaa 0
2 bbbb 0
3 cccc 0
4 dddd 15
select
a.Id,
a.Name,
[count]=sum(case when b.IsNew=0 then 1 else 0 end)
from
ProductCategory a
left join
Product b on a.ID=b.CategoryId方法2:select
ID,
Name,
[count]=(select count(1) from Product where CategoryId=a.ID and IsNew=0)--IsNew=0非新品
from
ProductCategory a
c.Id,
ProductCategory.Name,
c.[count]
from
(select
a.Id,
[count]=count(a.Id)
from
Product b
left join
ProductCategory a on a.ID=b.CategoryId group by a.ID) c,ProductCategory
c.Id,
ProductCategory.Name,
c.[count]
from
(select
a.Id,
[count]=count(a.Id)
from
Product b
left join
ProductCategory a on a.ID=b.CategoryId group by a.ID) c,ProductCategory
where c.Id=ProductCategory.id
insert @t1 select 1,'种类一'
union all select 2,'种类二'
union all select 3,'种类三'
union all select 4,'种类四'
union all select 5,'种类五'
select * from @t1
declare @t2 table(id int ,name varchar(20),id_i int)
insert @t2 select 1,'产品名称1',1
union all select 2,'产品名称2',1
union all select 3,'产品名称3',1
union all select 4,'产品名称4',2
union all select 5,'产品名称5',2
union all select 6,'产品名称6',5
union all select 7,'产品名称7',4
union all select 8,'产品名称8',1
select * from @t2select a.id,a.name,count(b.id_i) from @t1 a left join @t2 b
on b.id_i=a.id
group by a.id,a.name
order by a.id
上面的实现
declare @ProductCategory table (Id int,CategoryName varchar(10))
insert into @ProductCategory
select 1,'aaaa' union all
select 2,'bbbb' union all
select 3,'cccc' union all
select 4,'dddd'
declare @Product table (Id int identity,Name varchar(10),CategoryId int,IsNew bit)
insert @Product
select 'P1',1,0 union all
select 'P2',1,1 union all
select 'P3',4,0 union all
select 'P4',4,0 union all
select 'P5',4,1--> 1
select
a.*,
[Count]=isnull(b.cn,0)
from
@ProductCategory a
left join
(select CategoryId,cn=count(1) from @Product where IsNew=0 group by CategoryId) b
on a.Id=b.CategoryId--> 2
select
a.*,
[Count]=(select count(1) from @Product where CategoryId=a.Id and IsNew=0)
from
@ProductCategory a--> 3
select
a.*,
[Count]=sum(case b.IsNew when 0 then 1 else 0 end)
from
@ProductCategory a
left join
@Product b
on a.ID=b.CategoryId
group by
a.Id,a.CategoryName/*
Id CategoryName Count
----------- ------------ -----------
1 aaaa 1
2 bbbb 0
3 cccc 0
4 dddd 2
*/--> 1、2性能差不多,3相对差少少
insert @t1 select 1,'种类一'
union all select 2,'种类二'
union all select 3,'种类三'
union all select 4,'种类四'
union all select 5,'种类五'
select * from @t1
declare @t2 table(id int ,name varchar(20),id_i int,isnew int)
insert @t2 select 1,'产品名称1',1,0
union all select 2,'产品名称2',1,1
union all select 3,'产品名称3',1,1
union all select 4,'产品名称4',2,0
union all select 5,'产品名称5',2,1
union all select 6,'产品名称6',5,0
union all select 7,'产品名称7',4,0
union all select 8,'产品名称8',1,1
select * from @t2select a.id,a.name,count(b.id_i) from @t1 a left join ( select * from @t2 where isnew = 0) b
on b.id_i=a.id
group by a.id,a.name
order by a.id
b.name,
count = (case b.isnew when 0 then 0 else count(b.id) end)
from product a
left join productcategory b
on a.categoryid = b.id
a.Id,
a.Name,
[count]=sum(case when b.IsNew=0 then 1 else 0 end)
from
ProductCategory a
left join
Product b on a.ID=b.CategoryId