我对PostgreSQL还是不太熟悉,但我知道你不能在WHERE子句中使用聚合函数。
我有如下所示的数据:
dID | status | created_at
---------------------------
2 | 2 | 2014-01-01
2 | 2 | 2014-02-01
3 | 2 | 2013-12-22
我本质上是在尝试构建一个查询,该查询将根据最小created_at日期从数据集返回所有结果。以下查询不起作用,但它确实突出显示了我正在尝试完成的任务:
select
*
from table1 a
JOIN table2 b ON (a.id = b.id)
where a.did = 2
AND b.status = 2
AND MIN(b.created_at) < '2014-01-31'
上面的示例表是从非常大的数据集聚合而来的,但我基本上想找到一个具有特定状态的 dID 列表,其中created_at日期是在某个时间段定义的。有什么建议吗?
也许是:
postgres=# SELECT DISTINCT ON (id, status) *
FROM table1
WHERE id = 2 and status = 2 and created_at < '2014-01-31'
ORDER BY id, status, created_at;
id | status | created_at
----+--------+------------
2 | 2 | 2014-01-01
(1 row)
我认为您只需要HAVING
子句,尽管很难从您的问题中确定:
select did
from t
group by did
having min(created_at) < '2014-01-31';
如果要通过 DID 从另一个表中获取数据,可以使用 EXISTS
子查询,尽管可能存在性能问题:
SELECT *
FROM t1
WHERE EXISTS (
SELECT 1
FROM t2
WHERE t1.did = t2.did
GROUP BY t2.did
HAVING min(t2.created_at) < '2014-01-31'
);
。或者,您可以加入返回一组合格 DID 的查询。