如何使用 ROW_NUMBER() 向 MySQL 显示添加行号

debugcn 发表于 Dev

用户1972031

如何使用 ROW_NUMBER() 函数（必需）在 Mysql 表显示上显示行号？

mysql> select * from work;
+------+-----------+
| name | work_days |
+------+-----------+
| john |         5 |
| jane |         7 |
| jane |         2 |
| john |         3 |
+------+-----------+
4 rows in set (0.01 sec)

不使用 ROW_NUMBER()：

mysql> SELECT name, 
    -> AVG(work_days) AS workday_average, 
    -> COUNT(*) as count
    -> FROM work
    -> GROUP BY name
    -> HAVING workday_average > 2
    -> ORDER BY workday_average ASC, count DESC;
+------+-----------------+-------+
| name | workday_average | count |
+------+-----------------+-------+
| john |          4.0000 |     2 |
| jane |          4.5000 |     2 |
+------+-----------------+-------+
2 rows in set (0.00 sec)

尝试使用 ROW_NUMBER() 添加行号列时出现以下错误。

mysql> SELECT name, 
    -> ROW_NUMBER() over(PARTITION BY name ORDER BY work_days) as row_num,
    -> AVG(work_days) AS workday_average, 
    -> COUNT(*) as count
    -> FROM work
    -> GROUP BY name
    -> HAVING workday_average > 2
    -> ORDER BY workday_average ASC, count DESC;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(PARTITION BY name ORDER BY work_days) as row_num,
AVG(work_days) AS workday_ave' at line 2
mysql>

蒂姆·比格莱森

窗口函数在GROUP BY聚合发生后进行评估，因此在上使用分区没有多大意义name，因为此时的每条记录都可以保证具有不同的名称。很可能，你想要这样的东西：

SELECT
    name, 
    ROW_NUMBER() OVER (ORDER BY AVG(work_days), COUNT(*) DESC) AS row_num,
    AVG(work_days) AS workday_average, 
    COUNT(*) AS count
FROM work
GROUP BY
    name
HAVING
   workday_average > 2
ORDER BY
    workday_average,
    count DESC;

但这当然假设您使用的是 MySQL 8+。如果没有，那么ROW_NUMBER将无法使用。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。