如何使用两列查询重复行 [英] How do I query duplicate rows using two columns

问题描述

我有类似的东西

column1 column2

a 1

a 1

a 2

b 5

b 5

b 4

我想要这样的东西

column1 column2

a 1

a 1

b 5

b 5

I have something like this
column1 column2
a 1
a 1
a 2
b 5
b 5
b 4
I want something like this
column1 column2
a 1
a 1
b 5
b 5

推荐答案

以下唯一地打印重复的行。

the following prints duplicated rows uniquely.

select col1, col2 from
(select ROW_NUMBER() over(PARTITION by col1, col2 order by col1 asc) as rn, col1, col2 from T1) a
where rn=2

row_number（）函数根据'over'子句为每行结果集分配行号。在此示例中，结果集由< col1，col2>分区。元组。每个< col1，col2> group在其组中按行编号，例如：

row_no col1 col2

1 a 1

2 a 1

1 a 2

1 b 5

2 b 5

1 b 4



如果你有另一个< a，1>在你的表中的元组，它的行号将是3.因此，如果我选择行号为2的行，这意味着我选择计数至少为2的行实例。新结果集在< col1方面是唯一的，COL2>元组，因为分区中只有一个实例，行号为2.





如果你想要所有重复的行，您可以将结果与原始表一起加入。

row_number() function assigns row number to each row of result set according to the 'over' clause. in this example, the result set is partitioned by <col1,col2> tuples. each <col1,col2> group is row-numbered within its group, such as:
row_no col1 col2
1 a 1
2 a 1
1 a 2
1 b 5
2 b 5
1 b 4

if you have another <a,1> tuple in your table, its row number would be 3. therefore, if I select the rows with the row number 2, that means I select row instances having count of at least 2. the new result set is unique in terms of <col1,col2> tuples, because there is only one instance within the partition with the row number 2.


if you want all of the duplicated rows, you can join the result with the original table.

select T1.col1, T1.col2
from T1 inner join
(select col1, col2 from
(select ROW_NUMBER() over(PARTITION by col1, col2 order by col1 asc) as rn, col1, col2 from T1) a
where rn=2) b
on T1.col1 = b.col1 and T1.col2 = b.col2

SELECT t1.column1, t1.column2 FROM tablename t1
WHERE
(
SELECT COUNT(t2.column2) FROM tablename t2 WHERE
    t1.column1=t2.column1 AND t1.column2=t2.column2
) > 1

试试这个

try this

select t2.* from
 (
select column1,column2 from ta group by column1,column2 having count(*)>1
   ) t1
join ta t2 on
t1.column1 = t2.column1 and
t1.column2 = t2.column2

现场演示：提示 [ ^ ]

这篇关于如何使用两列查询重复行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！