Mysql 更新列与子请求 Order By

Question

我有一个包含这 4 列的表 biblek2 items :

id (autoincrement)  
catid(int)  
introtext(varchar)  
ordering(int)

表 biblek2_items

╔════╦═══════╦═══════════╦══════════╗
║ ID ║ catid ║ introtext ║ ordering ║
╠════╬═══════╬═══════════╬══════════╣
║  1 ║  3024 ║   orange  ║  122     ║
║  2 ║  2024 ║   zebra   ║  45      ║
║  3 ║  3010 ║   juice   ║  55      ║
║  4 ║  3002 ║   build   ║  17      ║
║  5 ║  2003 ║   car     ║  87      ║
║  6 ║  1610 ║   other   ║  1521    ║
║  7 ║  1620 ║   other   ║  200     ║
╚════╩═══════╩═══════════╩══════════╝

我希望如此

表 biblek2_items

╔════╦═══════╦═══════════╦══════════╗    
║ ID ║ catid ║ introtext ║ ordering ║
╠════╬═══════╬═══════════╬══════════╣
║  5 ║  2003 ║   car     ║  1       ║
║  4 ║  3002 ║   build   ║  2       ║
║  3 ║  3010 ║   juice   ║  3       ║
║  1 ║  3024 ║   orange  ║  4       ║
║  2 ║  2024 ║   zebra   ║  5       ║
╚════╩═══════╩═══════════╩══════════╝

我想要

1. 从 biblek2_items 中选择 *，其中 catid 介于 2001 到 3024 之间
2. 按 introtext ASC 排序
3. 清空排序列
4. 根据 order 列的结果以从 1 到 n 的增量对 order 列重新排序

我尝试过但没有成功

DECLARE @variable int    
SET @variable = 0    
UPDATE `biblek2_items`    
SET @variable = ordering = @variable + 1     
WHERE ordering IN (SELECT ordering     
                 FROM `biblek2_items`    
                 WHERE catid BETWEEN 2001 AND 3024    
                 ORDER BY `introtext` DESC)

我在论坛上看到MySQL不允许使用ORDER BY的子请求，所以你能帮我吗

Answer 1

正如评论中所解释的:无论如何，子查询中的 ORDER BY 毫无意义，因为您没有LIMIT。因此，所有行都将被返回，并且它们的排序方式并不重要，因为主查询中的 IN 会考虑所有行。

但是您的查询还存在其他问题。

这样做:

SET @row_number = 0 ;

UPDATE biblek2_items, 
    (select id, catid,introtext,ordering,  (@row_number:=@row_number + 1)  AS newordering 
    from biblek2_items 
    where catid between 2001 and 3024
    ORDER BY introtext ASC          
    ) as temp
SET biblek2_items.ordering =  temp.newordering
WHERE biblek2_items.ID = temp.ID

<小时/>

此外，如果您有一个大表，并且有很多用户在上面积极写入，为了避免不一致或锁定问题，我建议采用稍微不同的方法，使用临时表来存储计算出的新排序。

CREATE TABLE biblek2_items_TEMP (ID INT, ordering INT);

SET @row_number = 0 ;

INSERT INTO biblek2_items_TEMP 
select id,  (@row_number:=@row_number + 1)  AS newordering 
from biblek2_items 
where catid between 2001 and 3024
ORDER BY introtext ASC  
;

UPDATE biblek2_items, biblek2_items_TEMP
SET biblek2_items.ordering =  biblek2_items_TEMP.ordering
WHERE biblek2_items.ID = biblek2_items_TEMP.ID;

DROP TABLE biblek2_items_TEMP;

在 MySQL 5.7 和 MariaDB 10 上测试成功