Sql查询获得最大的评级总和 [英] Sql query to get the largest sum of Rating

问题描述

亲爱的专家，



我有一张下面的表格，

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屋！