mysql - 在 View 中对项目编号进行排名，MYSQL

Question

我通过以下语句创建了一个 View 。

CREATE VIEW
view_projectHour
AS
SELECT pno
, SUM( hours ) AS total_hours
FROM works_on
GROUP BY pno
ORDER BY total_hours DESC

现在，如何在此 View 中实现排名？我希望对项目进行排名。小时数最高的项目必须排在第 1 位，并放在最前面，依此类推。也有时间相同的项目。

Answer 1

不幸的是，MySQL 缺乏对分析函数的支持。特别是 RANK() 和 RANK_DENSE()。

要模拟 RANK() 你可以这样做

SELECT pno, total_hours, rank
  FROM
( 
  SELECT pno, total_hours,
         @n := @n + 1 rnum, @r := IF(@h = total_hours, @r, @n) rank, @h := total_hours
    FROM
  (
    SELECT pno, SUM(hours) total_hours
      FROM works_on 
     GROUP BY pno
  ) q CROSS JOIN (SELECT @n := 0, @r := 0, @h := NULL) i
   ORDER BY total_hours DESC, pno
) t

示例输出:

| PNO | TOTAL_HOURS | RANK ||-----|-------------|------||   3 |          61 |    1 ||   1 |          40 |    2 ||   2 |          40 |    2 ||   4 |          10 |    4 |

To emulate DENSE_RANK() you can do

SELECT pno, total_hours, rank
  FROM
(
  SELECT pno, total_hours,
         @r := IF(@h = total_hours, @r, @r + 1) rank, @h := total_hours
    FROM
  (
    SELECT pno, SUM(hours) total_hours
      FROM works_on 
     GROUP BY pno
  ) q CROSS JOIN (SELECT @r := 0, @h := NULL) i
   ORDER BY total_hours DESC, pno
) t

示例输出:

| PNO | TOTAL_HOURS | RANK ||-----|-------------|------||   3 |          61 |    1 ||   1 |          40 |    2 ||   2 |          40 |    2 ||   4 |          10 |    3 |

注意: 如果您不介意在结果集中有一两个额外的列，则可以放弃外部 SELECT。/p>

这是 SQLFiddle 演示