共有两张表 分别如下:表 Category (产品类别表)
-------------------------------------
CategoryID CategoryName
1 电视
2 DVD
3 电脑
4 冰箱
-------------------------------------
表 Products (产品表)
-------------------------------------
ProductID CategoryID ProductName
1 1 长虹电视
2 2 松下DVD
3 3 HP
4 3 DEL
-------------------------------------
现在要统计每个产品分类中的产品总记录数和百分比 比如:CategoryID CategoryName ProductNum Percentage
1 电视 1 25%
2 DVD 1 25%
3 电脑 2 50%
4 冰箱 0 0
-------------------------------------
CategoryID CategoryName
1 电视
2 DVD
3 电脑
4 冰箱
-------------------------------------
表 Products (产品表)
-------------------------------------
ProductID CategoryID ProductName
1 1 长虹电视
2 2 松下DVD
3 3 HP
4 3 DEL
-------------------------------------
现在要统计每个产品分类中的产品总记录数和百分比 比如:CategoryID CategoryName ProductNum Percentage
1 电视 1 25%
2 DVD 1 25%
3 电脑 2 50%
4 冰箱 0 0
CategoryID CategoryName ProductNum Percentage
1 电视 1 25%
2 DVD 1 25%
3 电脑 2 50%
4 冰箱 0 0
*/
create table #Category(CategoryID int,CategoryName varchar(20))
insert into #Category select 1,'电视'
insert into #Category select 2,'DVD'
insert into #Category select 3,'电脑'
insert into #Category select 4,'冰箱'create table #Products(ProductID int,CategoryID int,ProductName varchar(20))
insert into #Products select 1,1,'长虹电视'
insert into #Products select 2,2,'松下DVD'
insert into #Products select 3,3,'HP'
insert into #Products select 4,3,'DEL'
goselect c.CategoryID,c.CategoryName,
isnull(p.ProductNum,0) as ProductNum,
ltrim(cast(isnull(p.ProductNum,0)*100.0/(select count(*) from #products) as decimal(9,2)))+'%' as Percentage
from #Category as c
left outer join (select CategoryID,count(*) as ProductNum
from #Products group by CategoryID) as p
on c.CategoryID=p.CategoryIDgo
drop table #Category,#Products/*
CategoryID CategoryName ProductNum Percentage
----------- -------------------- ----------- ------------
1 电视 1 25.00%
2 DVD 1 25.00%
3 电脑 2 50.00%
4 冰箱 0 0.00%(所影响的行数为 4 行)*/
insert into @a select 1,'电视'
insert into @a select 2,'DVD'
insert into @a select 3,'电脑'
insert into @a select 4,'冰箱'declare @b table (ProductID int,CategoryID int,ProductName varchar(20))
insert into @b select 1,1,'长虹电视'
insert into @b select 2,2,'松下DVD'
insert into @b select 3,3,'HP'
insert into @b select 4,3,'DEL'select a.CategoryID,a.CategoryName,
sum(case when b.ProductID is null then 0 else 1 end) as ProductNum,
ltrim(cast(sum(case when b.ProductID is null then 0 else 1 end)*100.0/(select count(*) from @b) as int))+'%' as Percentage
from @a a left join
@b b on a.CategoryID=b.CategoryID
group by a.CategoryID,a.CategoryName
order by a.CategoryID,a.CategoryNameCategoryID CategoryName ProductNum Percentage
1 电视 1 25%
2 DVD 1 25%
3 电脑 2 50%
4 冰箱 0 0%
a.CategoryID,
a.CategoryName,
count(b.ProductName) as ProductNum,
rtrim(cast(count(b.ProductName)*100.0/(select count(1) from #Products) as decimal(18,2)))+'%' as Percentage
from
#Category a
left join
#Products b on a.CategoryID=b.CategoryID
group by a.CategoryID, a.CategoryName
order by a.CategoryID
select
distinct
a.CategoryID,
a.CategoryName,
count(b.ProductName)over(partition by a.CategoryID) as ProductNum,
count(b.ProductName)over(partition by a.CategoryID)*100.0/count(1)over()
from
#Category a
left join
#Products b on a.CategoryID=b.CategoryID
order by a.CategoryID