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

[ling201003]

数据如下：
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要怎么写?

[chuifengde]

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, 81

DECLARE @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 desc

SELECT 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 行）*/

[黄_瓜]

--> 测试数据：[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 行受影响)
*/

[htl258_Tony]

--> 生成测试数据表: [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 行受影响)
*/

[黄_瓜]

[Quote=引用 2 楼 beirut 的回复:]

SQL code
--> 测试数据：[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,3……
[/Quote]
修改

--> 测试数据：[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 行受影响)

*/

[永生天地]

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 * 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 all

select '其他',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 行）

*/

[ling201003]

一楼的 树上的鸟儿 谢啦

[ling201003]

感激。。各位大虾们。。