日期:2014-05-17  浏览次数:20717 次

请教一个关于统计并排序的sql写法之二
有三张表,主要的字段如下:
信息表tb1:信息id,信息录入单位id,录入时间
信息评分表tb2:信息id,信息得分 (同一篇信息可以有多次的评分)
单位表tb3:单位id,单位名称

想实现的功能是:全部单位按照2013年录入的信息得分总分进行排行。如:

名次 单位id  单位名称 得分
1       25     销售部   56
2       11     综合部   43
2       12     后勤部   43
3       6      市场部   25

求详细代码。谢谢!

------解决方案--------------------

-- 不考虑并列排名的话
SELECT ROW_Number() OVER(ORDER BY s.总分) AS 名次, s.单位id, s.单位名称, s.总分 
FROM (SELECT t3.单位id, t3.单位名称, SUM(t2.信息得分) AS 总分 
FROM tb3 INNER JOIN tb1 t1 ON t3.单位id=t1.信息录入单位id 
INNER JOIN tb2 t2 ON t1.信息id = t2.信息id 
WHERE Year(t1.录入时间) = 2013 
GROUP BY t3.单位id, t3.单位名称 ) s 
ORDER BY s.总分 

-- 考虑并列排名
SELECT RANK() OVER(ORDER BY s.总分) AS 名次, s.单位id, s.单位名称, s.总分 
FROM (SELECT t3.单位id, t3.单位名称, SUM(t2.信息得分) AS 总分 
FROM tb3 INNER JOIN tb1 t1 ON t3.单位id=t1.信息录入单位id 
INNER JOIN tb2 t2 ON t1.信息id = t2.信息id 
WHERE Year(t1.录入时间) = 2013 
GROUP BY t3.单位id, t3.单位名称 ) s 
ORDER BY s.总分 


------解决方案--------------------
借用1楼,改了N...点。

SELECT dense_rank() OVER(ORDER BY s.总分 desc) AS 名次, s.单位id, s.单位名称, s.总分 
FROM (SELECT t3.单位id, t3.单位名称, SUM(t2.信息得分) AS 总分 
FROM tb3 t3 INNER JOIN tb1 t1 ON t3.单位id=t1.信息录入单位id 
INNER JOIN tb2 t2 ON t1.信息id = t2.信息id 
WHERE Year(t1.录入时间) = 2013 and 审核类别=1
GROUP BY t3.单位id, t3.单位名称 ) s 
------解决方案--------------------
try this,

select dense_rank() over(order by sum(t2.信息得分) desc) '名次',
       t3.单位id,
       t3.单位名称,
       sum(t2.信息得分) '得分'
 from tb1 t1
 inner join tb2 t2 on t1.信息id=t2.信息id
 inner join tb3 t3 on t1.信息录入单位id=t3.单位id
 where year(t1.录入时间)=2013 and t2.审核类别=1
 group by t3.单位id,t3.单位名称