关联表分组统计问题
--主表（类型）结构
Create table #tb_type
(
id int identity(1,1),
name nvarchar(50)--名称
)
--子表（记录表）结构
Create table #tb_record
(
id int identity(1,1),
typeid int--外键
)

--测试数据
insert into #tb_type values('类别1')
insert into #tb_type values('类别2')
insert into #tb_type values('类别3')
insert into #tb_record values(1)
insert into #tb_record values(1)
insert into #tb_record values(1)
insert into #tb_record values(2)
insert into #tb_record values(2)

我想统计每个类别所占子表总数量的百分比，如果没有记录显示为0%

想要的结果：

类别名称 百分比
类别1 60%
类别2 40%
类别3 0%

[解决办法]

SQL code

--主表（类型）结构Create table #tb_type( id int identity(1,1), name nvarchar(50)--名称)--子表（记录表）结构Create table #tb_record( id int identity(1,1), typeid int--外键)--测试数据insert into #tb_type values(N'类别1')insert into #tb_type values(N'类别2')insert into #tb_type values(N'类别3')insert into #tb_record values(1)insert into #tb_record values(1)insert into #tb_record values(1)insert into #tb_record values(2)insert into #tb_record values(2)goSELECT     a.name,STR(count(b.ID)*100.0/c.con,2,0)+'%'FROM #tb_type AS a    LEFT JOIN #tb_record AS b ON a.ID=b.typeid    CROSS JOIN (SELECT COUNT(*) AS con FROM #tb_record) AS c GROUP BY a.name,c.con
[解决办法]
SQL code
select  a.name as 类别名称,  isnull(ltrim(cast(count(b.typeid)*100.0/(select count(1) from #tb_record) as dec(18,0))+'%','0%')from  #tb_type a,#tb_record bwhere   a.id=b.typeidgroup by   a.name
[解决办法]
SQL code
SELECT     a.name AS 类别名称,STR(count(b.ID)*100.0/c.con,2,0)+'%' AS 百分比FROM #tb_type AS a    LEFT JOIN #tb_record AS b ON a.ID=b.typeid    CROSS JOIN (SELECT COUNT(*) AS con FROM #tb_record) AS c GROUP BY a.name,c.con/*类别名称    百分比类别1    60%类别2    40%类别3     0%*/
[解决办法]
SQL code
select  a.name as 类别名称,  isnull(ltrim(cast(count(b.typeid)*100.0/(select count(1) from #tb_record) as dec(18,0))+'%','0%')from  #tb_type a left join #tb_record bon   a.id=b.typeidgroup by   a.name