一个表中数据内容如下:ID SN RecordTime
---------------------------------------------------
100000018 31 2011-10-28 16:52:00.307
100000009 31 2011-10-28 16:51:51.193
100000000 31 2011-10-28 16:51:42.267
100000019 32 2011-10-28 16:52:01.327
100000010 32 2011-10-28 16:51:52.207
100000001 32 2011-10-28 16:51:43.070
100000020 33 2011-10-28 16:52:02.337
100000011 33 2011-10-28 16:51:53.220
100000002 33 2011-10-28 16:51:44.090
100000021 34 2011-10-28 16:52:03.360
100000012 34 2011-10-28 16:51:54.233
100000003 34 2011-10-28 16:51:45.107
100000022 35 2011-10-28 16:52:04.370
100000013 35 2011-10-28 16:51:55.240
100000004 35 2011-10-28 16:51:46.123
100000014 36 2011-10-28 16:51:56.250
100000005 36 2011-10-28 16:51:47.137其中ID为主键只保留每组SN相同的项RecordTime列最大的一条
查询结果为:100000018 31 2011-10-28 16:52:00.307
100000019 32 2011-10-28 16:52:01.327
100000020 33 2011-10-28 16:52:02.337
100000021 34 2011-10-28 16:52:03.360
100000022 35 2011-10-28 16:52:04.370
100000014 36 2011-10-28 16:51:56.250要求只使用一条SQL语句想了好长时间没搞定,请大家帮下忙
---------------------------------------------------
100000018 31 2011-10-28 16:52:00.307
100000009 31 2011-10-28 16:51:51.193
100000000 31 2011-10-28 16:51:42.267
100000019 32 2011-10-28 16:52:01.327
100000010 32 2011-10-28 16:51:52.207
100000001 32 2011-10-28 16:51:43.070
100000020 33 2011-10-28 16:52:02.337
100000011 33 2011-10-28 16:51:53.220
100000002 33 2011-10-28 16:51:44.090
100000021 34 2011-10-28 16:52:03.360
100000012 34 2011-10-28 16:51:54.233
100000003 34 2011-10-28 16:51:45.107
100000022 35 2011-10-28 16:52:04.370
100000013 35 2011-10-28 16:51:55.240
100000004 35 2011-10-28 16:51:46.123
100000014 36 2011-10-28 16:51:56.250
100000005 36 2011-10-28 16:51:47.137其中ID为主键只保留每组SN相同的项RecordTime列最大的一条
查询结果为:100000018 31 2011-10-28 16:52:00.307
100000019 32 2011-10-28 16:52:01.327
100000020 33 2011-10-28 16:52:02.337
100000021 34 2011-10-28 16:52:03.360
100000022 35 2011-10-28 16:52:04.370
100000014 36 2011-10-28 16:51:56.250要求只使用一条SQL语句想了好长时间没搞定,请大家帮下忙
from (select *,row_number()over(partition by SN order by RecordTime desc) as a from table1) t
where row=1
declare @T table([ID] int,[SN] int,[RecordTime] Datetime)
Insert @T
select 100000018,31,'2011-10-28 16:52:00.307' union all
select 100000009,31,'2011-10-28 16:51:51.193' union all
select 100000000,31,'2011-10-28 16:51:42.267' union all
select 100000019,32,'2011-10-28 16:52:01.327' union all
select 100000010,32,'2011-10-28 16:51:52.207' union all
select 100000001,32,'2011-10-28 16:51:43.070' union all
select 100000020,33,'2011-10-28 16:52:02.337' union all
select 100000011,33,'2011-10-28 16:51:53.220' union all
select 100000002,33,'2011-10-28 16:51:44.090' union all
select 100000021,34,'2011-10-28 16:52:03.360' union all
select 100000012,34,'2011-10-28 16:51:54.233' union all
select 100000003,34,'2011-10-28 16:51:45.107' union all
select 100000022,35,'2011-10-28 16:52:04.370' union all
select 100000013,35,'2011-10-28 16:51:55.240' union all
select 100000004,35,'2011-10-28 16:51:46.123' union all
select 100000014,36,'2011-10-28 16:51:56.250' union all
select 100000005,36,'2011-10-28 16:51:47.137'
Select * from @T as a where not exists(select 1 from @T where SN=a.SN and [RecordTime]>a.[RecordTime])
/*
ID SN RecordTime
100000018 31 2011-10-28 16:52:00.307
100000019 32 2011-10-28 16:52:01.327
100000020 33 2011-10-28 16:52:02.337
100000021 34 2011-10-28 16:52:03.360
100000022 35 2011-10-28 16:52:04.370
100000014 36 2011-10-28 16:51:56.250
*/
declare @T table([ID] int,[SN] int,[RecordTime] Datetime)
Insert @T
select 100000018,31,'2011-10-28 16:52:00.307' union all
select 100000009,31,'2011-10-28 16:51:51.193' union all
select 100000000,31,'2011-10-28 16:51:42.267' union all
select 100000019,32,'2011-10-28 16:52:01.327' union all
select 100000010,32,'2011-10-28 16:51:52.207' union all
select 100000001,32,'2011-10-28 16:51:43.070' union all
select 100000020,33,'2011-10-28 16:52:02.337' union all
select 100000011,33,'2011-10-28 16:51:53.220' union all
select 100000002,33,'2011-10-28 16:51:44.090' union all
select 100000021,34,'2011-10-28 16:52:03.360' union all
select 100000012,34,'2011-10-28 16:51:54.233' union all
select 100000003,34,'2011-10-28 16:51:45.107' union all
select 100000022,35,'2011-10-28 16:52:04.370' union all
select 100000013,35,'2011-10-28 16:51:55.240' union all
select 100000004,35,'2011-10-28 16:51:46.123' union all
select 100000014,36,'2011-10-28 16:51:56.250' union all
select 100000005,36,'2011-10-28 16:51:47.137'
Select * from @T as a where [RecordTime]=(select MAX([RecordTime]) from @T where SN=a.SN)
/*
ID SN RecordTime
100000018 31 2011-10-28 16:52:00.307
100000019 32 2011-10-28 16:52:01.327
100000020 33 2011-10-28 16:52:02.337
100000021 34 2011-10-28 16:52:03.360
100000022 35 2011-10-28 16:52:04.370
100000014 36 2011-10-28 16:51:56.250
*/--SQL2005以上版本
select [ID],[SN],[RecordTime]
from (select *,row_number()over(partition by SN order by RecordTime desc) as row from @T) t
where row=1
错误提示:'RecordTime' 附近有语法错误。
select *
from (select *,row_number()over(partition by SN order by RecordTime desc) as a from temp) t
where row=1
错误提示:列名 'row' 无效。
select [ID],[SN],[RecordTime]
from (select *,row_number()over(partition by SN order by RecordTime desc) as row from @T) t
where row=1
select *
from (select *,row_number()over(partition by SN order by RecordTime desc) as a from temp) t
where row=1你这条SQL和大阪不一样..row_number() 函数的 列名row 你改成a了 肯定row无效了啊..
select *
from (select *,row_number()over(partition by SN order by RecordTime desc) as a from temp) t
where a=1
这样就对了
select max(ID) ID,sn,max(RecordTime) RecordTime from t gorup by sn