MYSQL-如何根据订单数量更新排序

Question

我有两个表，看起来像这样。

air_line_info

code    sort 
BR      1
CI      2
CX      3

订单

order_id    air_line_code 
170101       BR
170102       CX
170103       CX
170104       BR
170105       CX
170106       CI

我试图只使用sql命令通过计算订单数量来更新air_line_info.sort。

下面是我所期望的

air_line_info

code    sort 
BR      2
CI      3
CX      1

这是我的sql命令

SET @sort = 0;
UPDATE 
`air_line_info` 
SET `sort` = (@sort = @sort+1) 
WHERE 
`air_line_code` = (SELECT `air_line_code` FROM `orders` GROUP BY `air_line_code` ORDER BY COUNT(`order_id`))

但我只收到一些消息，告诉我SQL语法有错误。有人知道出了什么问题吗？如果你有其他解决方法，我也想知道，谢谢。

Answer 1

这可以通过子查询和使用ROW_NUMBER()来确保不会获得重复的sort值：

UPDATE `air_line_info` nfo INNER JOIN (SELECT `air_line_code`, ROW_NUMBER() OVER (ORDER BY COUNT(`order_id`) DESC, `air_line_code`) as `new_sort`
FROM `orders`
GROUP BY `air_line_code`) tmp ON nfo.`code` = tmp.`air_line_code`
SET nfo.`sort` = tmp.`new_sort`
;

子查询将根据每个air_line_code的出现顺序(从最常见到最不常见)和字母位置给它们一个唯一的数字。

Answer 2

如果您使用的是支持ROW_NUMBER()等窗口功能的MySQL v8或MariaDB，那么没有更好的方法了。但是，如果您仅限于较旧的MySQL版本，那么我可以理解您为什么使用这种方法。然而，你现在这样做的方式是无效的，这从错误消息中可以清楚地看出：

错误1242：子查询返回超过1行

为了说明你正在做什么，可以这样做：

行

部分查询
。。其中air_line_code=	BR
	CI
	CX

你可以试试这个：

UPDATE air_line_info ali
JOIN
(
SELECT COUNT(order_id) count_Order,air_line_code
FROM order odr
GROUP BY air_line_code
)odr ON ali.Code=odr.air_line_code
SET ali.Sort=odr.count_Order;