主页 / dba / 问题 / 278822
Accepted
Mio
Asked: 2020-10-29 10:36:25 +0800 CST

如何过滤具有两个窗口条件的数据？

  • 772

我想得到两个子查询的结果。在我的情况下，我有用户，用户可以对门进行多个访问，但有时门被禁用，因此访问存在但不指向任何门。我想为同一个用户找到多个访问权限，该用户至少有一个没有门的访问权限和至少一个有一个门组的访问权限。

这是SQL，可以工作。但这非常复杂。我该如何改进它？

DROP TABLE IF EXISTS users CASCADE;
DROP TABLE IF EXISTS doors CASCADE;
DROP TABLE IF EXISTS access CASCADE;
CREATE TABLE users(id SERIAL PRIMARY KEY);
CREATE TABLE doors(id SERIAL PRIMARY KEY, type TEXT NOT NULL);
CREATE TABLE access(
  id SERIAL PRIMARY KEY,
  user_id INT NOT NULL, FOREIGN KEY (user_id) REFERENCES users (id),
  door_id INT, FOREIGN KEY (door_id) REFERENCES doors (id)
);
INSERT INTO users VALUES (1), (2), (3), (4);
INSERT INTO doors(type) VALUES ('front'), ('back');
INSERT INTO access(user_id, door_id)
VALUES
  (1, 1), -- user have only access to door
  (2, null), -- user with empty door access
  (3, 1), (3, null), -- user with access and empty access
  (4, 1),(4, 2) -- user with access to front and back access
  ;

--- We want all the door access for the same user with at least one empty access and one door access

WITH users_with_multiple_access AS (
  SELECT access.user_id 
  FROM access
  GROUP BY access.user_id
  HAVING (COUNT(access.user_id) > 1)
),
users_with_multiple_access_and_without AS (
  SELECT access.*, CASE WHEN door_id IS NULL THEN 1 END AS marked 
  FROM access
  JOIN users_with_multiple_access USING(user_id)
),
users_with_multiple_access_marked AS (
  SELECT access.id AS access_id, sum(marked) OVER (PARTITION BY access.user_id) AS marked
  FROM access
  LEFT JOIN users_with_multiple_access_and_without uwmaw ON uwmaw.id = access.id
)
SELECT access.*
FROM access
JOIN users_with_multiple_access_marked awmam ON awmam.access_id = access.id AND marked IS NOT NULL;

小提琴

postgresql subquery

2 个回答

  1. 另一种方法是：

    select id, door_id, type
from (
    select u.id, a.door_id, d.type
         , count(case when a.door_id is null then 1 end) 
               over (partition by u.id) as x1
         , count(case when a.door_id is not null then 1 end) 
               over (partition by u.id) as x2
    from
    users u
    left join access a
        on a.user_id = u.id
    left join doors d
        on d.id = a.door_id
) as t
where x1 > 0 
  and x2 > 0;

    我劫持了@McNets Fiddle并将此查询添加到其中

    • 2
  2. Best Answer

    您可以在 WHERE 子句中使用 EXIST：

    添加这两个条件，必须至少存在一个有门的通道，并且必须存在一个没有门的通道。

    select
    u.id, a.door_id, d.type
from
    users u
left join
    access a
    on a.user_id = u.id
left join
    doors d
    on d.id = a.door_id
where exists (select 1
              from   access
              where  access.user_id = u.id
                     and access.door_id is null)
       and exists (select 1
                   from   access
                   where  access.user_id = u.id
                          and access.door_id is not null)

    编号 | 门号 | 类型
-: | ------: | :----
 3 | 1 | 正面
 3 |    空| 无效的

    db<>在这里摆弄

    • 1

相关问题