我有一個表來存盤這樣的事件：
| id | title | start_date | end_date |
| 1 | event one | 2022-05-11 09:00:00.000 | 2022-08-21 09:00:00.000 |
| 2 | event two | 2022-06-22 15:00:00.000 | 2022-09-23 15:00:00.000 |
| 3 | event three | 2022-07-12 13:00:00.000 | 2022-08-12 13:00:00.000 |
| 4 | event four | 2022-07-12 13:00:00.000 | 2022-08-12 13:00:00.000 |
和另一個表來存盤這樣的帖子：
| id | title | created_at |
| 1 | post one | 2022-07-03 19:38:00.000 |
| 2 | post two | 2022-08-29 07:12:00.000 |
| 3 | post three | 2022-10-05 17:35:00.000 |
| 3 | post four | 2022-10-07 20:05:00.000 |
如果我想統計在單個事件中發生的帖子，我可以寫：
WITH
event AS (
SELECT
start_date
, end_date
FROM
events
WHERE
id = 1
)
SELECT
COUNT(*)
FROM
posts
WHERE
create_at >= (SELECT start_date FROM event)
AND create_at < (SELECT end_date FROM event)
但是，當目標事件僅在運行時已知時，如何獲取多個事件期間發生的帖子數？
編輯：
資料庫是 PostgresSQL 13
uj5u.com熱心網友回復：
連接加聚合方法應該在這里作業：
SELECT p.id, p.title
FROM posts p
INNER JOIN events e
ON p.created_at BETWEEN e.start_date AND e.end_date
GROUP BY p.id, p.title
HAVING COUNT(*) > 1;
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/516234.html