A는 관계를 통해-많은이에 SQL 결과를 필터링하는 방법

나는 테이블 학생, 클럽, student_club을 가지고 가정 :

student {
    id
    name
}
club {
    id
    name
}
student_club {
    student_id
    club_id
}

나는 축구 (30), 야구 (50) 클럽 모두에서 모든 학생을 찾는 방법을 알고 싶어요. 이 쿼리가 작동하지 않지만, 내가 지금까지 가지고있는 가장 가까운 것입니다 :

SELECT student.*
FROM   student
INNER  JOIN student_club sc ON student.id = sc.student_id
LEFT   JOIN club c ON c.id = sc.club_id
WHERE  c.id = 30 AND c.id = 50

해결법

==============================

1.나는 궁금했어. 우리 모두가 알다시피, 호기심이 고양이를 죽이는에 대한 명성을 가지고있다.

나는 궁금했어. 우리 모두가 알다시피, 호기심이 고양이를 죽이는에 대한 명성을 가지고있다.

이 테스트에 대한 정확한 고양이 스키닝 환경 :

에서 총 런타임 분석에 대해 설명합니다.

SELECT s.stud_id, s.name
FROM   student s
JOIN   student_club sc USING (stud_id)
WHERE  sc.club_id IN (30, 50)
GROUP  BY 1,2
HAVING COUNT(*) > 1;

SELECT s.stud_id, s.name
FROM   student s
JOIN   (
   SELECT stud_id
   FROM   student_club
   WHERE  club_id IN (30, 50)
   GROUP  BY 1
   HAVING COUNT(*) > 1
   ) sc USING (stud_id);

SELECT s.stud_id, s.name
   FROM   student s
   WHERE  student_id IN (
   SELECT student_id
   FROM   student_club
   WHERE  club_id = 30
   INTERSECT
   SELECT stud_id
   FROM   student_club
   WHERE  club_id = 50);

SELECT s.stud_id,  s.name
FROM   student s
WHERE  s.stud_id IN (SELECT stud_id FROM student_club WHERE club_id = 30)
AND    s.stud_id IN (SELECT stud_id FROM student_club WHERE club_id = 50);

SELECT s.stud_id,  s.name
FROM   student s
WHERE  EXISTS (SELECT * FROM student_club
               WHERE  stud_id = s.stud_id AND club_id = 30)
AND    EXISTS (SELECT * FROM student_club
               WHERE  stud_id = s.stud_id AND club_id = 50);

SELECT s.stud_id, s.name
FROM   student s
JOIN   student_club x ON s.stud_id = x.stud_id
JOIN   student_club y ON s.stud_id = y.stud_id
WHERE  x.club_id = 30
AND    y.club_id = 50;

마지막 세 거의 같은 수행합니다. 4), 5) 동일한 쿼리 계획을 초래할.

팬시 SQL,하지만 성능은 유지할 수 없습니다.

SELECT s.stud_id,  s.name
FROM   student AS s
WHERE  NOT EXISTS (
   SELECT *
   FROM   club AS c 
   WHERE  c.club_id IN (30, 50)
   AND    NOT EXISTS (
      SELECT *
      FROM   student_club AS sc 
      WHERE  sc.stud_id = s.stud_id
      AND    sc.club_id = c.club_id  
      )
   );

SELECT s.stud_id,  s.name
FROM   student AS s
WHERE  NOT EXISTS (
   SELECT *
   FROM  (
      SELECT 30 AS club_id  
      UNION  ALL
      SELECT 50
      ) AS c
   WHERE NOT EXISTS (
      SELECT *
      FROM   student_club AS sc 
      WHERE  sc.stud_id = s.stud_id
      AND    sc.club_id = c.club_id  
      )
   );

예상대로 두 거의 같은 수행합니다. 테이블 스캔의 쿼리 계획의 결과는 플래너는 여기에 인덱스를 사용하는 방법을 찾을 수 없습니다.

WITH RECURSIVE two AS (
   SELECT 1::int AS level
        , stud_id
   FROM   student_club sc1
   WHERE  sc1.club_id = 30
   UNION
   SELECT two.level + 1 AS level
        , sc2.stud_id
   FROM   student_club sc2
   JOIN   two USING (stud_id)
   WHERE  sc2.club_id = 50
   AND    two.level = 1
   )
SELECT s.stud_id, s.student
FROM   student s
JOIN   two USING (studid)
WHERE  two.level > 1;

팬시 SQL, CTE를위한 괜찮은 성능을 제공합니다. 아주 이국적인 쿼리 계획. 다시 말하지만, 어떻게 9.1 핸들이 흥미로운 일이 될 것이다. 나는 곧 9.1로 여기에 사용되는 DB 클러스터를 업그레이드 할 예정이다. 어쩌면 내가 전체 오두막을 다시 실행거야 ...

WITH sc AS (
   SELECT stud_id
   FROM   student_club
   WHERE  club_id IN (30,50)
   GROUP  BY stud_id
   HAVING COUNT(*) > 1
   )
SELECT s.*
FROM   student s
JOIN   sc USING (stud_id);

CTE 쿼리 2의 변형). 놀랍게도, 동일한 데이터와 약간 다른 쿼리 계획을 초래할 수 있습니다. 나는 부질-변형이 인덱스를 사용하는 학생에 순차 검색을 발견했다.

또 다른 최신 추가 @ypercube. 얼마나 많은 방법으로, 긍정적으로 놀랍습니다.

SELECT s.stud_id, s.student
FROM   student s
JOIN   student_club sc USING (stud_id)
WHERE  sc.club_id = 10                 -- member in 1st club ...
AND    NOT EXISTS (
   SELECT *
   FROM  (SELECT 14 AS club_id) AS c  -- can't be excluded for missing the 2nd
   WHERE  NOT EXISTS (
      SELECT *
      FROM   student_club AS d
      WHERE  d.stud_id = sc.stud_id
      AND    d.club_id = c.club_id
      )
   )

@ ypercube의 11)은 실제로도 아직 행방 불명 된이 간단한 변형, 단지 마음 트위스트 역 접근 방법이다. 수행합니다 거의 최대한 빨리 정상 고양이.

SELECT s.*
FROM   student s
JOIN   student_club x USING (stud_id)
WHERE  sc.club_id = 10                 -- member in 1st club ...
AND    EXISTS (                        -- ... and membership in 2nd exists
   SELECT *
   FROM   student_club AS y
   WHERE  y.stud_id = s.stud_id
   AND    y.club_id = 14
   )

하드 생각하지만, 여기에 진정, 새로운 변종 또 다른입니다합니다. 나는 두 개 이상의 회원에 대한 잠재적 볼 수 있지만, 그것은 또한 두와 상단의 고양이들 중 하나입니다.

SELECT s.*
FROM   student AS s
WHERE  EXISTS (
   SELECT *
   FROM   student_club AS x
   JOIN   student_club AS y USING (stud_id)
   WHERE  x.stud_id = s.stud_id
   AND    x.club_id = 14
   AND    y.club_id = 10
   )

즉 : 필터의 수를 변화. 이 질문은 정확히 두 개의 회원권을 요청. 그러나 많은 사용 사례는 다양한 수의 준비를해야합니다.

이 관련 나중에 대답 토론을 상세 :

==============================

2.

SELECT s.*
FROM student s
INNER JOIN student_club sc_soccer ON s.id = sc_soccer.student_id
INNER JOIN student_club sc_baseball ON s.id = sc_baseball.student_id
WHERE 
 sc_baseball.club_id = 50 AND 
 sc_soccer.club_id = 30

==============================

3.

select *
from student
where id in (select student_id from student_club where club_id = 30)
and id in (select student_id from student_club where club_id = 50)

==============================

4.그냥 다음 student_id을 원하는 경우 :

그냥 다음 student_id을 원하는 경우 :

    Select student_id
      from student_club
     where club_id in ( 30, 50 )
  group by student_id
    having count( student_id ) = 2

당신은 또한 다음 학생에서 이름을해야하는 경우 :

Select student_id, name
  from student s
 where exists( select *
                 from student_club sc
                where s.student_id = sc.student_id
                  and club_id in ( 30, 50 )
             group by sc.student_id
               having count( sc.student_id ) = 2 )

당신은 다음 club_selection 테이블에 두 개 이상의 클럽이있는 경우 :

Select student_id, name
  from student s
 where exists( select *
                 from student_club sc
                where s.student_id = sc.student_id
                  and exists( select * 
                                from club_selection cs
                               where sc.club_id = cs.club_id )
             group by sc.student_id
               having count( sc.student_id ) = ( select count( * )
                                                   from club_selection ) )

==============================

5.

SELECT *
FROM   student
WHERE  id IN (SELECT student_id
              FROM   student_club
              WHERE  club_id = 30
              INTERSECT
              SELECT student_id
              FROM   student_club
              WHERE  club_id = 50)

또는 더 쉽게 더 일반적인 솔루션은 N 클럽에 연장하고 (이 MySQL을 빨아의 성능으로) INTERSECT (MySQL을 사용할 수 없습니다) 및 IN을 방지

SELECT s.id,
       s.name
FROM   student s
       join student_club sc
         ON s.id = sc.student_id
WHERE  sc.club_id IN ( 30, 50 )
GROUP  BY s.id,
          s.name
HAVING COUNT(DISTINCT sc.club_id) = 2

==============================

6.또 다른 CTE. 그것은 깨끗하게 보이지만, 아마 정상 하위 쿼리에서 GROUPBY 같은 계획을 생성합니다.

또 다른 CTE. 그것은 깨끗하게 보이지만, 아마 정상 하위 쿼리에서 GROUPBY 같은 계획을 생성합니다.

WITH two AS (
    SELECT student_id FROM tmp.student_club
    WHERE club_id IN (30,50)
    GROUP BY student_id
    HAVING COUNT(*) > 1
    )
SELECT st.* FROM tmp.student st
JOIN two ON (two.student_id=st.id)
    ;

테스트를 원하는 사람들을 위해, 내 사본은 테스트 데이터 꼬추를 생성합니다 :

DROP SCHEMA tmp CASCADE;
CREATE SCHEMA tmp;

CREATE TABLE tmp.student
    ( id INTEGER NOT NULL PRIMARY KEY
    , sname VARCHAR
    );

CREATE TABLE tmp.club
    ( id INTEGER NOT NULL PRIMARY KEY
    , cname VARCHAR
    );

CREATE TABLE tmp.student_club
    ( student_id INTEGER NOT NULL  REFERENCES tmp.student(id)
    , club_id INTEGER NOT NULL  REFERENCES tmp.club(id)
    );

INSERT INTO tmp.student(id)
    SELECT generate_series(1,1000)
    ;

INSERT INTO tmp.club(id)
    SELECT generate_series(1,100)
    ;

INSERT INTO tmp.student_club(student_id,club_id)
    SELECT st.id  , cl.id
    FROM tmp.student st, tmp.club cl
    ;

DELETE FROM tmp.student_club
WHERE random() < 0.8
    ;

UPDATE tmp.student SET sname = 'Student#' || id::text ;
UPDATE tmp.club SET cname = 'Soccer' WHERE id = 30;
UPDATE tmp.club SET cname = 'Baseball' WHERE id = 50;

ALTER TABLE tmp.student_club
    ADD PRIMARY KEY (student_id,club_id)
    ;

==============================
7.그래서 피부에 하나 이상의 방법 고양이가있다. 나는 잘, 더 완전한, 그것을 만들기 위해 두 개를 추가 할 수 있습니다.

그래서 피부에 하나 이상의 방법 고양이가있다. 나는 잘, 더 완전한, 그것을 만들기 위해 두 개를 추가 할 수 있습니다.

온전한 데이터 모델 곳 (student_id, club_id)를 가정하면 student_club에서 유일하다. 마틴 스미스의 두 번째 버전은 다소 비슷한 유사하지만 그는 그룹 후, 첫 합류했다. 이것은 빨리해야한다 :
```
SELECT s.id, s.name
  FROM student s
  JOIN (
   SELECT student_id
     FROM student_club
    WHERE club_id IN (30, 50)
    GROUP BY 1
   HAVING COUNT(*) > 1
       ) sc USING (student_id);
```
그리고 물론, 고전적인 존재가있다. IN와 데릭의 변형 유사합니다. 간단하고 빠르게. (MySQL은,이 빠르게 IN과 변형에 비해 꽤이어야한다) :
```
SELECT s.id, s.name
  FROM student s
 WHERE EXISTS (SELECT 1 FROM student_club
               WHERE  student_id = s.student_id AND club_id = 30)
   AND EXISTS (SELECT 1 FROM student_club
               WHERE  student_id = s.student_id AND club_id = 50);
```

==============================

8.아무도 때문에 (고전) 버전을 추가했습니다 :

아무도 때문에 (고전) 버전을 추가했습니다 :

SELECT s.*
FROM student AS s
WHERE NOT EXISTS
      ( SELECT *
        FROM club AS c 
        WHERE c.id IN (30, 50)
          AND NOT EXISTS
              ( SELECT *
                FROM student_club AS sc 
                WHERE sc.student_id = s.id
                  AND sc.club_id = c.id  
              )
      )

또는 이와 유사한 :

SELECT s.*
FROM student AS s
WHERE NOT EXISTS
      ( SELECT *
        FROM
          ( SELECT 30 AS club_id  
          UNION ALL
            SELECT 50
          ) AS c
        WHERE NOT EXISTS
              ( SELECT *
                FROM student_club AS sc 
                WHERE sc.student_id = s.id
                  AND sc.club_id = c.club_id  
              )
      )

하나는 더 약간 다른 접근 방식을 시도합니다. EAV 테이블의 여러 속성 : 확장 된 것을 설명한다의 기사에서 영감을 대 BY 그룹은 NOT EXISTS :

SELECT s.*
FROM student_club AS sc
  JOIN student AS s
    ON s.student_id = sc.student_id
WHERE sc.club_id = 50                      --- one option here
  AND NOT EXISTS
      ( SELECT *
        FROM
          ( SELECT 30 AS club_id           --- all the rest in here
                                           --- as in previous query
          ) AS c
        WHERE NOT EXISTS
              ( SELECT *
                FROM student_club AS scc 
                WHERE scc.student_id = sc.id
                  AND scc.club_id = c.club_id  
              )
      )

또 다른 방법 :

SELECT s.stud_id
FROM   student s

EXCEPT

SELECT stud_id
FROM 
  ( SELECT s.stud_id, c.club_id
    FROM student s 
      CROSS JOIN (VALUES (30),(50)) c (club_id)
  EXCEPT
    SELECT stud_id, club_id
    FROM student_club
    WHERE club_id IN (30, 50)   -- optional. Not needed but may affect performance
  ) x ;

==============================

9.

WITH RECURSIVE two AS
    ( SELECT 1::integer AS level
    , student_id
    FROM tmp.student_club sc0
    WHERE sc0.club_id = 30
    UNION
    SELECT 1+two.level AS level
    , sc1.student_id
    FROM tmp.student_club sc1
    JOIN two ON (two.student_id = sc1.student_id)
    WHERE sc1.club_id = 50
    AND two.level=1
    )
SELECT st.* FROM tmp.student st
JOIN two ON (two.student_id=st.id)
WHERE two.level> 1

    ;

이것은 CTE-스캔을 피 두 개의 하위 쿼리가 필요하기 때문에, 합리적으로 잘 수행 할 것으로 보인다.

오용 재귀 쿼리에 대한 이유는 항상있다!

(BTW : MySQL은 재귀 쿼리를하지 않는 것)

==============================

10.이름이 catskin 목록과 다를 그래서는 실제 DB에서 테스트. 아무것도 (카탈로그에 사소한 변경 제외) 모든 테스트 실행 중에 변경되지 않도록 그것은 백업 복사본입니다.

이름이 catskin 목록과 다를 그래서는 실제 DB에서 테스트. 아무것도 (카탈로그에 사소한 변경 제외) 모든 테스트 실행 중에 변경되지 않도록 그것은 백업 복사본입니다.

SELECT a.*
FROM   ef.adr a
JOIN (
    SELECT adr_id
    FROM   ef.adratt
    WHERE  att_id IN (10,14)
    GROUP  BY adr_id
    HAVING COUNT(*) > 1) t using (adr_id);

Merge Join  (cost=630.10..1248.78 rows=627 width=295) (actual time=13.025..34.726 rows=67 loops=1)
  Merge Cond: (a.adr_id = adratt.adr_id)
  ->  Index Scan using adr_pkey on adr a  (cost=0.00..523.39 rows=5767 width=295) (actual time=0.023..11.308 rows=5356 loops=1)
  ->  Sort  (cost=630.10..636.37 rows=627 width=4) (actual time=12.891..13.004 rows=67 loops=1)
        Sort Key: adratt.adr_id
        Sort Method:  quicksort  Memory: 28kB
        ->  HashAggregate  (cost=450.87..488.49 rows=627 width=4) (actual time=12.386..12.710 rows=67 loops=1)
              Filter: (count(*) > 1)
              ->  Bitmap Heap Scan on adratt  (cost=97.66..394.81 rows=2803 width=4) (actual time=0.245..5.958 rows=2811 loops=1)
                    Recheck Cond: (att_id = ANY ('{10,14}'::integer[]))
                    ->  Bitmap Index Scan on adratt_att_id_idx  (cost=0.00..94.86 rows=2803 width=0) (actual time=0.217..0.217 rows=2811 loops=1)
                          Index Cond: (att_id = ANY ('{10,14}'::integer[]))
Total runtime: 34.928 ms

WITH two AS (
    SELECT adr_id
    FROM   ef.adratt
    WHERE  att_id IN (10,14)
    GROUP  BY adr_id
    HAVING COUNT(*) > 1
    )
SELECT a.*
FROM   ef.adr a
JOIN   two using (adr_id);

Hash Join  (cost=1161.52..1261.84 rows=627 width=295) (actual time=36.188..37.269 rows=67 loops=1)
  Hash Cond: (two.adr_id = a.adr_id)
  CTE two
    ->  HashAggregate  (cost=450.87..488.49 rows=627 width=4) (actual time=13.059..13.447 rows=67 loops=1)
          Filter: (count(*) > 1)
          ->  Bitmap Heap Scan on adratt  (cost=97.66..394.81 rows=2803 width=4) (actual time=0.252..6.252 rows=2811 loops=1)
                Recheck Cond: (att_id = ANY ('{10,14}'::integer[]))
                ->  Bitmap Index Scan on adratt_att_id_idx  (cost=0.00..94.86 rows=2803 width=0) (actual time=0.226..0.226 rows=2811 loops=1)
                      Index Cond: (att_id = ANY ('{10,14}'::integer[]))
  ->  CTE Scan on two  (cost=0.00..50.16 rows=627 width=4) (actual time=13.065..13.677 rows=67 loops=1)
  ->  Hash  (cost=384.68..384.68 rows=5767 width=295) (actual time=23.097..23.097 rows=5767 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 1153kB
        ->  Seq Scan on adr a  (cost=0.00..384.68 rows=5767 width=295) (actual time=0.005..10.955 rows=5767 loops=1)
Total runtime: 37.482 ms

==============================
11.@ 어윈 - brandstetter하십시오, 벤치 마크이 :

@ 어윈 - brandstetter하십시오, 벤치 마크이 :
```
SELECT s.stud_id, s.name
FROM   student s, student_club x, student_club y
WHERE  x.club_id = 30
AND    s.stud_id = x.stud_id
AND    y.club_id = 50
AND    s.stud_id = y.stud_id;
```
그것은 @sean, 단지 청소기로 번호 6)처럼, 그런 것 같아요.

==============================

12.

-- EXPLAIN ANALYZE
WITH two AS (
    SELECT c0.student_id
    FROM tmp.student_club c0
    , tmp.student_club c1
    WHERE c0.student_id = c1.student_id
    AND c0.club_id = 30
    AND c1.club_id = 50
    )
SELECT st.* FROM tmp.student st
JOIN two ON (two.student_id=st.id)
    ;

쿼리 계획 :

 Hash Join  (cost=1904.76..1919.09 rows=337 width=15) (actual time=6.937..8.771 rows=324 loops=1)
   Hash Cond: (two.student_id = st.id)
   CTE two
     ->  Hash Join  (cost=849.97..1645.76 rows=337 width=4) (actual time=4.932..6.488 rows=324 loops=1)
           Hash Cond: (c1.student_id = c0.student_id)
           ->  Bitmap Heap Scan on student_club c1  (cost=32.76..796.94 rows=1614 width=4) (actual time=0.667..1.835 rows=1646 loops=1)
                 Recheck Cond: (club_id = 50)
                 ->  Bitmap Index Scan on sc_club_id_idx  (cost=0.00..32.36 rows=1614 width=0) (actual time=0.473..0.473 rows=1646 loops=1)                     
                       Index Cond: (club_id = 50)
           ->  Hash  (cost=797.00..797.00 rows=1617 width=4) (actual time=4.203..4.203 rows=1620 loops=1)
                 Buckets: 1024  Batches: 1  Memory Usage: 57kB
                 ->  Bitmap Heap Scan on student_club c0  (cost=32.79..797.00 rows=1617 width=4) (actual time=0.663..3.596 rows=1620 loops=1)                   
                       Recheck Cond: (club_id = 30)
                       ->  Bitmap Index Scan on sc_club_id_idx  (cost=0.00..32.38 rows=1617 width=0) (actual time=0.469..0.469 rows=1620 loops=1)
                             Index Cond: (club_id = 30)
   ->  CTE Scan on two  (cost=0.00..6.74 rows=337 width=4) (actual time=4.935..6.591 rows=324 loops=1)
   ->  Hash  (cost=159.00..159.00 rows=8000 width=15) (actual time=1.979..1.979 rows=8000 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 374kB
         ->  Seq Scan on student st  (cost=0.00..159.00 rows=8000 width=15) (actual time=0.093..0.759 rows=8000 loops=1)
 Total runtime: 8.989 ms
(20 rows)

그래서 여전히 학생의 서열 스캔을 할 것으로 보인다.

==============================
13.
```
SELECT s.stud_id, s.name
FROM   student s,
(
select x.stud_id from 
student_club x 
JOIN   student_club y ON x.stud_id = y.stud_id
WHERE  x.club_id = 30
AND    y.club_id = 50
) tmp_tbl
where tmp_tbl.stud_id = s.stud_id
;
```
빠른 변형의 사용 (씨 Brandstetter 차트 씨 숀). 단 하나에 만 student_club 매트릭스 라이브에 대한 권리를 가진다 가입으로 변형 될 수있다. 그래서, 가장 긴 쿼리가 계산하는 두 열이있을 것이다, 생각은 쿼리가 얇은 만드는 것입니다.

from https://stackoverflow.com/questions/7364969/how-to-filter-sql-results-in-a-has-many-through-relation by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 카운트 대 (*) COUNT (1) - SQL 서버 (0)	2020.03.06
[SQL] 조인 가입 및 다른 유형의 SQL 지 (0)	2020.03.06
[SQL] 어떻게 오라클에 AUTO INCREMENT와 ID를 만드는 방법? (0)	2020.03.06
[SQL] 엔티티 프레임 워크에 삽입하는 가장 빠른 방법 (0)	2020.03.06
[SQL] 빠른 600K 행에서 10 개 임의의 행을 선택 MYSQL (0)	2020.03.06

복붙노트

[SQL] A는 관계를 통해-많은이에 SQL 결과를 필터링하는 방법

A는 관계를 통해-많은이에 SQL 결과를 필터링하는 방법

해결법

1.나는 궁금했어. 우리 모두가 알다시피, 호기심이 고양이를 죽이는에 대한 명성을 가지고있다.

2.

3.

4.그냥 다음 student_id을 원하는 경우 :

5.

6.또 다른 CTE. 그것은 깨끗하게 보이지만, 아마 정상 하위 쿼리에서 GROUPBY 같은 계획을 생성합니다.

7.그래서 피부에 하나 이상의 방법 고양이가있다. 나는 잘, 더 완전한, 그것을 만들기 위해 두 개를 추가 할 수 있습니다.

8.아무도 때문에 (고전) 버전을 추가했습니다 :

9.

10.이름이 catskin 목록과 다를 그래서는 실제 DB에서 테스트. 아무것도 (카탈로그에 사소한 변경 제외) 모든 테스트 실행 중에 변경되지 않도록 그것은 백업 복사본입니다.

11.@ 어윈 - brandstetter하십시오, 벤치 마크이 :

12.

13.

'SQL' 카테고리의 다른 글

티스토리툴바