mysql - SQL简化

我有一个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`

有没有办法让我干巴巴地做这件事？这两个select语句几乎都是一样的，最好用某种方式将它们排除在外。
谢谢

最佳答案

是的，您可以直接选择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

关于mysql - SQL简化，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/20648559/