Sql查询获得最大的评级总和 [英] Sql query to get the largest sum of Rating
本文介绍了Sql查询获得最大的评级总和的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
亲爱的专家,
我有一张下面的表格,
Dear Experts,
I have a Table Give below,
ITEM_ID | RATING
-----------------
1 | 5
1 | 2
1 | 5
1 | 1
2 | 5
2 | 5
2 | 5
3 | 4
我需要一个Sql查询来获得最大等级的Item_ID ...
例如....
I need a Sql query to get the Item_ID of largest sum of Rating...
Eg....
Sum of First Items Ratings = 5 + 2 + 5 + 1 = 13
Sum of Second Items Ratings = 5 + 5 + 5 = 15
Sum of Third Items Ratings = 4
So my out put is = Item_ID = 2
Coz, it having the highest rating 15 ...
PLease帮助我..
谢谢...
Dileep
PLease help me..
Thanks...
Dileep
推荐答案
你可以得到你的结果使用follwong查询
You can get your result using follwong Query
SELECT ITEM_ID, MAX(SUM(RATING)) AS HighestRate FROM TableName
GROUP BY ITEM_ID
要获得正确的评分,我建议您使用< a href =htt p://msdn.microsoft.com/en-US/library/ms176102.aspx> RANK() [ ^ ]如果两个(或更多)项目获得相同的点数(评级),则使用该函数。
To get proper rating, i would suggest you to use RANK()[^] function in case if two (or more) Items will get the same count of points (rating).
SELECT ITEM_ID, Points, RANK() OVER(ORDER BY Points DESC) AS [Rank]
FROM (
SELECT ITEM_ID, SUM(RATING) AS Points
FROM TableName
GROUP BY ITEM_ID) AS T
使用以下查询我希望它会根据你的结果给你结果预期。
Hi,
Use the below query i hope it will give you result as per your expection.
select ITEM_ID, Sum(RATING) As TotalRATING, Rank() over (order by Sum(RATING) desc) as CurrentRank from TableName group by ITEM_ID
OutPut-
ITEM_ID | TotalRATING | CurrentRank
2 | 15 | 1
1 | 13 | 2
3 | 4 | 3
谢谢
Zubair
OutPut-
ITEM_ID |TotalRATING |CurrentRank
2 | 15 | 1
1 | 13 | 2
3 | 4 | 3
Thanks
Zubair
这篇关于Sql查询获得最大的评级总和的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文