postgresql 如何过滤具有多次通过关系的 SQL 结果

Question

提问by Xeoncross

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

假设我有表student, club, 和student_club：

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

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:

我想知道如何找到足球 (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

Answer 1

回答by Erwin Brandstetter

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

我很好奇。众所周知，好奇心以杀死猫而闻名。

So, which is the fastest way to skin a cat?

那么，给猫剥皮最快的方法是什么？

The precise cat-skinning environment for this test:

本次测试的精确剥猫环境：

PostgreSQL 9.0on 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.idis student.stud_idand club.idis club.club_idhere.
I named the queries after their author in this thread, with an index where there are two.
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);
```
club_pkeyis 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 indexeson PostgreSQL:

Debian Squeeze 上的PostgreSQL 9.0具有不错的 RAM 和设置。
6.000 名学生，24.000 名俱乐部会员（从具有现实生活数据的类似数据库复制的数据。）
稍微偏离了问题中的命名模式：student.idisstudent.stud_id和club.idis club.club_idhere。
我在此线程中以其作者的名字命名查询，并在其中有两个索引。
我运行了几次所有查询以填充缓存，然后我使用 EXPLAIN ANALYZE 选择了 5 个中最好的。
相关指标（应该是最佳的——只要我们不知道哪些俱乐部会被查询）：
```
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);
```
club_pkey此处的大多数查询不需要。
主键在 PostgreSQL 中自动实现唯一索引。
最后一个索引是为了弥补PostgreSQL上多列索引的这个众所周知的缺点：

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.

多列 B 树索引可与涉及索引列的任何子集的查询条件一起使用，但当对前导（最左侧）列存在约束时，该索引的效率最高。

Results:

结果：

Total runtimes from EXPLAIN ANALYZE.

来自 EXPLAIN ANALYZE 的总运行时间。

1) Martin 2: 44.594 ms

1) 马丁 2：44.594 毫秒

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) Erwin 1: 33.217 ms

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) Martin 1: 31.735 ms

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) Derek: 2.287 ms

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) Erwin 2: 2.181 ms

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) Sean: 2.043 ms

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;

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

最后三个表现几乎相同。4) 和 5) 导致相同的查询计划。

Late Additions:

迟到的补充：

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

花式SQL，但性能跟不上。

7) ypercube 1: 148.649 ms

7) 超立方体 1：148.649 毫秒

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) ypercube 2: 147.497 ms

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

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.

正如预期的那样，这两者的表现几乎相同。查询计划导致表扫描，计划器在此处找不到使用索引的方法。

9) wildplasser 1: 49.849 ms

9) Wildplasser 1: 49.849 毫秒

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, decent performance for a CTE. Very exotic query plan.
Again, would be interesting how 9.1 handles this. I am going to upgrade the db cluster used here to 9.1 soon. Maybe I'll rerun the whole shebang ...

Fancy SQL，CTE 性能不错。非常奇特的查询计划。
同样，9.1 如何处理这个会很有趣。我打算很快将这里使用的数据库集群升级到 9.1。也许我会重新运行整个shebang ...

10) wildplasser 2: 36.986 ms

10) Wildplasser 2：36.986 毫秒

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 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.

查询 2) 的 CTE 变体。令人惊讶的是，对于完全相同的数据，它可能会导致略有不同的查询计划。我在上找到了一个顺序扫描student，其中子查询变体使用了索引。

11) ypercube 3: 101.482 ms

11) 超立方体 3：101.482 毫秒

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

另一个迟到的@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
      )
   )

12) erwin 3: 2.377 ms

12) 埃尔文 3：2.377 毫秒

@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.

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

13) erwin 4: 2.375 ms

13) 埃尔文 4: 2.375 毫秒

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
   )

Dynamic number of club memberships

俱乐部会员动态数量

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

换句话说：不同数量的过滤器。这个问题要求正好有两个俱乐部会员资格。但是许多用例必须为不同的数量做准备。

Detailed discussion in this related later answer:

此相关稍后答案中的详细讨论：

Using same column multiple times in WHERE clause

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

Answer 2

回答by Sean

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

Answer 3

回答by Derek Kromm

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)

Answer 4

回答by Paul Morgan

If you just want student_id then:

如果你只想要 student_id 那么：

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

If you also need name from student then:

如果您还需要学生的姓名，则：

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 )

If you have more than two clubs in a club_selection table then:

如果 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 ) )

Answer 5

回答by Martin Smith

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)

Or a more general solution easier to extend to nclubs and that avoids INTERSECT(not available in MySQL) and IN(as performance of this sucks in MySQL)

或者更通用的解决方案更容易扩展到n俱乐部并且避免INTERSECT（在 MySQL 中不可用）和IN（因为MySQL 的性能很差）

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

Answer 6

回答by wildplasser

Another CTE. It looks clean, but it will probably generate the same plan as a groupby in a normal subquery.

另一个 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)
    ;

For those who want to test, a copy of my generate testdata thingy:

对于那些想要测试的人，请复制一份我的 generate testdata 东西：

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

Answer 7

回答by Erwin Brandstetter

So there's more than one way to skin a cat.
I'll to add two moreto make it, well, more complete.

所以给猫剥皮的方法不止一种。
我将再添加两个以使其更完整。

1) GROUP first, JOIN later

1）先分组，后加入

Assuming a sane data model where (student_id, club_id)is uniquein student_club. Martin Smith's second version is like somewhat similar, but he joins first, groups later. This should be faster:

假设一个理智的数据模型，其中(student_id, club_id)是唯一在student_club。Martin Smith 的第二个版本有点相似，但他先加入，后加入。这应该更快：

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

2) EXISTS

2) 存在

And of course, there is the classic EXISTS. Similar to Derek's variant with IN. Simple and fast. (In MySQL, this should be quite a bit faster than the variant with IN):

当然，还有经典EXISTS。类似于 Derek 的变体，带有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);

Answer 8

回答by ypercube??

Since noone has added this (classic) version:

由于没有人添加此（经典）版本：

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

or similar:

或类似：

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

One more try with a slightly different approach. Inspired by an article in Explain Extended: Multiple attributes in a EAV table: GROUP BY vs. NOT EXISTS:

再尝试一种稍微不同的方法。受解释扩展中的一篇文章启发：EAV 表中的多个属性：GROUP 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  
              )
      )

Another approach:

另一种方法：

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 ;

Answer 9

回答by wildplasser

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

    ;

This seems to perform reasonably well, since the CTE-scan avoids the need for two separate subqueries.

这似乎表现得相当好，因为 CTE 扫描避免了对两个单独子查询的需要。

There is always a reason to misuse recursive queries!

总是有理由滥用递归查询！

(BTW: mysql does not seem to have recursive queries)

（顺便说一句：mysql 似乎没有递归查询）

Answer 10

回答by Erwin Brandstetter

Different query plans in query 2) and 10)

查询 2) 和 10) 中的不同查询计划

I tested in a real life db, so the names differ from the catskin list. It's a backup copy, so nothing changed during all test runs (except minor changes to the catalogs).

我在现实生活中的数据库中进行了测试，因此名称与猫皮列表不同。这是一个备份副本，所以在所有测试运行期间没有任何变化（除了对目录的微小更改）。

Query 2)

查询 2)

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

Query 10)

查询 10)

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

postgresql 如何过滤具有多次通过关系的 SQL 结果

提问by Xeoncross

回答by Erwin Brandstetter

So, which is the fastest way to skin a cat?

那么，给猫剥皮最快的方法是什么？

Results:

结果：

1) Martin 2: 44.594 ms

1) 马丁 2：44.594 毫秒

2) Erwin 1: 33.217 ms

2) 欧文 1：33.217 毫秒

3) Martin 1: 31.735 ms

3) 马丁 1: 31.735 毫秒

4) Derek: 2.287 ms

4) 德里克：2.287 毫秒

5) Erwin 2: 2.181 ms

5) 欧文 2：2.181 毫秒

6) Sean: 2.043 ms

6) 肖恩：2.043 毫秒

Late Additions:

迟到的补充：

7) ypercube 1: 148.649 ms

7) 超立方体 1：148.649 毫秒

8) ypercube 2: 147.497 ms

8) 超立方体 2：147.497 毫秒

9) wildplasser 1: 49.849 ms

9) Wildplasser 1: 49.849 毫秒

10) wildplasser 2: 36.986 ms

10) Wildplasser 2：36.986 毫秒

11) ypercube 3: 101.482 ms

11) 超立方体 3：101.482 毫秒

12) erwin 3: 2.377 ms

12) 埃尔文 3：2.377 毫秒

13) erwin 4: 2.375 ms

13) 埃尔文 4: 2.375 毫秒

Dynamic number of club memberships

俱乐部会员动态数量

回答by Sean

回答by Derek Kromm

回答by Paul Morgan

回答by Martin Smith

回答by wildplasser

回答by Erwin Brandstetter

1) GROUP first, JOIN later

1）先分组，后加入

2) EXISTS

2) 存在

回答by ypercube??

回答by wildplasser

回答by Erwin Brandstetter

Different query plans in query 2) and 10)

查询 2) 和 10) 中的不同查询计划

Query 2)

查询 2)

Query 10)

查询 10)

相关推荐

postgreSQL uuid 生成

postgresql 将t-sql（SQL Server）存储过程转换为pgsql（postgre sql）的工具

postgresql 返回 NULL 的空数组的 array_length()

postgresql PostGIS 错误：类型“地理”不存在

相关推荐

最近更新

标签