如何在 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中文网其他相关文章！