如何在几列之间找到重复计数? [英] How to find duplicate count among several columns?
问题描述
这里是一个模仿我的情景的范例表格:
COL_1 COL_2 COL_3 COL_4 LAST_COL
APX NY 10
APX NY 11
APY NY 12
APY NY 13
APX NY 14
BQX NY 15
BQY NY 16
BQY CA 17
BQY CA 18
LAST_COL是主键,因此每次都会不同。 p>
我想忽略LAST_COL并收集与4列其余部分相关的统计信息。
基本上,我的表中有数百万行,我想知道 COL_1,COL_2,COL_3和COL_4
中的行数最多。
因此,我想要一个查询,可以输出所有唯一的行及其出现次数。
COL_1 COL_2 COL_3 COL_4 TOTAL
APX NY 3
APY NY 2
BQX NY 1
BQY NY 1
BQY CA 2
感谢任何人帮助我。
我使用MS SQL, SELECT COL_1,COL_2,COL_3,COL_4,COUNT(*)
FROM MyTable
GROUP BY COL_1,COL_2,COL_3,COL_4
如果你想要除去没有重复的行:
SELECT COL_1,COL_2,COL_3,COL_4,COUNT(*)
FROM MyTable
GROUP BY COL_1,COL_2,COL_3,COL_4
HAVING COUNT(*)& 1
Here is a sample table that mimics my scenario:
COL_1 COL_2 COL_3 COL_4 LAST_COL A P X NY 10 A P X NY 11 A P Y NY 12 A P Y NY 13 A P X NY 14 B Q X NY 15 B Q Y NY 16 B Q Y CA 17 B Q Y CA 18
The LAST_COL is a primary key so it will be different every time.
I want to ignore LAST_COL and gather some statistics related to the rest of the 4 columns.
Basically, I have millions of rows in my table, and I want to know which set of
COL_1, COL_2, COL_3 and COL_4
are having most number of rows.So, I want a query which can output me all the unique rows with their count of occurrences.
COL_1 COL_2 COL_3 COL_4 TOTAL A P X NY 3 A P Y NY 2 B Q X NY 1 B Q Y NY 1 B Q Y CA 2
Thanks to anyone who helps me with this.
*I am using MS SQL, if that would make any difference.
解决方案SELECT COL_1, COL_2, COL_3, COL_4, COUNT(*) FROM MyTable GROUP BY COL_1, COL_2, COL_3, COL_4
If you ever want to weed out rows that don't have a duplicate:
SELECT COL_1, COL_2, COL_3, COL_4, COUNT(*) FROM MyTable GROUP BY COL_1, COL_2, COL_3, COL_4 HAVING COUNT(*) > 1
这篇关于如何在几列之间找到重复计数?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!