在HAVING子句中使用SELECT语句时，如何省略该列? [英] How can I omit a column from SELECT statement while using it into HAVING clause?

问题描述

这是我的查询:

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.

It works as well and returns two columns: id and indicator_id.

我只需要获取id.如何省略indicator_id?如您所见，我需要在HAVING子句中使用indicator_id.所以我不能省略整个子查询.只是我需要将其移动到SELECT语句以外的其他位置.语法是什么?

I need to just get the id. How can I omit indicator_id? As you can see, I need to use indicator_id into HAVING clause. So I cannot omit the whole subquery. Just I need to move it somewhere else than SELECT statement. What's the syntax?

推荐答案

您可以将子查询移至having子句.正如戈登回答的那样，您将having子句用作第二个where，只有MySQL支持.最好使用and将第二个条件添加到where:

You can move the subquery to the having clause. As Gordon answered, you're using the having clause as a second where, which only MySQL supports. It's better to add the second condition to the 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个帖子:

Based on your comment, this would be a bit simpler. It selects the 15 posts with the highest event id:

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

这篇关于在HAVING子句中使用SELECT语句时，如何省略该列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！