日期:2014-05-17  浏览次数:20420 次

如何能查出如下的结果,百分百结贴,欢迎围观
编号 份数
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 all
SELECT 2 ,300union all
SELECT 3 ,600

)

select * ,num*1.0/(select sum(num) from t)
from t

/*
bh          num         
----------- ----------- ---------------------------------------
1           100         0.100000000000
2           300         0.300000000000
3           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 ALL
SELECT 2,300 UNION ALL
SELECT 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 test
go
create table test([编号] int,[份数] int)
go
insert into test
select 1,100 union all
select 2,300 union all
select 3,600
go

declare @sum float
select @sum=sum([份数]) from test
select [编号],convert(varchar,convert(float,[份数])/@sum*100)+'%' 比例 from test

/*

(3 row(s) affected)
编号          比例
----------- -------------------------------
1           10%
2           30%
3           60%

(3 row(s) affected)

*/