Go语言视频零基础入门到精通

叁点水

一个表中数据内容如下：

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语句

想了好长时间没搞定，请大家帮下忙

8jnuj3

SQL code select *
from (select *,row_number()over(partition by SN order by RecordTime desc) as a from table1) t
where row=1

水中月

SQL code --> --> (Roy)生成測試數據

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
*/

yweii

SQL code --> --> (Roy)生成測試數據

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

不要错过

Select * from temp as a where not exists(select 1 from temp where SN=a.SN and [RecordTime]>a.[RecordTime])

都掉了个括号

yweii

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
这样就对了

aopre8

少复制括号，上面都是测试过的

1楼把脚本在3楼有更正

彩虹糖梦

SQL code
select max(ID) ID,sn,max(RecordTime) RecordTime from t gorup by sn