找到重复并给出排名 [英] find duplicates and give ranking

问题描述

如何将重复和不同的行从源表分离到单独的目标表？

源表：



COL1 COL2 COL3

a b c

x y z

a b c

r f u

a b c < br $>
v f r

v f r

目标表1：包含所有唯一行的表格

COL1 COL2 COL3

a b c

x y z

r f u

v f r



目标表2：包含所有重复行的表格



COL1 COL2 COL3

a b c

a b c

v f r



这些现在完成我想将目标表1链接到目标表2

How to segregate the duplicate and distinct rows from source table to separate target tables?
source table:

COL1 COL2 COL3
a b c
x y z
a b c
r f u
a b c
v f r
v f r
Target Table 1: Table containing all the unique rows
COL1 COL2 COL3
a b c
x y z
r f u
v f r

Target Table 2: Table containing all the duplicate rows

COL1 COL2 COL3
a b c
a b c
v f r

these is done now i want to link target table 1 to target table 2

推荐答案

在表格中找到重复项：



http://blog.sqlauthority.com/2007/07/11/sql-server-count-duplicate-records-row s / [ ^ ]





http://technet.microsoft.com/en-us/library/ms176102.aspx [ ^ ]

find Duplicates in table:

http://blog.sqlauthority.com/2007/07/11/sql-server-count-duplicate-records-rows/[^]


http://technet.microsoft.com/en-us/library/ms176102.aspx[^]

SELECT [YourColumnName]
FROM
(
    SELECT Row_Number() over (partion by YourColumnName order by YourColumnName) as counter, YourColumnName
    FROM [yourtable]
) as doubles
where doubles.counter > 1

您好，

根据您的需要使用以下查询让我知道它是否对你有所帮助。

Hi,
Use following query for your need and let me know if it helps you.

SELECT [YourColumnName],
RANK() OVER (
ORDER BY COUNT(*)) AS 'Rank'
FROM [YourTableName]
GROUP BY [YourColumnName]

谢谢，

Hitesh Varde

Thanks,
Hitesh Varde

这篇关于找到重复并给出排名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！