select 类别1, 类别2, 数量=count(*) , 比例=cast(count(*)*100/(select count(*) from 表1 t where 类别1=表1.类别1) as varchar)+'%' from 表1 group by 类别1, 类别2
select TA.type1, TA.type2, count(*) / (count(*) + as count_2) from TA, (select type1, count(*) as count_2 from TA) TB where TA.type1 = TB.type1 group by TA.type1,TA.type2
DECLARE @TB TABLE(TYPE1 VARCHAR(2),TYPE2 VARCHAR(2)) INSERT @TB SELECT 'A', 'aa' UNION ALL SELECT 'A', 'aa' UNION ALL SELECT 'B', 'cc' UNION ALL SELECT 'B', 'dd' UNION ALL SELECT 'B', 'cc' UNION ALL SELECT 'B', 'cc' SELECT TYPE1,TYPE2,COUNT(*) AS QTY,RTRIM(CAST(COUNT(*)*1.0*100/(SELECT COUNT(*) FROM @TB WHERE TYPE1=A.TYPE1) AS INT))+'%' FROM @TB AS A GROUP BY TYPE1,TYPE2 /* TYPE1 TYPE2 QTY ----- ----- ----------- ------------- A aa 2 100% B cc 3 75% B dd 1 25%(3 row(s) affected) */
, 比例=cast(count(*)*100/(select count(*) from 表1 t where 类别1=表1.类别1) as varchar)+'%'
from 表1
group by 类别1, 类别2
TA.type2,
count(*) / (count(*) + as count_2)
from TA,
(select type1, count(*) as count_2 from TA) TB
where TA.type1 = TB.type1
group by TA.type1,TA.type2
INSERT @TB
SELECT 'A', 'aa' UNION ALL
SELECT 'A', 'aa' UNION ALL
SELECT 'B', 'cc' UNION ALL
SELECT 'B', 'dd' UNION ALL
SELECT 'B', 'cc' UNION ALL
SELECT 'B', 'cc'
SELECT TYPE1,TYPE2,COUNT(*) AS QTY,RTRIM(CAST(COUNT(*)*1.0*100/(SELECT COUNT(*) FROM @TB WHERE TYPE1=A.TYPE1) AS INT))+'%'
FROM @TB AS A
GROUP BY TYPE1,TYPE2
/*
TYPE1 TYPE2 QTY
----- ----- ----------- -------------
A aa 2 100%
B cc 3 75%
B dd 1 25%(3 row(s) affected)
*/
我觉得后面只要group by 类别2 就够了