为什么此查询:
UPDATE
(SELECT A.id trophy_id
FROM usertrophys A,
userinfo B
WHERE A.user_id = B.id
AND B.cidade_new_id = 25755
AND ban = 0
AND A.platform = 'vita'
ORDER BY points DESC, platinum DESC, gold DESC, silver DESC, bronze DESC, total DESC) A
LEFT JOIN rankgeralcidade B USING (trophy_id)
SET B.rank = @r:= (@r+1)
WHERE platform = 'vita'
AND meninas = 0
AND cidade_id = '25755'
AND trophy_id = B.trophy_id;</code></pre>
以这个结果结尾 select * from rankgeralcidade where cidade_id = 25755 and platform = 'vita' ;
+--------+-----------+----------+---------+------+-----------+-----------+---------------------+---------+---------------------+-----------+
| id | trophy_id | platform | user_id | rank | last_rank | best_rank | best_rank_date | meninas | date_updated | cidade_id |
+--------+-----------+----------+---------+------+-----------+-----------+---------------------+---------+---------------------+-----------+
| 138300 | 86412 | vita | 2774 | 1 | 1 | 1 | 2013-02-09 18:07:25 | 0 | 2012-12-25 05:20:30 | 25755 |
| 182075 | 120401 | vita | 3546 | 2 | 0 | 0 | 2014-01-25 19:04:55 | 0 | 2014-01-25 19:04:55 | 25755 |
+--------+-----------+----------+---------+------+-----------+-----------+---------------------+---------+---------------------+-----------+
2 rows in set (0.00 sec)
以下选择有此返回 mysql> SELECT @r:= (@r+1), B.*
FROM
(SELECT A.id trophy_id
FROM usertrophys A,
userinfo B
WHERE A.user_id = B.id
AND B.cidade_new_id = 25755
AND ban = 0
AND A.platform = 'vita'
ORDER BY points DESC, platinum DESC, gold DESC, silver DESC, bronze DESC, total DESC) A
LEFT JOIN rankgeralcidade B USING (trophy_id);
+-------------+--------+-----------+----------+---------+------+-----------+-----------+---------------------+---------+---------------------+-----------+
| @r:= (@r+1) | id | trophy_id | platform | user_id | rank | last_rank | best_rank | best_rank_date | meninas | date_updated | cidade_id |
+-------------+--------+-----------+----------+---------+------+-----------+-----------+---------------------+---------+---------------------+-----------+
| 1 | 182075 | 120401 | vita | 3546 | 2 | 0 | 0 | 2014-01-25 19:04:55 | 0 | 2014-01-25 19:04:55 | 25755 |
| 2 | 138300 | 86412 | vita | 2774 | 1 | 1 | 1 | 2013-02-09 18:07:25 | 0 | 2012-12-25 05:20:30 | 25755 |
+-------------+--------+-----------+----------+---------+------+-----------+-----------+---------------------+---------+---------------------+-----------+
2 rows in set (0.01 sec)
主要问题是,为什么从rankgeralcidade中使用ID =" 182075",而rank = 2进行更新?在同一查询中,仅替换更新以选择结果似乎都很好。
这是select
查询:
SELECT @r:= (@r+1) as rank, B.*
FROM (SELECT A.id trophy_id
FROM usertrophys A join
userinfo B
on A.user_id = B.id
WHERE B.cidade_new_id = 25755 AND ban = 0 AND A.platform = 'vita'
ORDER BY points DESC, platinum DESC, gold DESC, silver DESC, bronze DESC, total DESC
) A LEFT JOIN
rankgeralcidade B
USING (trophy_id);
外部查询没有order by
子句。意味着未定义结果的顺序。您在子查询中拥有order by
子句并没有什么不同。偶数如果保留了订购(不能保证它是),则join
可能会导致数据以不同的方式订购。
update
和select
会产生不同的订单对我来说很有意义。
如果您想要稳定的值分配,请使用order by
的CC_9子句,对于select
和order by
。
编辑:
我没有意识到MySQL不支持join
(因为我在更新中不使用order by
)。您仍然可以使用一个子查询:
update rankgeralcidade b join
(<the select query here>
) toupdate
on b.trophy_id = toupdate.trophy_id
set B.rank = toupdate.rank;