我可以在MySQL的多行更新中为选择查询取别名吗

Question

我正在努力让MySQL代码更可读：

UPDATE customers
SET last_order_date = (SELECT MAX(date) FROM orders WHERE customers.customer_id = orders.customer_id),
added_note = (SELECT note FROM orders WHERE customers.customer_id = orders.customer_id 
AND date = (SELECT MAX(date) FROM orders WHERE customers.customer_id = orders.customer_id));

示例代码将每个客户最近订单的日期和备注添加到客户表中的相应行，但它必须两次SELECT任何给定客户的最近订单日期。

我尝试了这里建议的别名语法：

UPDATE customers
SET last_order_date = (SELECT MAX(date) AS `maxdate` FROM orders WHERE customers.customer_id = orders.customer_id),
added_note = (SELECT note FROM orders WHERE customers.customer_id = orders.customer_id 
AND date = `maxdate`);

我也尝试过不使用AS关键字：

UPDATE customers
SET last_order_date = (SELECT MAX(date) maxdate FROM orders WHERE customers.customer_id = orders.customer_id),
added_note = (SELECT note FROM orders WHERE customers.customer_id = orders.customer_id 
AND date = maxdate);

但在这两种情况下，别名都无法识别。

有没有一种方法可以将中间结果分配给一个名称，并在以后引用它？

Answer 1

使用MySQL 8.0，您可以使用CTE:

WITH o AS (
SELECT date, note, customer_id,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY date DESC) AS rownum 
FROM orders 
)
UPDATE customers AS c 
INNER JOIN o USING (customer_id)
SET c.last_order_date = o.date,
c.added_note = o.note
WHERE o.rownum = 1;

对于不支持CTE的旧版本MySQL，以下是我对它的编码方式：

UPDATE customers AS c
INNER JOIN orders AS o1 
ON c.customer_id=o1.customer_id
LEFT OUTER JOIN orders AS o2 
ON o1.customer_id=o2.customer_id AND o1.date < o2.date
SET c.last_order_date = o1.date,
c.added_note = o1.note
WHERE o2.customer_id IS NULL;

当不匹配时，外部联接为联接表的所有列返回NULL。如果没有日期大于行o1的行o2，则o1必须是相应customer_id的日期最大的行。

后一种解决方案可能导致平局。也就是说，对于一个给定的客户，可能会有不止一行的最长日期。CTE解决方案不会有这个问题。