MySQL 重用子查询

Question

我在这里阅读了一些关于子查询重用的线程，但找不到对我的问题的满意答案:假设下表temp(在 MySQL 中):

+------+---+----+
| Y    | M | V  |
+------+---+----+
| 1995 | 0 |  3 |
| 1995 | 1 | 16 |
| 1995 | 4 | 18 |
| 2025 | 0 |  4 |
| 2025 | 2 | 13 |
+------+---+----+

我想生成一个新列 X，它将 M 为 0 的行中的 V 复制到具有相同 Y(耳朵)的所有行中，例如:

+------+---+----+------+
| Y    | M | V  | X    |
+------+---+----+------+
| 1995 | 0 |  3 |    3 |
| 1995 | 1 | 16 |    3 |
| 1995 | 4 | 18 |    3 |
| 2025 | 0 |  4 |    4 |
| 2025 | 2 | 13 |    4 |
+------+---+----+------+

可以这样做:

SELECT Y, M, V,
  (SELECT V FROM temp WHERE temp.Y = t.Y AND M = 0) AS X
FROM temp t;

或者像这样:

SELECT temp.Y, temp.M, temp.V, q2.V AS X
FROM temp
JOIN (SELECT * FROM temp WHERE M = 0) q2 USING (Y);

出于本练习的目的，可以假设 temp 仅包含一个 M = 0 用于每个 Y(耳朵)。

问题是，temp 并不是真正的表，而是复杂查询的结果。用实际查询替换 temp 很笨拙，而且性能可能会很差，除非 MySQL 足够聪明，可以检测到这两个构造是相同的。

如果 MySQL 中没有 WITH 子句，有没有一种方法可以生成 X 而无需引用 temp 两次；即这个问题真的需要子查询吗？

代码最终将在 S-PROC 中运行，因此我可以创建一个临时内存表，但我想知道是否有更优雅的解决方案。

Answer 1

公用表表达式和子查询是最简单的方法。 View 是一种解决方案。另一种是使用变量:

select t.*,
       (@v := if(@y = y, @v,
                 if(@y := y, v, v)
                )
       )
from temp t cross join
     (select @y := 0, @v := -1) params
order by year, (m = 0) desc;

请注意，此公式假设每年至少有一个 m = 0 值——它会为该年的所有行复制每年遇到的第一个值。您在问题描述中指定这是真的。可以使表达式更复杂一些，以涵盖一年没有这样一行的情况(但那将是一个不同的问题)。