SQL: 如何查找一个表与另一个表相连的最小条目数

Question

假设我有一个名为Table_A的表：

A_id | A_val
1      a
2      b
3      c

一些 Table_B：

B_id | B_val
1      d
2      e
3      g

还需要一个连接器 Table_C：

A_id | B_id
1      1
2      1
2      2
3      1
3      2
3      3

我需要帮助，在表A中找到与之关联的表B中最少的项目。 我目前初学SQL，使用PostgreSQL，并且想到可能需要使用子查询。我已经成功地使用以下内容计算了链接数：

SELECT A_id, COUNT(B_id) as Num_links
  FROM TABLE_C
  GROUP BY A_id;

但是我不知道接下来该怎么办。

- sounami

---

你是想找到单个项目，还是可能有多个项目在Table_C中都具有相同（最低）数量的项目？ - gmm

我需要选择表C中具有相同（最低）项目数量的所有项目。很抱歉我没有在详细信息中包含这个。 - sounami

表A中是否存在没有在表C中有任何链接的条目？这些会是“优胜者”吗，拥有0个链接？另外，你忘记提供你的Postgres版本了。 - Erwin Brandstetter

Answer 1

您可以使用with子句给您的“count”查询命名别名，并将其视为临时表。然后选择a_id，并将num_links小于或等于num_links中最低计数的值。

WITH link_counts AS (
         SELECT a_id, COUNT(b_id) as num_links
           FROM table_c
       GROUP BY a_id
     )
SELECT a_id
  FROM link_counts
 WHERE num_links <= (SELECT MIN(num_links) FROM link_counts)

请注意，如果不同的a_id具有相同（最低）数量的链接，则可能返回多个行（例如，如果a_id 1和4仅各具有1个链接）。

- matts

---

查看这个SQL Fiddle演示。它可以正常工作。干得好，马特斯。 - gmm

非常感谢，这似乎运行得非常好。同时，我也想感谢你们介绍给我 SQL Fiddle。 - sounami

Answer 2

你可以使用RANK()。这将按COUNT(Bid)对你的援助进行排名--对于具有相同数量的援助，所有援助将返回相同的排名。

SELECT *
FROM A T1
  JOIN (
    SELECT Aid, RANK() OVER (ORDER BY COUNT(Bid)) rnk
    FROM C 
    GROUP BY Aid
    ) T2 ON T1.Id = T2.Aid
WHERE T2.rnk = 1

这里是Fiddle。

祝你好运。

- sgeddes

Answer 3

这里是策略。计算最大链接数。您可以通过使用order by和limit修改查询来实现。

接下来，计算tableC中每行的总链接数。为此，我使用了一个窗口函数。语句如下：

count(*) over (partition by a_id)

创建一个变量，它是表格中"a"的计数。

然后将其连接在一起。

select distinct c.a_id
from (select c.*,
             count(*) over (partition by a_id) as num_links
      from table_c c
     ) c join
     (select a_id, count(*) as num_links
      from table_c c
      group by a_id
      order by 2 asc
      limit 1
     ) cmax
     on c.num_links = cmax.num_links

- Gordon Linoff

---

问题是在Table_A中找到与Table_B关联最少的项目，因此您应该使用asc而不是desc。不错的解决方案。 - gmm

Answer 4

这里有另一种选择。它在 HAVING 子句中使用了一个子查询：

SELECT DISTINCT AId, COUNT(*)
FROM C
GROUP BY AId
HAVING COUNT(*) <= ALL (SELECT COUNT(*) 
                        FROM C 
                        GROUP BY AId)

还有相关的fiddle。我不知道这与其他解决方案在性能方面如何比较，但它似乎清楚地展示了正在发生的事情。

- Wilduck

Answer 5

看起来其他人有更优雅的解决方案...我的SQL技能有点生疏，但这个也可以工作。

CREATE TABLE Table_C
(
    A_id INT,
    B_id INT
);

INSERT INTO Table_C (A_id, B_id) VALUES (13, 112);
INSERT INTO Table_C (A_id, B_id) VALUES (44, 105);
INSERT INTO Table_C (A_id, B_id) VALUES (66, 68);
INSERT INTO Table_C (A_id, B_id) VALUES (13, 113);
INSERT INTO Table_C (A_id, B_id) VALUES (445, 105);
INSERT INTO Table_C (A_id, B_id) VALUES (660, 68);

CREATE TABLE TempTable
(
    A_id INT,
    Cnt INT
);

INSERT INTO
  TempTable (A_id, Cnt)
SELECT
  t.A_id
  , COUNT(t.A_id) AS Cnt
FROM
  Table_C t
GROUP BY
  t.A_id;

SELECT @minCnt := MIN(Cnt) FROM TempTable;

SELECT
  A_id
FROM
  Table_C
GROUP BY
  A_id
HAVING
  COUNT(A_id) = @minCnt;

- joebarbere

Answer 6

WITH ct AS (
   SELECT a.a_id
         ,count(c.a_id) AS link_ct
         ,min(count(c.a_id)) OVER () AS min_ct
   FROM   table_a a
   LEFT   JOIN table_c c USING (a_id)
   GROUP  BY 1
   )
SELECT a_id, link_ct
FROM   ct
WHERE  link_ct = min_ct;

这与@matts发布的类似，但在某些方面有所不同：

• 在CTE ct中，我使用LEFT JOIN连接到table_c，这样我就不会错过从table_a到table_b没有0个连接的行，这是根据问题定义应该获胜的。
• 使用窗口函数在CTE中计算min_ct（因此在最终的WHERE条件中没有额外的子查询）。可能更快，无论如何都更清洁。
• 最终的WHERE条件使用=而不是<=。

->sqlfiddle演示了差异。

- Erwin Brandstetter