MySQL 在一个查询中选择所有数据并更新

Question

我有 3 张 table 。其中之一存储 users_data。

我运行这个sql代码:

SELECT @rownum := @rownum + 1 AS position,
user_id, score, user_type
FROM users_data
ORDER BY score DESC

结果如下:

我有 2 张 table 供这样的用户使用

我的 table

users1

+----+----------+----------+
| id | username | position |
+----+----------+----------+
|  1 | uname    |        0 |
|  2 | uname2   |        0 |
|  3 | uname3   |        0 |
|  5 | uname5   |        0 |
| 11 | uanme11  |        0 |
| 12 | uname12  |        0 |
+----+----------+----------+

users0

+-----+----------+----------+
| id  | username | position |
+-----+----------+----------+
|   1 | uname    |        0 |
| 111 | uname111 |        0 |
| 138 | uname138 |        0 |
| 241 | uname241 |        0 |
+-----+----------+----------+

我想在一个查询中运行 SELECT sql 时更新用户位置。

if user_type is 0, update users0.position = @rownum
if user_type is 1, update users1.position = @rownum

结果必须是这样的:

users1

+----+----------+----------+
| id | username | position |
+----+----------+----------+
|  1 | uname    |     1100 |
|  2 | uname2   |     1100 |
|  3 | uname3   |     1075 |
|  5 | uname5   |     1075 |
| 11 | uanme11  |     1075 |
| 12 | uname12  |     1175 |
+----+----------+----------+

users0

+-----+----------+----------+
| id  | username | position |
+-----+----------+----------+
|   1 | uname    |     1075 |
| 111 | uname111 |     1025 |
| 138 | uname138 |     1025 |
| 241 | uname241 |     1025 |
+-----+----------+----------+

Answer 1

update users1,users0
set users1.position = (
            select v.position
            from
            (SELECT 
            @rn1 := @rn1 + 1 AS position,
            users_id, score, user_type
            FROM (select @rn1:=0) rn, users_data ud
            ORDER BY score DESC
            ) v
            where v.users_id = users1.id and v.user_type = 1
            ) 
,
     users0.position = (
        select s.position
        from
        (SELECT 
        @rn := @rn + 1 AS position,
        users_id, score, user_type
        FROM (select @rn:=0) rn, users_data ud
        ORDER BY score DESC
        ) s
        where s.users_id = users0.id and s.user_type = 0
        ) 

where 1 = 1
;

结果

+-----+------+----------+----------+
| src | id   | username | position |
+-----+------+----------+----------+
| u1  |   12 | uname12  |        1 |
| u1  |    1 | uname    |        2 |
| u1  |    2 | uname2   |        3 |
| u1  |   11 | uanme11  |        4 |
| u1  |    5 | uname5   |        5 |
| u1  |    3 | uname3   |        6 |
| u0  |    1 | uname    |        7 |
| u0  |  111 | uname111 |        8 |
| u0  |  138 | uname138 |        9 |
| u0  |  241 | uname241 |       10 |
+-----+------+----------+----------+
10 rows in set (0.00 sec)