找到重复并给出排名 [英] 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屋!