PostgreSQL SELECT必须匹配多行

发布时间：2020-12-13 15:57:39 所属栏目：百科来源：网络整理

导读：我有一个表,其中包含一对表示时间跨度的时间戳.这些行的范围是用户标识,每个用户可以有一个或多个与之关联的行. 此数据是从抽象的“可用性”表单生成的,该表单表示用户何时在一周内可用.我需要输入一系列时间范围作为查询,并返回表中所有行匹配的所有用户ID.

我有一个表,其中包含一对表示时间跨度的时间戳.这些行的范围是用户标识,每个用户可以有一个或多个与之关联的行.

此数据是从抽象的“可用性”表单生成的,该表单表示用户何时在一周内可用.我需要输入一系列时间范围作为查询,并返回表中所有行匹配的所有用户ID.

鉴于此表：

CREATE TABLE "public"."availability" (
  "id" int4 NOT NULL,"user_id" int4,"starts_at" timestamp(6),"ends_at" timestamp(6),PRIMARY KEY ("id")
) WITH (OIDS=FALSE)

而这个数据：

User #1 is available Mon-Tue between 08:00 and 17:00

+----+---------+---------------------+---------------------+
| id | user_id | starts_at           | ends_at             |
+----+---------+---------------------+---------------------+
| 1  | 1       | 2013-03-18 08:00:00 | 2013-03-18 17:00:00 |
+----+---------+---------------------+---------------------+
| 2  | 1       | 2013-03-19 08:00:00 | 2013-03-19 17:00:00 |
+----+---------+---------------------+---------------------+

User #2 is available Sun-Sat all day

+----+---------+---------------------+---------------------+
| 3  | 2       | 2013-03-17 00:00:00 | 2013-03-23 23:59:59 |
+----+---------+---------------------+---------------------+

User #3 is available Wed between 06:00 and 18:00

+----+---------+---------------------+---------------------+
| 4  | 3       | 2013-03-20 06:00:00 | 2013-03-20 18:00:00 |
+----+---------+---------------------+---------------------+

我可以轻松选择可用于任何给定时间戳的用户：

SELECT * FROM "public"."availability"
  WHERE ('2013-03-19 08:35:00' BETWEEN starts_at AND ends_at 
     AND '2013-03-19 18:25:00' BETWEEN starts_at AND ends_at)
    OR  ('2013-03-20 12:00:00' BETWEEN starts_at AND ends_at
     AND '2013-03-20 18:00:00' BETWEEN starts_at AND ends_at);

+----+---------+---------------------+---------------------+
| id | user_id | starts_at           | ends_at             |
+----+---------+---------------------+---------------------+
| 3  | 2       | 2013-03-17 00:00:00 | 2013-03-23 23:59:59 |
+----+---------+---------------------+---------------------+
| 4  | 3       | 2013-03-20 06:00:00 | 2013-03-20 18:00:00 |
+----+---------+---------------------+---------------------+

但我真正需要的是能够查询多个时间盘并仅返回匹配所有条件的user_ids.

查询：2013-03-17 10：00：00-2013-03-17 16：00：00,2013-03-23 10：00：00-2013-03-23 16:00:00应该返回：

+----+---------+---------------------+---------------------+
| id | user_id | starts_at           | ends_at             |
+----+---------+---------------------+---------------------+
| 3  | 2       | 2013-03-17 00:00:00 | 2013-03-23 23:59:59 |
+----+---------+---------------------+---------------------+

查询：2013-03-18 09：00：00-2013-03-18 16：00：00,2013-03-19 08：00：00-2013-03-19 15:45:00应该返回：

+----+---------+---------------------+---------------------+
| id | user_id | starts_at           | ends_at             |
+----+---------+---------------------+---------------------+
| 1  | 1       | 2013-03-18 08:00:00 | 2013-03-18 17:00:00 |
+----+---------+---------------------+---------------------+
| 2  | 1       | 2013-03-19 08:00:00 | 2013-03-19 17:00:00 |
+----+---------+---------------------+---------------------+
| 3  | 2       | 2013-03-17 00:00:00 | 2013-03-23 23:59:59 |
+----+---------+---------------------+---------------------+

查询：2013-03-18 07：00：00-2013-03-18 18:00:00应该什么都不返回.

SQLFiddle example

解决方法

SQL Fiddle

这会将boolean强制转换为整数0或1.

select a.*
from
    availability a
    inner join
    (
        select
            user_id,sum (
                ('2013-03-18 09:00:00' between starts_at and ends_at
                 and
                 '2013-03-18 16:00:00' between starts_at and ends_at
                )::integer
                +
                ('2013-03-19 08:00:00' between starts_at and ends_at
                 and
                 '2013-03-19 15:45:00' between starts_at and ends_at
                )::integer
            ) period
        from availability
        group by user_id
    ) s on a.user_id = s.user_id
where period >= 2

将where条件更改为要匹配的句点数.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!