根据表中某列去除重复的行

--根据表中某列(或者某些列)去除重复的行

--例如有表A,有两行相同的CardID,我们只要随机的某一行

--DROP TABLE #A
--DROP TABLE #B

CREATE TABLE #A
    (
      CardID VARCHAR(100) ,
      CardCode VARCHAR(100)
    ) ;

INSERT  INTO #A
        ( CardID, CardCode )
VALUES  ( 'abc', '123' )

INSERT  INTO #A
        ( CardID, CardCode )
VALUES  ( 'abc', '456' )

SELECT * FROM #A

--先建与A表机构相同的临时表B
CREATE TABLE #B
    (
      CardID VARCHAR(100) ,
      CardCode VARCHAR(100)
    ) ;

--插入去除重复的CardID列数据到表B
INSERT  INTO #B
        ( CardID )
SELECT DISTINCT CardID FROM #A

SELECT * FROM #B

UPDATE  b
SET     b.CardCode = a.CardCode 
FROM    #B b
        INNER JOIN #A a ON b.CardID = a.CardID

--得到表B为我们想要的表 
SELECT * FROM #B