请问sql 的去重时忽略ID但又必须输出ID如何做?

Question

distinct 好像必须放到前头?
大多数表都有自增ID吧.我目前的情况是想根据两个其它字段去重,但又要输出ID号.至于去重后保留哪个ID倒无所谓.用的是sql server 2008

至于为何有这样的需求:自己挖的坑呗,以为客户会遵守约定不发重复数据所以当初没加唯一性约束,现在大量数据存在无法加约束了...

Answer 1

SELECT * FROM TB t1
WHERE t1.ID IN (
    SELECT MAX(t1.id) FROM TB t2 GROUP BY t2.column1, t2.column2
)

Answer 2

可以用ROW_NUMBER
PARTITION BY 后的col1,col2列就是用于区分重复行的列

select * from 
(SELECT *，row_number(patition by col1,col2 order by ID) as seq FROM table1) as t
where t.seq=1

Answer 3

不用分区函数也可以吧

select t1.*
from table1 as t1 inner join (
    select col1, col2, min(id) as id
    from table1 as t2
    group by col1, col2
) t3 on t1.col1 = t3.col1 and t1.col2 = t3.col2 and t1.id > t3.id