SQL 计算每个值的所有重复项
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6257580/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Count all duplicates of each value
提问by Filip Haglund
I would like a SQL query for MS Jet 4.0 (MSSql?) to get a count of all the duplicates of each number in a database.
我想要 MS Jet 4.0 (MSSql?) 的 SQL 查询，以获取数据库中每个数字的所有重复项的计数。
The fields are: id(autonum), number(text)
字段是：id(autonum), number(text)
I have a database with a lot of numbers.
我有一个包含很多数字的数据库。
Each number should be returned in numerical order, without duplicates, with a count of all duplicates.
每个数字应按数字顺序返回，没有重复，并计算所有重复。
Number-fields containing 1, 2, 2, 3, 1, 4, 2 should return:
包含 1, 2, 2, 3, 1, 4, 2 的数字字段应返回：
1, 2
2, 3
3, 1
4, 1
回答by cetver
SELECT col,
COUNT(dupe_col) AS dupe_cnt
FROM TABLE
GROUP BY col
HAVING COUNT(dupe_col) > 1
ORDER BY COUNT(dupe_col) DESC
回答by Joe Stefanelli
SELECT number, COUNT(*)
FROM YourTable
GROUP BY number
ORDER BY number
回答by dpmattingly
You'd want the COUNToperator.
你会想要COUNT运营商。
SELECT NUMBER, COUNT(*)
FROM T_NAME
GROUP BY NUMBER
ORDER BY NUMBER ASC
回答by Duncan Howe
This is quite simple.
这很简单。
Assuming the data is stored in a column called A in a table called T, you can use
假设数据存储在名为 T 的表中名为 A 的列中，您可以使用
select A, count(A) from T group by A
回答by Arjun Singh
If you want to check repetition more than 1 in descending order then implement below query.
如果要按降序检查重复次数超过 1 次，请执行以下查询。
SELECT duplicate_data,COUNT(duplicate_data) AS duplicate_data
FROM duplicate_data_table_name
GROUP BY duplicate_data
HAVING COUNT(duplicate_data) > 1
ORDER BY COUNT(duplicate_data) DESC
If want simple count query.
如果想要简单的计数查询。
SELECT COUNT(duplicate_data) AS duplicate_data
FROM duplicate_data_table_name
GROUP BY duplicate_data
ORDER BY COUNT(duplicate_data) DESC
