如何在 PostgreSQL 查詢中正確找到重疊的日期範圍？

在PostgreSQL 中尋找重疊的日期範圍

問題：

您在以下位置尋找玩家的查詢特定年份的團隊似乎不正確。更正查詢並提供缺失的詳細資訊。

不正確的查詢：

SELECT *
FROM contract
JOIN team USING (name_team)
JOIN player USING(name_player)
WHERE name_team = ?
AND DATE_PART('YEAR',date_join) >= ?
AND DATE_PART('YEAR',date_leave) <= ?

原因：

查詢不找不到重疊的日期範圍，沒有查詢因為BETWEEN 運算子使用不正確。為了正確比較，應排除上限。

正確答案：

基本查詢：

SELECT p.*
FROM team AS t
JOIN contract AS c USING (name_team)
JOIN player AS p USING (name_player)
WHERE t.name_team = ?
AND c.date_join < date '2010-01-01'
AND c.date_leave >= date '2009-01-01';

使用 Distinct 和 NULL進行精細查詢處理：

SELECT DISTINCT p.*
FROM contract AS c
JOIN player AS p USING (name_player)
WHERE c.name_team = ?
AND c.date_join < date '2010-01-01'
AND (c.date_leave >= date '2009-01-01' OR c.date_leave IS NULL);

使用OVERLAPS 運算符：

SELECT DISTINCT name_player
FROM contract
WHERE name_team = ?
AND (date_join, COALESCE(date_leave, CURRENT_DATE)) OVERLAPS
(date '2009-01-01', date '2010-01-01');

使用範圍類型和索引支持：

SELECT DISTINCT name_player
FROM contract
WHERE name_team = ?
AND daterange(date_join, date_leave) &&&
daterange '[2009-01-01,2010-01-01)';

記得替換「？」佔位符，其中包含查詢中所需的值。

以上是如何在 PostgreSQL 查詢中正確找到重疊的日期範圍？的詳細內容。更多資訊請關注PHP中文網其他相關文章！