大家看看这个sql语句如何写?
数据如下,如何取得字段c(时间)最大的那批数据.
a b c
------------------------------------------
241684500.0000 2005-05-01 00:00:00
241685713.0000 2006-07-01 00:00:00
241693600.0000 2005-05-01 00:00:00
241694470.0000 2006-07-01 00:00:00
241703300.0000 2005-05-01 00:00:00
241704056.0000 2006-07-01 00:00:00
241713900.0000 2005-05-01 00:00:00
241715084.0000 2006-07-01 00:00:00
241734900.0000 2005-05-01 00:00:00
[解决办法]
declare @t table(a int,b decimal(10,4),c datetime)
insert @t
select 24168,4500.0000, '2005-05-01 00:00:00 ' union all
select 24168,5713.0000, '2006-07-01 00:00:00 ' union all
select 24169,3600.0000, '2005-05-01 00:00:00 ' union all
select 24169,4470.0000, '2006-07-01 00:00:00 ' union all
select 24170,3300.0000, '2005-05-01 00:00:00 ' union all
select 24170,4056.0000, '2006-07-01 00:00:00 ' union all
select 24171,3900.0000, '2005-05-01 00:00:00 ' union all
select 24171,5084.0000, '2006-07-01 00:00:00 ' union all
select 24173,4900.0000, '2005-05-01 00:00:00 '
select * from @t as t where not exists(select 1 from @t where a = t.a and c > t.c)
/*结果
a b c
----------- ------------ --------------------------
24168 5713.0000 2006-07-01 00:00:00.000
24169 4470.0000 2006-07-01 00:00:00.000
24170 4056.0000 2006-07-01 00:00:00.000
24171 5084.0000 2006-07-01 00:00:00.000
24173 4900.0000 2005-05-01 00:00:00.000
*/
[解决办法]
--方法一
Select * From 表 A Where Not Exists(Select c From 表 Where a = A.a And c > A.c)
--方法二
Select * From 表 A Where c = (Select Max(c) From 表 Where a = A.a)
--方法三
Select A.* From 表 A
Inner Join
(Select a, Max(c) As c From 表 Group By a) B
On A.a = B.a And A.c = B.c
