重复记录
T
======
C1 INT IDENTITY(1,1)
C2 VARCHAR(20)
C3 INT
C4 DATETIME

除了C1,其它都有重复
C1 C2 C3 C4
1 A 3 20070101
2 A 3 20070202
3 B 3 20070101
4 A 2 20070101
5 A 3 20070202
用DISTINCT可否过滤重复的行(因为C1是自增列,故不计其中)
结果要:
C1 C2 C3 C4
1 A 3 20070101
2 A 3 20070202
3 B 3 20070101
4 A 2 20070101
实际上的列很多,不用group by
如何实现,谢谢



------解决方案--------------------

SQL code

--方法一：
select a.* from t a where c1 = (select min(c1) from tb where c2=a.c2 and c3=a.c3 and c4=a.c4)
--方法二：
select a.* from t a,
(select min(c1) c1 , c2,c3,c4 from t group by c2,c3,c4) b
where a.c1=b.c1 and a.c2=b.c2 and a.c3=b.c3 and a.c4=b.c4

------解决方案--------------------
select a.* from T a where not exists(select 1 from T where C2=a.C2 and C3=a.C3 and C1<a.C1)