sql - 多对一关系，只选择祖先满足所有条件的行

Question

首先我将向您展示表的架构。

 Table "public.questionare"
      Column        |          Type         |                           
--------------------+-----------------------+
 id                 | integer               | 




Table "public.questionareacceptance"
         Column          |          Type         |                         
-------------------------+-----------------------+
 id                      | integer               | 
 questionare_id          | integer               |
 accept_id               | integer               |
 impact_id               | integer               |

表 questionareacceptance 包含:

id  | questionare_id | accept_id|       impact_id  |
----+----------------+----------+------------------+
1   |1               |1         |                  |
2   |1               |1         | 1                |
3   |1               |1         | 1                |
4   |2               |          | 1                |
5   |3               |1         | 1                |
6   |4               |1         | 1                |
7   |4               |1         | 1                |

我想要得到的是一个 questionare ID 列表，其中每个 questionareacceptance 字段 accept_id 和 impact_id 不是 NULL

我的查询如下:

SELECT q.id AS quest,
    qa.id AS accepted
FROM questionare q,
    questionareacceptance qa
WHERE q.id = qa.questionare_id
    AND qa.accept_id IS NOT NULL
    AND qa.impact_id IS NOT NULL;

但结果是休闲的:

      quest         |          accepted     |                           
--------------------+-----------------------+
 1                  |1                      | 
 1                  |2                      | 
 1                  |3                      | 
 2                  |4                      | 
 3                  |5                      | 
 4                  |6                      | 
 4                  |7                      | 

但应该返回的结果只有3和4，其他的impact_id或accept_id为null。

谁能指出我哪里做错了？

Answer 1

您的查询可以写成不存在:

select
    q.id as quest, qa.id as accepted
from questionare as q
    inner join questionareacceptance as qa on qa.questionare_id = q.id
where
    not exists (
        select *
        from questionareacceptance as tqa
        where
           tqa.questionare_id = q.id and
           (tqa.accept_id is null or tqa.impact_id is null)
    )

但我认为使用窗口函数会更快:

with cte as (
    select
        q.id as quest, qa.id as accepted,
        sum(case when qa.accept_id is not null and qa.impact_id is not null then 1 else 0 end) over(partition by q.id) as cnt1,
        count(*) over(partition by q.id) as cnt2
    from questionare as q
        inner join questionareacceptance as qa on qa.questionare_id = q.id
)
select quest, accepted
from cte
where cnt1 = cnt2

实际上看起来你根本不需要加入:

with cte as (
    select
        qa.questionare_id as quest, qa.id as accepted,
        sum(case when qa.accept_id is not null and qa.impact_id is not null then 1 else 0 end) over(partition by qa.questionare_id) as cnt1,
        count(*) over(partition by qa.questionare_id) as cnt2  
    from questionareacceptance as qa
)
select quest, accepted
from cte
where cnt1 = cnt2;

sql fiddle demo