PostgreSQL在连接查询中如何计算重复行数

Question

PostgreSQL在连接查询中如何计算重复行数

4

我有一个复杂的问题。我会尝试用例子来解释：

我有一张表，它有一个主键，我想要加入另一张表，其中第一张表的主键是外键，如果在第二张表中存在重复的外键，则我想要选择重复性的数量。例如：

第一张表：

  id    name 
  ---  -----
  1     Greg
  2     Alan 
  3    George 
  4     John 
  5     Peter

第二个表格

 id       aid        data   
 ---     -----      -------
 1        2          CCCV   
 2        2          VVVV 
 3        3          DDDDD 
 4        3          SSSS 
 5        4          PPPPP

我希望联接的结果为：

 id(1st table)  aid   name    Data   Number
 -----------    ----  -----   -----  -----
 1               null  Greg    null   1
 2                1    Alan    CCCV   1
 2                2    Alan    VVVV   2
 3                3    George  DDDDD  1
 3                4    George  SSSS   2
 4                5    John    PPPPP  1
 5               null  Peter   null   1

我进行了大量的搜索，但仍然没有找到所需内容。可能是因为我的搜索方式不对，或者我想做的事情并不存在。

- Julian

MySQL和PostGreSQL具有非常不同的功能。我将使用ROW_NUMBER()来创建您的最终列，这在PostGreSQL中存在，但在MySQL中不存在。您实际上使用哪个？ - MatBailie

数据在 SQLFiddle 上：http://sqlfiddle.com/#!12/319f6 - Craig Ringer

我正在使用PostgreSQL，但在“OVER”语句中出现错误。 - Julian

@dna - 请问您使用的是哪个确切版本的PostGreSQL？还有，您遇到了什么确切的错误信息？（请注意，在SQL Fiddle上它是可以工作的...） - MatBailie

版本是8.3，我看到这个版本不支持ROW_NUMBER()函数，所以它给了我一个错误 :) - Julian

3个回答

2

SELECT Table1.id, Table2.id as aid, Table1.name, Table2.data,
GREATEST(1, (SELECT COUNT(*)
             FROM Table2 t2
             WHERE t2.aid = Table1.id
             AND t2.id <= Table2.id))
AS number
FROM Table1
LEFT JOIN Table2
ON Table2.aid = Table1.id
ORDER BY id, aid;

适用于MySQL和PostgreSQL两种数据库。

- Terje D.

0

^{针对没有窗口函数的PostgreSQL 8.3的查询。}
对于更大的表，通常使用JOIN比相关子查询更快。
第一个查询在加入Table1之前聚合了Table2的值，这应该也更快：

SELECT t1.id, t2.aid, t1.name, t2.data, COALESCE(t2.ct, 1) AS number
FROM   Table1 t1
LEFT   JOIN  (
    SELECT x.aid, x.data, count(y.aid) + 1 AS ct
    FROM   Table2 x
    LEFT   JOIN Table2 y ON x.aid = y.aid AND x.id > y.id
    GROUP  BY x.aid, x.data
    ) t2 ON t2.aid = t1.id
ORDER  BY t1.id, t2.ct;

而且ORDER BY应该被修复。

没有子查询的替代方案。可能更快，但是：

SELECT t1.id, t2.aid, t1.name, t2.data, count(*) + count(t3.id) AS number
FROM   Table1 t1
LEFT   JOIN Table2 t2 ON t2.aid = t1.id
LEFT   JOIN Table2 t3 ON t3.aid = t2.aid AND t3.id < t2.id
GROUP  BY t1.id, t2.aid, t1.name, t2.data
ORDER  BY t1.id, count(t3.id);

不确定，没有使用更大的数据集进行测试。请使用EXPLAIN ANALYZE测试性能。您能否回报一下您的结果？

- Erwin Brandstetter

问题在于这只是一个例子。实际工作更加复杂，在实际查询中连接了超过15个表，当我添加你建议的逻辑时，会在一个未受更改影响的列中出现错误。我不确定是否粘贴整个查询，任何人都能理解我在说什么... - Julian

如果您感兴趣，我可以粘贴整个查询，并添加您建议的内容？ - Julian

@dna：我建议你开一个新问题，展示你的完整（简化后的）查询。你很可能会很快得到修复。我猜测是未聚合的列。但最好还是问一个新问题，而不是猜测。你可以随时链接到这个问题，以提供更多上下文。使用“分享”下提供的链接。 - Erwin Brandstetter

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- MatBailie · Accepted Answer

根据我的评论，你已经标记了MySQL和PostgreSQL两个标签。

这个回答是针对PostgreSQL的。

SELECT
  table1.id,
  table2.aid,
  table1.name,
  table2.data,
  ROW_NUMBER() OVER (PARTITION BY table1.id ORDER BY table2.aid) AS number
FROM
  table1
LEFT JOIN
  table2
    ON table1.id = table2.aid