SQL Server2008 删除重复记录只剩一条（无Uid）

INSERT INTO 表1   SELECT  *  FROM  视图1

 CREATE TABLE  ##TMP01                                    ---创建全局临时表
(
    [AGUID]            [nvarchar](50)        NULL,
    [BGUID]            [nvarchar](50)        NULL,
    [DeviceID]         [nvarchar](50)        NULL,
    [EmpID]             [nvarchar](50)        NULL,  
    [EmpCardID]     [nvarchar](50)        NULL,
);

 TRUNCATE TABLE ##TMP01    

INSERT INTO    ##TMP01       
SELECT DISTINCT  *   FROM  表1
WHERE   AGUID in   (SELECT AGUID  FROM  表1  GROUP BY AGUID HAVING COUNT(*)>1 )

DELETE 表1   WHERE  AGUID IN(SELECT AGUID FROM  表1  GROUP BY AGUID HAVING COUNT(1)>1)

INSERT INTO  表1   (AGUID,BGUID,DeviceID,EmpID,EmpCardID) SELECT AGUID,BGUID,DeviceID,EmpID,EmpCardID FROM  ##TMP01

INSERT INTO 总表  (AGUID,DeviceID,EmpID,EmpCardID)
SELECT AGUID,DeviceID,EmpID,EmpCardID FROM  表1  WHERE AGUID not in (SELECT AGUID FROM  总表  ) ORDER BY DeviceID,EmpCardID

DROP TABLE ##TMP01
 
TRUNCATE TABLE   表1 

 

 

https://blog.csdn.net/n950814abc/article/details/82284838