所以,我有一个表(order_items
),看起来像:
+----------+-------+
| id | order |
|----------+-------|
| 6c1e1f12 | 4 |
|----------+-------|
| 6c1e1f12 | 7 |
|----------+-------|
| 6c1e1f12 | 2 |
|----------+-------|
| 6c1e1f12 | 3 |
|----------+-------|
| 6c1e1f12 | 6 |
|----------+-------|
| 5d9f1892 | 5 |
|----------+-------|
| 5d9f1892 | 1 |
+----------+-------+
我试图做的是:为每组现有id
记录"重置"order
值,从零开始(每次)。所以结果应该是这样的:
+----------+-------+
| id | order |
|----------+-------|
| 6c1e1f12 | 0 |
|----------+-------|
| 6c1e1f12 | 1 |
|----------+-------|
| 6c1e1f12 | 2 |
|----------+-------|
| 6c1e1f12 | 3 |
|----------+-------|
| 6c1e1f12 | 4 |
|----------+-------|
| 5d9f1892 | 0 |
|----------+-------|
| 5d9f1892 | 1 |
+----------+-------+
我不介意实际的顺序——或者之前行的排序方式,从某种意义上说,例如,如果以前12
为order
的行在此之后变成了0
行......和某种。
我有这个查询以我想要的方式返回值:
SELECT IF(@prev != a.id, @curr := 0, @curr := @curr + 1) AS order, @prev := a.id AS item_id
FROM (SELECT id FROM order_items, (SELECT @curr := 0, @prev := '') b ORDER BY id) a;
。但是我正在构建的UPDATE
查询只是将每个order
值设置为1
,而不管:(
UPDATE order_items, (
SELECT IF(@prev != a.id, @curr := 0, @curr := @curr + 1) AS order, @prev := a.id as item_id
FROM (SELECT id FROM order_items, (SELECT @curr := 0, @prev := '') b ORDER BY id) a
) AS tmp
SET order_items.order = tmp.order
WHERE order_items.id = tmp.item_id;
这是一个带有初始模式和一些数据的 SQL 小提琴。
MySQL 参考手册警告说,用户定义变量的行为无法保证:
作为一般规则,除了在SET语句中之外,您永远不应该为用户变量赋值并在同一语句中读取该值。
你可能会得到你期望的结果,但这不能保证。
涉及用户变量的表达式的计算顺序未定义。
https://dev.mysql.com/doc/refman/5.7/en/user-variables.html
考虑到这一点,我们确实观察到了精心构造的 SQL 语句的一致行为。
我已经使用了这种类型的模式,在MySQL 5.6中使用了用户定义的变量,并取得了成功。(我希望随着MySQL的未来版本,优化器的改进,这可能不会继续工作。
鉴于当前声明的目的,我倾向于这样写:
UPDATE (
SELECT @curr := IF(o.id <> @prev, 0, @curr + 1) AS order
, @prev := o.id AS item_id
, o.pk_col
FROM ( SELECT @curr := 0, @prev := '') i
CROSS
JOIN order_items o
ORDER BY o.id
) s
JOIN order_items t
ON t.pk_col = s.pk_col
SET t.order = s.order
我还没有测试过这个特定的语句,但这是我使用的模式。
一些建议:
避免使用联接操作的逗号语法,改用JOIN
关键字。
确保ORDER BY
适用于正在执行评估和分配给用户定义变量的SELECT
。
在IF
之外对 udv 进行赋值(我希望先计算IF
函数中的表达式,然后再进行赋值。
使用表名或表别名限定所有列引用。
作为样式首选项,我将别名s
分配给源(派生表/内联视图),并将别名t
分配给目标表(我正在更新的表)。
我单独测试内联视图查询,以确保它返回我期望的结果。
如果此模式停止工作,如果我无法开始工作,那么我准备调整为使用中间临时表,以确保视图查询在 UPDATE 之前具体化。
DROP TEMPORARY TABLE IF EXISTS __tt_vq__ ;
CREATE TEMPORARY TABLE __tt_vq__ ( ... ) ;
使用用户定义的变量执行查询并具体化
INSERT INTO __tt_vq__ SELECT s.* FROM ( view query ) s ;
然后验证结果是否符合我的预期(在我进行更新之前完成对用户定义变量的操作)
UPDATE __tt_vq__ s
JOIN target t
ON t.id = s.id
SET t.col = s.val
;
DROP TEMPORARY TABLE __tt_vq__ ;
这种使用用户定义变量的方法特定于 MySQL。其他数据库(如SQL Server和Oracle)提供分析功能,例如ROW_NUMBER() OVER
或DENSE_RANK()
.我们使用用户定义的变量来模拟该功能。
要做到这一点,没有用户定义的变量,也没有分析函数,我们需要一个保证唯一的列(或一组列)。然后我们可以执行半连接操作,并获取"小于"(之前)当前行的行数。 也就是说,我们认为"order"列代表"这个之前的行数",所以第一行前面有 0 行,第二行前面有 1 行,依此类推。
*编辑*
将 SQLFiddle 添加到问题中后,我们现在看到表order_items
有一个item_id
列和一个id
列(并且order
列实际上名为rank
。
SQL 小提琴演示在这里:http://sqlfiddle.com/#!9/9caf62/1
-- #####################################
-- added 2017-11-03 20:18:17 spencer7593
UPDATE (
SELECT @curr := IF(o.item_id <> @prev, 0, @curr + 1) AS rank
, @prev := o.item_id AS item_id
, o.id AS id
FROM ( SELECT @curr := 0, @prev := '') i
CROSS
JOIN order_items o
ORDER BY o.item_id, o.id
) s
JOIN order_items t
ON t.id = s.id
SET t.rank = s.rank