mysql - SQL 简化

Question

我有这个 SQL 语句:

SELECT
       (CASE
            WHEN EXISTS
                   (SELECT *
                    FROM votes
                    WHERE votes.user_id = 0
                      AND votes.post_id = posts.id
                      AND votes.vote = 0) THEN 0
            WHEN EXISTS
                   (SELECT *
                    FROM votes
                    WHERE votes.user_id = 0
                      AND votes.post_id = posts.id
                      AND votes.vote = 1) THEN 1
            ELSE 2
        END) AS vote_by_me ,
       posts.*
FROM `posts`

有没有办法以 DRY 方式执行此操作？两个 select 语句几乎相同，最好以某种方式将它们分解出来。

谢谢

Answer 1

是的，你可以直接选择votes.vote，像这样:

SELECT
    COALESCE(
        (
        SELECT MIN(votes.vote)
        FROM votes
        WHERE votes.user_id = 0 AND votes.post_id = posts.id
        AND votes.vote in (0, 1)
        GROUP BY votes.user_id, votes.post_id
        )
    ,   2
    ) AS vote_by_me
,   posts.*
FROM `posts

如果一个帖子不能由同一用户多次投票，您可以消除 GROUP BY，如下所示:

SELECT
    COALESCE(
        (
        SELECT votes.vote
        FROM votes
        WHERE votes.user_id = 0 AND votes.post_id = posts.id AND votes.vote in (0, 1)
        )
    ,   2
    ) AS vote_by_me
,   posts.*
FROM `posts