問(wèn)題描述

假設(shè)我有 student、club 和 student_club 表:

Assuming I have the tables student, club, and student_club:

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

我想知道如何找到足球 (30) 和棒球 (50) 俱樂(lè)部的所有學(xué)生.
雖然此查詢(xún)不起作用，但它是我迄今為止最接近的:

I want to know how to find all students in both the soccer (30) and baseball (50) club.
While this query doesn't work, it's the closest thing I have so far:

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

推薦答案

我很好奇.眾所周知，好奇心以殺死貓而聞名.

I was curious. And as we all know, curiosity has a reputation for killing cats.

本次測(cè)試的貓皮環(huán)境:

• PostgreSQL 9.0 在 Debian Squeeze 上運(yùn)行，具有不錯(cuò)的 RAM 和設(shè)置.
• 6,000 名學(xué)生，24,000 名俱樂(lè)部會(huì)員(從具有現(xiàn)實(shí)生活數(shù)據(jù)的類(lèi)似數(shù)據(jù)庫(kù)中復(fù)制的數(shù)據(jù).)
• 稍微偏離了問(wèn)題中的命名模式:student.id 是 student.stud_id 而 club.id 是 club.club_id 在這里.
• 我在此線程中以其作者的名字命名了這些查詢(xún).
• 我運(yùn)行了幾次所有查詢(xún)以填充緩存，然后我使用 EXPLAIN ANALYZE 選擇了 5 個(gè)中最好的.
• 相關(guān)索引(應(yīng)該是最佳的——只要我們不知道哪些俱樂(lè)部會(huì)被查詢(xún)):

• PostgreSQL 9.0 on Debian Squeeze with decent RAM and settings.
• 6.000 students, 24.000 club memberships (data copied from a similar database with real life data.)
• Slight diversion from the naming schema in the question: student.id is student.stud_id and club.id is club.club_id here.
• I named the queries after their author in this thread.
• I ran all queries a couple of times to populate the cache, then I picked the best of 5 with EXPLAIN ANALYZE.
• Relevant indexes (should be the optimum - as long as we lack fore-knowledge which clubs will be queried):

ALTER TABLE student ADD CONSTRAINT student_pkey PRIMARY KEY(stud_id );
ALTER TABLE student_club ADD CONSTRAINT sc_pkey PRIMARY KEY(stud_id, club_id);
ALTER TABLE club       ADD CONSTRAINT club_pkey PRIMARY KEY(club_id );
CREATE INDEX sc_club_id_idx ON student_club (club_id);

此處的大多數(shù)查詢(xún)不需要

club_pkey.
主鍵在 PostgreSQL 中自動(dòng)實(shí)現(xiàn)唯一索引.
最后一個(gè)索引是為了彌補(bǔ)多列索引 在 PostgreSQL 上:

club_pkey is not required by most queries here.
Primary keys implement unique indexes automatically In PostgreSQL.
The last index is to make up for this known shortcoming of multi-column indexes on PostgreSQL:

多列 B 樹(shù)索引可以與查詢(xún)條件一起使用涉及索引列的任何子集，但索引是最多的當(dāng)對(duì)前導(dǎo)(最左側(cè))列有約束時(shí)效率高.

A multicolumn B-tree index can be used with query conditions that involve any subset of the index's columns, but the index is most efficient when there are constraints on the leading (leftmost) columns.

結(jié)果

來(lái)自 EXPLAIN ANALYZE 的總運(yùn)行時(shí)間.

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;

2) 埃爾文 1:33.217 毫秒

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);

3) 馬丁 1:31.735 毫秒

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
   );

4) 德里克:2.287 毫秒

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);

5) 埃爾文 2:2.181 毫秒

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);

6) 肖恩:2.043 毫秒

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;

最后三個(gè)表現(xiàn)幾乎相同.4) 和 5) 產(chǎn)生相同的查詢(xún)計(jì)劃.

The last three perform pretty much the same. 4) and 5) result in the same query plan.

花哨的 SQL，但性能跟不上:

Fancy SQL, but the performance can't keep up:

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  
      )
   );

8) 超立方體 2:147.497 毫秒

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  
      )
   );

不出所料，這兩者的表現(xiàn)幾乎相同.查詢(xún)計(jì)劃導(dǎo)致表掃描，計(jì)劃器在此處找不到使用索引的方法.

As expected, those two perform almost the same. Query plan results in table scans, the planner doesn't find a way to use the indexes here.

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;

Fancy SQL，CTE 性能不錯(cuò).非常奇特的查詢(xún)計(jì)劃.

Fancy SQL, decent performance for a CTE. Very exotic query plan.

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);

查詢(xún) 2) 的 CTE 變體.令人驚訝的是，對(duì)于完全相同的數(shù)據(jù)，它可能會(huì)導(dǎo)致略有不同的查詢(xún)計(jì)劃.我在 student 上發(fā)現(xiàn)了一個(gè)順序掃描，其中子查詢(xún)變體使用了索引.

CTE variant of query 2). Surprisingly, it can result in a slightly different query plan with the exact same data. I found a sequential scan on student, where the subquery-variant used the index.

另一個(gè)后期添加的超立方體.真是太神奇了，有多少種方法.

Another late addition ypercube. It is positively amazing, how many ways there are.

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
      )
   );

12) 埃爾文 3:2.377 毫秒

ypercube 的 11) 實(shí)際上只是這個(gè)更簡(jiǎn)單變體的令人費(fèi)解的反向方法，它也仍然缺失.表現(xiàn)幾乎和頂級(jí)貓一樣快.

12) erwin 3: 2.377 ms

ypercube's 11) is actually just the mind-twisting reverse approach of this simpler variant, that was also still missing. Performs almost as fast as the top cats.

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
   );

13) 埃爾文 4:2.375 毫秒

難以置信，但這是另一個(gè)真正的新變體.我看到了超過(guò)兩個(gè)會(huì)員資格的潛力，但它也僅擁有兩個(gè)會(huì)員資格就躋身頂級(jí)貓之列.

13) erwin 4: 2.375 ms

Hard to believe, but here's another, genuinely new variant. I see potential for more than two memberships, but it also ranks among the top cats with just two.

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
   );

俱樂(lè)部會(huì)員動(dòng)態(tài)數(shù)量

換句話說(shuō):不同數(shù)量的過(guò)濾器.這個(gè)問(wèn)題正好要求兩個(gè)俱樂(lè)部會(huì)員資格.但是許多用例必須為不同的數(shù)量做準(zhǔn)備.見(jiàn):

Dynamic number of club memberships

In other words: varying number of filters. This question asked for exactly two club memberships. But many use cases have to prepare for a varying number. See:

• 在 WHERE 子句中多次使用同一列

這篇關(guān)于如何過(guò)濾具有多次通過(guò)關(guān)系的 SQL 結(jié)果的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！