计算重量排名前三与剩余的所占总重量的百分比 sql

Question

数据如下：
ID    VID     Weight
1      3        20
2      2        30
3      3        34
4      1        40
5      4        21
6      5        24
7      1        50
8      7        81
想要得到的结果：
VID   Percentage
1         30%
7         27%
3         18%
其它      25%sql要怎么写?

Answer 1

declare @a table(ID INT, VID INT, WEIGHT INT)
insert @a select 1, 3, 20
union all select 2, 2, 30
union all select 3, 3, 34
union all select 4, 1, 40
union all select 5, 4, 21
union all select 6, 5, 24
union all select 7, 1, 50
union all select 8, 7, 81DECLARE @b TABLE(a int,b DECIMAL(20,2))
INSERT @b SELECT TOP 3 vid,SUM(WEIGHT)*1.0/(SELECT SUM(WEIGHT ) FROM @a )
FROM @a GROUP BY vid ORDER BY 2 descSELECT ltrim(a) VID ,ltrim(b*100)+'%' Percentage FROM  @b
UNION ALL 
SELECT '其它',ltrim((1-sum(b))*100)+'%' from @b--result
/*VID          Percentage                     
------------ ------------------------------ 
1            30.00%
7            27.00%
3            18.00%
其它           25.00%（所影响的行数为 4 行）*/

Answer 2

--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[VID] int,[Weight] int)
insert [tb]
select 1,3,20 union all
select 2,2,30 union all
select 3,3,34 union all
select 4,1,40 union all
select 5,4,21 union all
select 6,5,24 union all
select 7,1,50 union all
select 8,7,81--------------------------------查询开始------------------------------select top 3 [VID],Percentage=ltrim(cast(sum([Weight])*100.0/(select sum([Weight]) from [tb]) as decimal(9,2)))+'%'
from [tb] 
group by [VID]
order by [VID]
/*
VID         Percentage
----------- ------------------------------------------
1           30.00%
2           10.00%
3           18.00%(3 行受影响)
*/

Answer 3

--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([ID] [int],[VID] [int],[Weight] [int])
INSERT INTO [tb]
SELECT '1','3','20' UNION ALL
SELECT '2','2','30' UNION ALL
SELECT '3','3','34' UNION ALL
SELECT '4','1','40' UNION ALL
SELECT '5','4','21' UNION ALL
SELECT '6','5','24' UNION ALL
SELECT '7','1','50' UNION ALL
SELECT '8','7','81'
-->SQL查询如下:
;WITH t AS 
(
SELECT TOP 3 LTRIM(VID) VID,SUM([Weight]) AS sumWeight 
FROM [tb]
GROUP BY VID
ORDER BY sumWeight DESC
),
t1 AS 
(
SELECT '其它' AS VID,sum(sumWeight) sumWeight
FROM (
SELECT VID,SUM([Weight]) AS sumWeight 
FROM [tb]
GROUP BY VID
EXCEPT
SELECT * FROM t
) t1
)
SELECT VID,ltrim(cast(sumWeight*100./(SELECT SUM([Weight]) FROM tb) AS dec(9,2)))+'%' AS 百分比
FROM (
SELECT * FROM t
UNION ALL
SELECT * FROM t1
) AS t
/*
VID          百分比
------------ ------------------------------------------
1            30.00%
7            27.00%
3            18.00%
其它           25.00%(4 行受影响)
*/

Answer 4

修改
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[VID] int,[Weight] int)
insert [tb]
select 1,3,20 union all
select 2,2,30 union all
select 3,3,34 union all
select 4,1,40 union all
select 5,4,21 union all
select 6,5,24 union all
select 7,1,50 union all
select 8,7,81--------------------------------查询开始------------------------------
select [VID],Percentage from
(
select top 3 [VID]=ltrim([VID]),Percentage=ltrim(cast(sum([Weight])*100.0/(select sum([Weight]) from [tb]) as decimal(9,2)))+'%'
from [tb] 
group by [VID]
order by sum([Weight]) desc
) t
union all 
select '其它',ltrim(cast(
(
(select sum([Weight]) from [tb])-
(select sum ([Weight]) from(select top 3 [Weight]=sum([Weight]) from [tb] group by [VID] order by sum([Weight])desc)t))*100.0/
(select sum([Weight]) from [tb]) as decimal(9,2)))+'%'/*
VID          Percentage
------------ ------------------------------------------
1            30.00%
7            27.00%
3            18.00%
其它           25.00%(4 行受影响)*/

Answer 5

create table tb (id int,vid int,weight int)
insert tb
select 1,3,20 union all
select 2,2,30 union all
select 3,3,34 union all
select 4,1,40 union all
select 5,4,21 union all
select 6,5,24 union all
select 7,1,50 union all
select 8,7,81select * from(
select top 3  ltrim(vid) vid, left(sum(Weight)*100.0/hj,2)+'%' Percentage
from tb t1,(select sum(Weight) as hj from tb) t2
group by vid,hj 
order by sum(Weight) desc
)t
union allselect '其他',left(100-
(select sum(s) from(
select top 3 vid, sum(Weight) as s
from tb t1,(select sum(Weight) as hj from tb) t2
group by vid,hj 
order by sum(Weight) desc
)t) *100.0/(select sum(Weight) as hj from tb),2)+'%' /*
vid          Percentage 
------------ ---------- 
1            30%
7            27%
3            18%
其他           25%（所影响的行数为 4 行）*/