在HAVING子句中使用SELECT语句时，如何省略该列？

admin

在HAVING子句中使用SELECT语句时，如何省略该列？

sql

这是我的查询：

SELECT e.id, (SELECT MIN(u.id) id
        FROM (SELECT MIN(id) id
            FROM events
            WHERE author_id = 32
            GROUP BY type, post_id, table_code, comment_id, context
            ORDER BY MIN(id) desc 
            LIMIT 15) as u
        ) as indicator_id
FROM events e
WHERE author_id = 32
HAVING e.id >= indicator_id
ORDER BY id DESC

它也可以正常工作，并返回两列：id和indicator_id。

我只需要得到id。我该如何省略indicator_id？如您所见，我需要使用indicator_idintoHAVING子句。所以我不能省略整个子查询。我只需要将其移动到SELECT声明以外的其他位置。语法是什么？

阅读 303

2021-06-07

共1个答案

admin

您可以将子查询移到该having子句。正如戈登回答的那样，您将having第二个子句用作第二个子句where，只有MySQL支持。最好在wherewith中添加第二个条件and：

SELECT e.id
FROM events e
WHERE author_id = 32
    AND e.id >= (SELECT MIN(u.id) id
        FROM (SELECT MIN(id) id
            FROM events
            WHERE author_id = 32
            GROUP BY type, post_id, table_code, comment_id, context
            ORDER BY MIN(id) desc 
            LIMIT 15) as u
        ) 
ORDER BY id DESC

根据您的评论，这会更简单一些。它选择事件ID最高的15个帖子：

SELECT  id
FROM    events
WHERE   author_id = 32
        AND post_id IN
        (
        SELECT  DISTINCT post_id
        FROM    events
        ORDER BY
                id DESC
        LIMIT   15
        )

2021-06-07