GROUP BY后应用WHERE [英] Apply WHERE after GROUP BY

问题描述

我有一个包含属于线程一部分的项目的表，因此它们有一个线程ID，每个项目也有一个日期（UNIX时间戳）。所以我的表看起来像（简化的UNIX时间戳）：

I have a table with items that are part of a thread, so they have a thread ID, each item also has a date (UNIX timestamp). So my table looks something like (UNIX timestamps simplified):

+-----------------------------+
|  id |   date  |  thread_id  |
+-----+---------+-------------+
|  1  |   1111  |      4      |
|  2  |   1333  |      4      |
|  3  |   1444  |      5      |
|  4  |   1666  |      5      |
+-----------------------------+

我想要做的是选择共享相同线程ID的所有项目都小于传递日期的线程ID。因此，如果我想在所有项目大于1555（日期< 1555）的线程ID中使用线程ID，那么我只希望线程ID 4返回，而不是5，即使它有一个日期小于1555的项目。所以这就是我试过：

What I want to do is select thread IDs where ALL the items sharing the same thread ID are smaller than the passed date. So if I wanted thread IDs where ALL items are older than 1555 (date < 1555), I would only expect to have thread ID 4 returned, not 5 even though it's got an item with a date smaller than 1555. So this is what I tried:

SELECT * FROM table WHERE date < 1555 GROUP BY thread_id ORDER BY date DESC

我试图用这个查询做的事情是group all按日期值最高的项目按线程ID查找项目，然后从中获取日期小于1555的项目。但是，这不起作用，它仍然会返回线程ID 5，因为它具有早于1555的项目。

What I'm trying to do with that query is group all items by thread ID around the item with the highest date value and from there get the items where the date is less than 1555. But that doesn't work, it will still return thread ID 5 because it's got an item older than 1555.

所以总结一下，如何选择所有项目比特定日期早的线程ID？

So to summarize, how would I only select thread IDs where ALL items are older than a certain date?

推荐答案

SELECT thread_id FROM table GROUP BY thread_id HAVING MAX(date) < 1555

这篇关于GROUP BY后应用WHERE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！