MySQL:使用 COALESCE 创建多个变量

Question

通过使用 COALESCE，我可以创建一个名为 comment_votes 的临时变量，如下所示:

SELECT comments.*, COALESCE(rs_reputations.value, 0) AS comment_votes FROM `comments` 
LEFT JOIN rs_reputations ON comments.id = rs_reputations.target_id AND 
rs_reputations.target_type = 'Comment' AND rs_reputations.reputation_name = 
'comment_votes' AND rs_reputations.active = 1 WHERE (impression_id = 1)

我想在到来的查询中创建一个名为 impression_votes 的第二个变量。我试图这样做:

SELECT comments.*, COALESCE(rs_reputations.value, 0) AS comment_votes 
FROM 'comments' 
    LEFT JOIN rs_reputations ON 
        comments.id = rs_reputations.target_id AND 
        rs_reputations.target_type = 'Comment' AND 
        rs_reputations.reputation_name = 'comment_votes' AND 
        rs_reputations.active = 1 
SELECT comments.*, COALESCE(rs_reputations.value, 0) AS impression_votes 
FROM 'comments' 
    LEFT JOIN rs_reputations ON 
    comments.id = rs_reputations.target_id AND
    rs_reputations.target_type = 'Comment' AND
    rs_reputations.reputation_name = 'impression_votes' AND 
    rs_reputations.active = 1 
WHERE

这会导致错误:

You have an error in your SQL syntax

我正在尝试的是可能的吗？如果是这样，我似乎不正确地桥接了两个 SELECT/COALESCE 语句。这个应该怎么写？

Answer 1

MySQL COALESCE函数实际上是一个返回第一个非空值的内置函数 - 它不是一个变量，它是一个实际上在各种数据库系统中都受支持的函数。

例如，下表:

| Id    | Name      | Counter    |
| 1     | lolcat    | NULL       |
| 2     | codez     | 1          |

sql语句:

SELECT Id, Name, COALESCE(counter, 0) AS NonNullCounter FROM table

将返回结果:

| Id    | Name      | NonNullCounter |
| 1     | lolcat    | 0              |
| 2     | codez     | 1              |

在这种情况下，NULL 值已被 0 替换。

这对您很有用，因为如果您在 rs_reputations 中还没有任何与评论中的行匹配的行，LEFT JOIN 将返回 NULL rs_repuations.value 列，然后由 COALESCE 替换为 0。

如果您是 JOIN 的新手，那么有一个很棒的 visual guide by Jeff Atwood .

您的第一个查询实际上是:

SELECT     comments.*, 
           COALESCE(rs_reputations.value, 0) AS comment_votes 
FROM       comments 
LEFT JOIN   rs_reputations ON comments.id = rs_reputations.target_id 
                           AND rs_reputations.reputation_name = 'comment_votes' 
WHERE       impression_id = 1;

选择 1 - 联合

您有几个选择 - 您可以选择 UNION你的结果像这样:

SELECT     comments.*, 
           COALESCE(rs_reputations.value, 0) AS votes,
           'comment_votes' AS vote_type 
FROM       comments 
LEFT JOIN   rs_reputations ON comments.id = rs_reputations.target_id 
                           AND rs_reputations.reputation_name = 'comment_votes' 
WHERE       impression_id = 1

UNION

SELECT     comments.*, 
           COALESCE(rs_reputations.value, 0) AS votes,
           'impression_votes' as vote_type 
FROM       comments 
LEFT JOIN   rs_reputations ON comments.id = rs_reputations.target_id 
                           AND rs_reputations.reputation_name = 'impression_votes' 
WHERE       impression_id = 1;

在这种情况下，您的结果将如下所示:

|comments_columns|votes|vote_type       |
| *              |12   |comment_vote    |
| *              |2    |impression_vote |  

选择 2 - 在同一张 table 上加入两次

或者你可以通过 using the same table name but a different alias 自己加入同一张表两次:

SELECT     comments.*, 
           COALESCE(CommentRep.value, 0) AS comment_votes,
           COALESCE(ImpressionRep.value, 0) AS impression_votes,
FROM       comments 
LEFT JOIN   rs_reputations AS CommentRep ON comments.id = CommentRep.target_id 
                           AND CommentRep.reputation_name = 'comment_votes' 
LEFT JOIN   rs_reputations AS ImpressionRep ON comments.id = ImpressionRep.target_id 
                           AND ImpressionRep.reputation_name = 'impression_votes'
WHERE       CommentRep.impression_id = 1
AND         ImpressionRep.impression_id = 1

在这种情况下，您的结果将如下所示:

|comments_columns|comment_votes|impression_votes|
| *              |12           |0               |
| *              |2            |6               |  

最后(呸)您在原始 SQL 中出现错误的原因是您将两个 SELECT 语句链接在一起而没有实际关联它们 - SQL 在这种情况下没有真正意义，因为您需要在逻辑上关联它们(通过 UNION 或如上所述的重复连接。