Warm tip: This article is reproduced from serverfault.com, please click

sql-获取列中重复项的总数

(sql - Get total count of duplicates in column)

发布于 2020-12-02 13:29:09

我需要一个查询来计算表中重复项的总数,有什么办法可以做到这一点?

如果我有一个这样的表:

+------------+----------+
| item_name  |quantity  |
+------------+----------+
| Calculator |       89 |
| Notebooks  |       40 |
| Pencil     |       40 |
| Pens       |       32 |
| Shirts     |       29 |
| Shoes      |       29 |
| Trousers   |       29 |
+------------+----------+

我不能使用SELECT COUNT(quantity),因为它返回2.( 40 | 29

如何退回5?40 | 40 | 29 | 29 | 29

Questioner
Still_learning
Viewed
0
Tim Biegeleisen 2020-12-02 21:34:15

使用分析功能:

WITH cte AS (
    SELECT *, COUNT(*) OVER (PARTITION BY quantity) cnt
    FROM yourTable
)

SELECT COUNT(*)
FROM cte
WHERE cnt > 1;