常用SQL语句

去掉重复语句

 

-- This query is editable, but generally slower 

Select A1.*, A1.rowid

from SHCNC.SELECTEDTERMINATIONPOINT A1 

where exists (Select 'x' from SHCNC.SELECTEDTERMINATIONPOINT A2 

where A1.MEID = A2.MEID

and   A1.GROUPID = A2.GROUPID

and   A1.PHYSICALTERMINATIONPOINTID = A2.PHYSICALTERMINATIONPOINTID

and   A1.USERNAME = A2.USERNAME

and A1.ROWID <> A2.ROWID)

-- To delete all but one of each duplicate row, 

-- change the first line to 'Delete' 

-- and change the '<>' to '>' (keeps first duplicate) 

--                     or '<' (keeps last duplicate) 

 

;

 

delete

from SHCNC.SELECTEDTERMINATIONPOINT A1 

where exists (Select 'x' from SHCNC.SELECTEDTERMINATIONPOINT A2 

where A1.MEID = A2.MEID

and   A1.GROUPID = A2.GROUPID

and   A1.PHYSICALTERMINATIONPOINTID = A2.PHYSICALTERMINATIONPOINTID

and   A1.USERNAME = A2.USERNAME

and A1.ROWID > A2.ROWID)