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