编号 份数
1 100
2 300
3 600
上图是进行了一次group by形成的结果集，但现在的需求是要多查出一列，如下图
解释：占比就是 每个编号对应的数量 占总共份数的百分比
编号 份数 占比
1 100 10%
2 300 30%
3 600 60%
请问大家有什么好的方案，我用临时表，感觉查了俩便SQL效率差，百分百结贴，欢迎围观！
------解决方案--------------------
- SQL code
;WITH T AS (SELECT 1 AS bh, 100 as num union allSELECT 2 ,300union allSELECT 3 ,600)select * ,num*1.0/(select sum(num) from t)from t/*bh num ----------- ----------- ---------------------------------------1 100 0.1000000000002 300 0.3000000000003 600 0.600000000000(3 行受影响)*/
------解决方案--------------------
- SQL code
--> 测试数据：[tb]IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]GO CREATE TABLE [tb]([编号] INT,[份数] INT)INSERT [tb]SELECT 1,100 UNION ALLSELECT 2,300 UNION ALLSELECT 3,600--------------开始查询--------------------------SELECT *,LTRIM(CAST([份数]*100.0/SUM([份数]) OVER() AS DECIMAL(9,2)))+'%' FROM [tb]----------------结果----------------------------/* 编号 份数 ----------- ----------- ------------------------------------------1 100 10.00%2 300 30.00%3 600 60.00%(3 行受影响)*/
------解决方案--------------------
- SQL code
if object_id('test') is not null drop table testgocreate table test([编号] int,[份数] int)goinsert into testselect 1,100 union allselect 2,300 union allselect 3,600godeclare @sum floatselect @sum=sum([份数]) from testselect [编号],convert(varchar,convert(float,[份数])[email protected]*100)+'%' 比例 from test/*(3 row(s) affected)编号 比例----------- -------------------------------1 10%2 30%3 60%(3 row(s) affected)*/