mysql - 在将它用于 HAVING 子句时如何从 SELECT 语句中省略一个列？

Question

这是我的查询:

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？如您所见，我需要在 HAVING 子句中使用 indicator_id。所以我不能省略整个子查询。只是我需要将它移到 SELECT 语句以外的地方。语法是什么？

Answer 1

您可以将子查询移动到having 子句。正如戈登回答的那样，您使用 having 子句作为第二个 where，只有 MySQL 支持。最好将第二个条件添加到 where with 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
        )