mysql - SELECT 子句中的 COALESCE 函数

Question

问题:是根据id顺序从表中找到最后一个非NULL值，在这里找到http://sqlmag.com/t-sql/last-non-null-puzzle

CREATE TABLE Score(`id` int, `col1` INT NULL);
INSERT INTO Score
    (`id`, `col1`)
VALUES
    (2, NULL),
    (3, 10),
    (5, -1),
    (7, NULL),
    (11, NULL),
    (13, -12),
    (17, NULL),
    (19, NULL),
    (23, 1759);

期望的输出:

id          col1        lastval
----------- ----------- -----------
2           NULL        NULL
3           10          10
5           -1          -1
7           NULL        -1
11          NULL        -1
13          -12         -12
17          NULL        -12
19          NULL        -12
23          1759        1759

我在 SQLFiddle MySQL 5.6 中的代码

SELECT s1.id, COALESCE(s2.col1)
FROM score s1 JOIN score s2
ON s1.id >= s2.id
GROUP BY s1.id
ORDER BY s1.id, s2.id DESC

据我所知，COALESCE 给出了列表中遇到的第一个非 NULL 值。但它为所有列提供 (null) 。你能指出为什么 COALESCE 不起作用吗？我还意识到删除 COALESCE 也应该回答这个问题，因为 GROUP BY 子句返回 s2.col1 列中的第一个值。我在这里可能是错的。

Answer 1

COALESCE() 返回其参数列表中的第一个非空值。没有将 COALESCE() 应用于分组行集上的相同表达式的隐式行为。它不是像 SUM() 或 GROUP_CONCAT() 这样的聚合函数。

将它与一个参数一起使用是没有意义的，因为您只给了它一个参数。因此，这与将 s2.col1 放入查询的选择列表中而不将其放入 COALESCE() 调用中完全相同。

然后，当在 GROUP BY 函数中引用非分组列时，MySQL 将保留默认行为:MySQL 从组中的某行中任意选择 s2.col1 的值。实际上，这是按索引顺序读取的组中的第一行，但您不应依赖于此。

因此，在您的示例中，您的连接将每一行 s1 连接到所有行 s2，以便 s2.id 更早。每个组都包含表中的第一行，其中 s2.id=2。该行的 col1 为 NULL。

我通读了您试图解决的 SQL 难题。在 MySQL 中会特别尴尬，因为 MySQL 不支持窗口函数。

这些情况下的解决方案通常涉及使用 MySQL User-Defined Variables当您的查询遍历表中的行时，该值会发生变化。这可能非常棘手。

---

以下是我如何以可移植的方式解决问题(没有 MySQL 用户变量):

SELECT s1.id, s1.col1, s2.col1 as lastval
FROM Score AS s1
LEFT OUTER JOIN Score AS s2 ON s2.id <= s1.id AND s2.col1 IS NOT NULL
LEFT OUTER JOIN Score AS s3 ON s3.id > s2.id AND s3.id <= s1.id AND s3.col1 IS NOT NULL
WHERE s3.id IS NULL
ORDER BY s1.id;

下面是如何使用 MySQL 用户变量解决它:

SELECT id, col1, @lastval := COALESCE(col1, @lastval) AS lastval
FROM (SELECT @lastval := NULL) AS _init 
CROSS JOIN Score
ORDER BY id;

我会避免使用您在下方评论中发布的查询，因为它使用了相关子查询。相关子查询通常不利于性能。