SELECT CASE WHEN GROUPING(vProvince) = 1 THEN '合计' ELSE vProvince END vProvince
,COUNT(*) iCount
,cast(cast(sum(100.0)/(
select count(*) FROM Ac_RecordDetail
WHERE vProvince IN ('北京市','福建省','甘肃省')
) as decimal(10,2)) as varchar)+'%'
FROM Ac_RecordDetail
WHERE vProvince IN ('北京市','福建省','甘肃省')
GROUP BY vProvince WITH ROLLUP
ORDER BY iCount DESC
,COUNT(*) iCount
,cast(cast(sum(100.0)/(
select count(*) FROM Ac_RecordDetail
WHERE vProvince IN ('北京市','福建省','甘肃省')
) as decimal(10,2)) as varchar)+'%'
FROM Ac_RecordDetail
WHERE vProvince IN ('北京市','福建省','甘肃省')
GROUP BY vProvince WITH ROLLUP
ORDER BY iCount DESC
,COUNT(*) iCount
,cast(cast(sum(100.0)/(
select count(*) FROM Ac_RecordDetail
WHERE vProvince IN ('北京市','福建省','甘肃省')
) as decimal(10,2)) as varchar)+'%' iPercent
FROM Ac_RecordDetail
WHERE vProvince IN ('北京市','福建省','甘肃省')
GROUP BY vProvince WITH ROLLUP
ORDER BY iCount DESC
虽然结果是正确的,但是我忘了加上一个条件。
就是不要改变刚才的SQL,我要它得到结果集,然后再从结果中得到我想要的比例。
比如:
SELECT ... FROM
(SELECT CASE WHEN GROUPING(vProvince) = 1 THEN '合计' ELSE vProvince END vProvince,COUNT(*) iCount
FROM Ac_RecordDetail
WHERE vProvince IN ('北京市','福建省','甘肃省')
GROUP BY vProvince WITH ROLLUP ) A ...谢谢!