有一个Table2表 字段为
id(int) 编号(varchar) 时间(datatime)
1 01 2009-01-10
2 01 2009-01-09
3 02 2009-02-05
4 03 2009-02-01
5 04 2009-04-05
6 04 2010-01-13
7 05 2010-01-06
8 05 2010-01-09
结果 查询 重复编号的最大时间(就是与当前最接近的) 不重复的也输出
id(int) 编号(varchar) 时间(datatime)
1 01 2009-01-10
2 01 2009-01-09
3 02 2009-02-05
4 03 2009-02-01
5 04 2009-04-05
6 04 2010-01-13
7 05 2010-01-06
8 05 2010-01-09
结果 查询 重复编号的最大时间(就是与当前最接近的) 不重复的也输出
where not exists(select 1 from table2 where 编号=t.编号 and id>t.id)
select 编号,max(时间) as 时间
from Table2
group by 编号
where not exists(select 1 from table2 where 编号=t.编号 and 时间>t.时间)
-- Author :SQL77(只为思齐老)
-- Date :2010-01-15 15:45:37
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:#TB
if object_id('tempdb.dbo.#TB') is not null drop table #TB
go
create table #TB([id] int,[编号] varchar(2),[时间] datetime)
insert #TB
select 1,'01','2009-01-10' union all
select 2,'01','2009-01-09' union all
select 3,'02','2009-02-05' union all
select 4,'03','2009-02-01' union all
select 5,'04','2009-04-05' union all
select 6,'04','2010-01-13' union all
select 7,'05','2010-01-06' union all
select 8,'05','2010-01-09'
--------------开始查询--------------------------SELECT * FROM #TB T WHERE 时间=(SELECT MAX(时间) FROM #TB WHERE 编号=T.编号) ORDER BY ID
----------------结果----------------------------
/* (所影响的行数为 8 行)id 编号 时间
----------- ---- ------------------------------------------------------
1 01 2009-01-10 00:00:00.000
3 02 2009-02-05 00:00:00.000
4 03 2009-02-01 00:00:00.000
6 04 2010-01-13 00:00:00.000
8 05 2010-01-09 00:00:00.000(所影响的行数为 5 行)
*/
SELECT 编号,MAX(时间) AS 时间 FROM Table2 GROUP BY 编号
select 编号, min(datediff(day, 时间, getdate())) as mindiff into temptb from Table2 group by 编号;
select * from Table2 where datediff(day, 时间, getdate()) = (select min(mindiff) from temptb);
select * from table_1 as b where 时间=select max(时间) from table_1 where 编号=b.编号