|
提示: 作者被禁止或删除, 无法发言
马上注册,结交更多好友,享用更多功能,让你轻松玩转社区。
您需要 登录 才可以下载或查看,没有帐号?FreeOZ用户注册
x
要求:表中有重复的Account_NO,要求按着 Priority的优先级大小查重。
我目前解决了有主键的表的按优先级查重,对于没有主键的表,怎么更新,我还没找到办法。大家看看怎么好?
Table: ID (主键,自增长字段), Account_NO (有重复记录字段), Priority (排序优先级字段), DuplicatedFlag (标记字段), Other Fields
显示重复记录
SELECT * FROM Table
WHERE ID IN
(SELECT ID
FROM
(SELECT Row_Number() OVER (Account_NO DESC, Priority DESC, ID DESC) AS RowID, * FROM Table) A,
(SELECT Row_Number() OVER (Account_NO DESC, Priority DESC, ID DESC) AS RowID, * FROM Table) B
WHERE A.RowID > B.RowID
AND A.Account_NO = B.Account_NO
AND A.Priority >= B.Priority)
更新重复记录标记
UPDATE Table
SET DuplicatedFlag = 1
WHERE ID IN
(SELECT ID
FROM
(SELECT Row_Number() OVER (Account_NO DESC, Priority DESC, ID DESC) AS RowID, * FROM Table) A,
(SELECT Row_Number() OVER (Account_NO DESC, Priority DESC, ID DESC) AS RowID, * FROM Table) B
WHERE A.RowID > B.RowID
AND A.Account_NO = B.Account_NO
AND A.Priority >= B.Priority)
删除重复记录
DELETE Table
WHERE ID IN
(SELECT ID
FROM
(SELECT Row_Number() OVER (Account_NO DESC, Priority DESC, ID DESC) AS RowID, * FROM Table) A,
(SELECT Row_Number() OVER (Account_NO DESC, Priority DESC, ID DESC) AS RowID, * FROM Table) B
WHERE A.RowID > B.RowID
AND A.Account_NO = B.Account_NO
AND A.Priority >= B.Priority) |
|