如何在几列之间找到重复计数? [英] How to find duplicate count among several columns?

查看:88
本文介绍了如何在几列之间找到重复计数?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

这里是一个模仿我的情景的范例表格:

  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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆