34,598
社区成员
发帖
与我相关
我的任务
分享
select type ,sum(value) as value from (
select 'a'as type,1 as value
union all select 'a',2
union all select 'b',1
union all select 'b',3
union all select 'c',5 ) a
group by type
------------
type value
a 3
b 4
c 5
------------
但是我想显示的是这样:
type value sum bl
a 3 12 25%
b 4 12 33.3%
c 5 12 41.67%
请问如何实现
--在sqlserver2012 下有用的语句
select distinct type ,sum(a.value) over( partition by type )as value,sum(a.value) over() as rn
,cast(cast(sum(a.value) over( partition by type )*1.0/sum(a.value) over() *100 as decimal(9,2)) as varchar)+'%'
from (
select 'a'as type,1 as value
union all select 'a',2
union all select 'b',1
union all select 'b',3
union all select 'c',5 ) a
--结果展示
/*
type value rn
---- ----------- ----------- -------------------------------
a 3 12 25.00%
b 4 12 33.33%
c 5 12 41.67%
(3 行受影响)
*/
;with t
as(
select 'a'as type,1 as value
union all select 'a',2
union all select 'b',1
union all select 'b',3
union all select 'c',5
)
SELECT type,value,SumValue AS [sum],CONVERT(VARCHAR,ROUND(CONVERT(FLOAT,Value)/CONVERT(FLOAT,SumValue)*100,2))+'%' AS lr FROM
(
SELECT type,SUM(Value) Value,(SELECT SUM(value) FROM t) SumValue FROM t GROUP BY type
)a
/*
type value sum lr
---- ----------- ----------- -------------------------------
a 3 12 25%
b 4 12 33.33%
c 5 12 41.67%
(3 行受影响)
*/
with temp as(select * from Table11)
select Name,count(Name) 数量,CONVERT(decimal(18,2), COUNT(Name)*100.0/(select COUNT(Name) from temp)) 所占百分比 from temp group by Name,Content
;with t
as(
select 'a'as type,1 as value
union all select 'a',2
union all select 'b',1
union all select 'b',3
union all select 'c',5
)
select type,ltrim(SUM(value)*100.0/(select SUM(value) from t))+'%' as per from t
group by type
/*
-------------------------
type per
a 25.000000000000%
b 33.333333333333%
c 41.666666666666%
-------------------------
*/