时间戳

首页 > 解决方案 > 如何在 MySQL 中使用 join 和 order by 子句生成自动增量临时字段

mysql - 如何在 MySQL 中使用 join 和 order by 子句生成自动增量临时字段

问题描述

这是我的人民表

+----+------------------+------------+
| id | name             | address    |
+----+------------------+------------+
|  1 | Tony Stark       | Chicago    |
|  2 | Natasha Romanoff | Boston     |
|  3 | Steve Rogers     | Arkansas   |
|  4 | Bruce Banner     | Long Beach |
+----+------------------+------------+

这是我的角色表

+----+-----------------+-----------+
| id | role_name       | people_id |
+----+-----------------+-----------+
|  1 | Iron Man        |         1 |
|  2 | Black Widow     |         2 |
|  3 | Captain America |         3 |
|  4 | Hulk            |         4 |
+----+-----------------+-----------+

我想从这两个表中获取数据，并使用此查询生成自动递增序号字段

SELECT @rownum := @rownum + 1 as no, peoples.name, roles.role_name
FROM peoples
CROSS JOIN (select @rownum := 0) r
JOIN roles ON roles.people_id = peoples.id
ORDER BY peoples.name ASC

但结果不是我所期望的。这是结果

+------+------------------+-----------------+
| no   | name             | role_name       |
+------+------------------+-----------------+
|    4 | Bruce Banner     | Hulk            |
|    2 | Natasha Romanoff | Black Widow     |
|    3 | Steve Rogers     | Captain America |
|    1 | Tony Stark       | Iron Man        |
+------+------------------+-----------------+

也许是因为 JOIN 和 ORDER BY。如何解决这个问题，所以我得到一个序列号？

标签： mysqlauto-increment

解决方案

如果要在peoples表上按名称应用行号，则应首先在子查询中生成它，然后加入该子查询：

SELECT
    p.no,
    p.name,
    r.role_name
FROM
(
    SELECT id, name, address, @rownum:=@rownum + 1 AS no
    FROM peoples
    ORDER BY name
) p
CROSS JOIN (SELECT @rownum := 0) t
INNER JOIN roles r
    ON p.id = r.people_id
ORDER BY
    p.no;

推荐阅读