MySQL计数具有两个重复列值的行 - MySQL count rows with two duplicate column values

MySQL count rows with two duplicate column values

本文关键字：两个 MySQL | 更新日期: 2023-09-27

我想计数具有相同serv和spec的行。以下查询正确计数行数：

SELECT *, COUNT(*) AS c
FROM table1
GROUP BY serv, spec

但我希望输出显示为：

原件：

+-----+------+------+
| id  | serv | spec |
| 500 | 1    | 4    |
| 501 | 5    | 1    |
| 502 | 1    | 4    |
| 503 | 1    | 5    |
| 504 | 5    | 6    |
| 505 | 2    | 4    |
| 506 | 5    | 1    |
| 507 | 2    | 4    |
| 508 | 4    | 3    |
| 509 | 2    | 4    |
+-----+------+------+

期望输出：

+-----+------+------+-------+
| id  | serv | spec | count |
| 500 | 1    | 4    | 2     |
| 502 | 1    | 4    | 2     |
| 503 | 1    | 5    | 1     |
| 505 | 2    | 4    | 3     |
| 507 | 2    | 4    | 3     |
| 509 | 2    | 4    | 3     |
| 508 | 4    | 3    | 1     |
| 501 | 5    | 1    | 2     |
| 506 | 5    | 1    | 2     |
| 504 | 5    | 6    | 1     |
+-----+------+------+-------+

由于MySQL不支持窗口函数，您必须在子查询中进行计数，然后将此子查询加入表：

SELECT t.id, t.serv, t.spec, c.cnt
FROM
  table1 t INNER JOIN (
    SELECT serv, spec, COUNT(*) as cnt
    FROM table1
    GROUP BY serv, spec
  ) c ON t.serv = c.serv AND t.spec=c.spec