在单独的字段中计算具有匹配值的记录数 [英] Count number of records with matching values in separate fields

问题描述

我有一个这样的表（myTable）：

I have a table (myTable) as such:

id  | name  | orig_id
----+-------+--------
01  | Bill  | -
02  | Tom   | 01
03  | Sam   | 01
04  | Alex  | 02
05  | Phil  | -
06  | Bob   | 01

我想要一个查询，该查询返回每条记录，但是增加了包含其他计数的列orig_id等于当前行ID的行。


结果表如下所示：

I'd like a query that returns each record but with an added column containing the count of other rows that have an orig_id equal to the current row's id.

The resulting table would look like this:

id  | name  | orig_id | mycount
----+-------+---------+--------
01  | Bill  | -       | 3
02  | Tom   | 01      | 1
03  | Sam   | 01      | 0
04  | Alex  | 02      | 0
05  | Phil  | -       | 0
06  | Bob   | 01      | 0

我尝试了以下查询，但没有结果：

I've tried the following query, but get no results:

SELECT *, COUNT(t.name) AS mycount
FROM "myTable" AS t
WHERE t.id=t.orig_id
GROUP BY t.id;

如何获得所需的结果？

推荐答案

您可以使用联接和聚合来实现：

You can do this with a join and aggregation:

SELECT t.*, tsum.mycount 
FROM myTable t join
     (select orig_id, count(name) as mycount
      from myTable
      group by orig_id
     ) tsum
     on t.id = tsum.orig_id;

这篇关于在单独的字段中计算具有匹配值的记录数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！