如果编号相同则选日期最大的记录如下:
编号 日期 字段1 字段2 ...
1000 2007-01-01 1 a ...
0099 2007-02-03 2 b ...
1000 2007-02-08 2 c ...
1001 2007-02-02 0 c ...
0099 2007-03-18 0 l ...
... ... . . ...得到:
编号 日期 字段1 字段2 ...
0099 2007-03-18 0 l ...
1000 2007-02-08 2 c ...
1001 2007-02-02 0 c ...
编号 日期 字段1 字段2 ...
1000 2007-01-01 1 a ...
0099 2007-02-03 2 b ...
1000 2007-02-08 2 c ...
1001 2007-02-02 0 c ...
0099 2007-03-18 0 l ...
... ... . . ...得到:
编号 日期 字段1 字段2 ...
0099 2007-03-18 0 l ...
1000 2007-02-08 2 c ...
1001 2007-02-02 0 c ...
Select A.* From TableName A Where 日期 = (Select Max(日期) From TableName Where 编号 = A.编号)
Order By A.编号
--方法二:
Select A.* From TableName A Where Not Exists (Select 日期 From TableName Where 编号 = A.编号 And 日期 > A.日期)
Order By A.编号--方法三:
Select A.* From TableName A Inner Join (Select 编号, Max(日期) As 日期 From TableName Group By 编号) B
On A.编号 = B.编号 And A.日期 = B.日期
Order By A.编号
select * from t where 日期 IN (select max(日期) from t gruop by 编号)
-------
這種寫法不夠穩妥,日期有可能會重復.
dt datetime,
col1 varchar(20),
col2 varchar(20))insert into tt select '1000','2007-01-01','1','a'
union all select '0099','2007-02-03','2','b'
union all select '1000','2007-02-08','2','c'
union all select '1001','2007-02-02','0','c'
union all select '0099','2007-03-18','0','1'--select * from ttselect id
,dt
,col1
,col2
from tt a
where dt = ( select top 1 dt
from tt b
where b.id = a.id
order by b.dt desc
)
order by iddrop table tt(所影响的行数为 5 行)id dt col1 col2
-------------------- ------------------------------------------------------ -------------------- --------------------
0099 2007-03-18 00:00:00.000 0 1
1000 2007-02-08 00:00:00.000 2 c
1001 2007-02-02 00:00:00.000 0 c(所影响的行数为 3 行)
所以我觉得先建个视图,create view view_1 as select * from tt order by dt.然后再在视图上通过程序来实现唯一性。