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
如何实现,谢谢
======
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
如何实现,谢谢
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 c1=(select top 1 c1 from t where c2=a.c2 and c3=a.c3 and c4=a.c4)--取最小
select a.* from t a where c1=(select min(c1) from t where c2=a.c2 and c3=a.c3 and c4=a.c4)--取最大
select a.* from t a where c1=(select max(c1) from t where c2=a.c2 and c3=a.c3 and c4=a.c4)--随机取
select a.* from t a where c1=(select top 1 c1 from t where c2=a.c2 and c3=a.c3 and c4=a.c4 order by newid())
insert into t values(1, 'A', 3, '20070101')
insert into t values(2, 'A', 3, '20070202')
insert into t values(3, 'B', 3, '20070101')
insert into t values(4, 'A', 2, '20070101')
insert into t values(5, 'A', 3, '20070202')
go--按记录顺序取第一条
select a.* from t a where c1=(select top 1 c1 from t where c2=a.c2 and c3=a.c3 and c4=a.c4) order by a.c1
/*
C1 C2 C3 C4
----------- -------------------- ----------- ------------------------------------------------------
1 A 3 2007-01-01 00:00:00.000
2 A 3 2007-02-02 00:00:00.000
3 B 3 2007-01-01 00:00:00.000
4 A 2 2007-01-01 00:00:00.000(所影响的行数为 4 行)
*/--取最小
select a.* from t a where c1=(select min(c1) from t where c2=a.c2 and c3=a.c3 and c4=a.c4) order by a.c1
/*
C1 C2 C3 C4
----------- -------------------- ----------- ------------------------------------------------------
1 A 3 2007-01-01 00:00:00.000
2 A 3 2007-02-02 00:00:00.000
3 B 3 2007-01-01 00:00:00.000
4 A 2 2007-01-01 00:00:00.000(所影响的行数为 4 行)
*/--取最大
select a.* from t a where c1=(select max(c1) from t where c2=a.c2 and c3=a.c3 and c4=a.c4) order by a.c1
/*
C1 C2 C3 C4
----------- -------------------- ----------- ------------------------------------------------------
1 A 3 2007-01-01 00:00:00.000
3 B 3 2007-01-01 00:00:00.000
4 A 2 2007-01-01 00:00:00.000
5 A 3 2007-02-02 00:00:00.000(所影响的行数为 4 行)
*/--随机取
select a.* from t a where c1=(select top 1 c1 from t where c2=a.c2 and c3=a.c3 and c4=a.c4 order by newid()) order by a.c1
/*
C1 C2 C3 C4
----------- -------------------- ----------- ------------------------------------------------------
1 A 3 2007-01-01 00:00:00.000
2 A 3 2007-02-02 00:00:00.000
3 B 3 2007-01-01 00:00:00.000
4 A 2 2007-01-01 00:00:00.000(所影响的行数为 4 行)
*/
drop table t
再次感谢!