有详细记录表结构如下 Code DateTime Money
1 2010-06-29 15:15:00 100
1 2010-06-29 15:10:00 100
1 2010-06-29 14:10:00 100
1 2010-06-28 13:15:00 100
1 2010-06-28 12:10:00 100
1 2010-06-28 11:10:00 100 2 2010-06-29 15:15:00 100
2 2010-06-29 15:10:00 100
2 2010-06-29 14:10:00 100
2 2010-06-28 13:15:00 100
2 2010-06-28 12:10:00 100
2 2010-06-28 11:10:00 100
..................................
我要获取按日期最大 的所有详细记录 和前N天的最后一条记录如
1 2010-06-29 15:15:00 100
1 2010-06-29 15:10:00 100
1 2010-06-29 14:10:00 100
1 2010-06-28 13:15:00 100
2 2010-06-29 15:15:00 100
2 2010-06-29 15:10:00 100
2 2010-06-29 14:10:00 100
2 2010-06-28 13:15:00 100
谢谢了
1 2010-06-29 15:15:00 100
1 2010-06-29 15:10:00 100
1 2010-06-29 14:10:00 100
1 2010-06-28 13:15:00 100
1 2010-06-28 12:10:00 100
1 2010-06-28 11:10:00 100 2 2010-06-29 15:15:00 100
2 2010-06-29 15:10:00 100
2 2010-06-29 14:10:00 100
2 2010-06-28 13:15:00 100
2 2010-06-28 12:10:00 100
2 2010-06-28 11:10:00 100
..................................
我要获取按日期最大 的所有详细记录 和前N天的最后一条记录如
1 2010-06-29 15:15:00 100
1 2010-06-29 15:10:00 100
1 2010-06-29 14:10:00 100
1 2010-06-28 13:15:00 100
2 2010-06-29 15:15:00 100
2 2010-06-29 15:10:00 100
2 2010-06-29 14:10:00 100
2 2010-06-28 13:15:00 100
谢谢了
where datediff(day,[DateTime ],(select max([DateTime]) from tb where code=t.code))=0
or [datetime] = (select max([DateTime]) from tb b where b.code=t.code
and datediff(day,[DateTime ],(select max([DateTime]) from tb where code=t.code))<0)
where datediff(day,[DateTime ],(select max([DateTime]) from tb where code=t.code))=0
or [datetime] = (select max([DateTime]) from tb b where b.code=t.code
and datediff(day,b.[DateTime],(select max([DateTime]) from tb where code=t.code))<0)
--> 测试数据:[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([Code] int,[DateTime] datetime,[Money] int)
insert [TB]
select 1,'2010-06-29 15:15:00',100 union all
select 1,'2010-06-29 15:10:00',100 union all
select 1,'2010-06-29 14:10:00',100 union all
select 1,'2010-06-28 13:15:00',100 union all
select 1,'2010-06-28 12:10:00',100 union all
select 1,'2010-06-28 11:10:00',100 union all
select 2,'2010-06-29 15:15:00',100 union all
select 2,'2010-06-29 15:10:00',100 union all
select 2,'2010-06-29 14:10:00',100 union all
select 2,'2010-06-28 13:15:00',100 union all
select 2,'2010-06-28 12:10:00',100 union all
select 2,'2010-06-28 11:10:00',100select * from [TB] where datediff(dd,[DateTime],getdate())=0
union all
select * from TB t where not exists(select 1 from TB where Code=t.code and [DateTime]<t.DateTime)
and datediff(dd,[DateTime],getdate())>0
order by 1/*
Code DateTime Money
----------- ----------------------- -----------
1 2010-06-29 15:15:00.000 100
1 2010-06-29 15:10:00.000 100
1 2010-06-29 14:10:00.000 100
1 2010-06-28 11:10:00.000 100
2 2010-06-28 11:10:00.000 100
2 2010-06-29 15:15:00.000 100
2 2010-06-29 15:10:00.000 100
2 2010-06-29 14:10:00.000 100(8 行受影响)*/drop table TB
where convert(varchar(20),datetime,112) in (select max(convert(varchar(20),datetime,112)) from table group by code)
or datetime=(select max(datetime) from table where convert(varchar(20),datetime,112) not in (select max(convert(varchar(20),datetime,112)) from table group by code))
order by Code,DateTime desc
union all
select * from tb a where [datetime]=(select max(datetime) from tb where datediff(dd,[datetime],getdate())>0)
create table [TB]([Code] int,[[DateTime]] [DateTime],[Money] int)
insert [TB]
select 1,'2010-06-29 15:15:00',100 union all
select 1,'2010-06-29 15:10:00',100 union all
select 1,'2010-06-29 14:10:00',100 union all
select 1,'2010-06-28 13:15:00',100 union all
select 1,'2010-06-28 12:10:00',100 union all
select 1,'2010-06-28 11:10:00',100 union all
select 2,'2010-06-29 15:15:00',100 union all
select 2,'2010-06-29 15:10:00',100 union all
select 2,'2010-06-29 14:10:00',100 union all
select 2,'2010-06-28 13:15:00',100 union all
select 2,'2010-06-28 12:10:00',100 union all
select 2,'2010-06-28 11:10:00',100select * from [TB]
where convert(varchar(20),[DateTime],112) in (select max(convert(varchar(20),[DateTime],112)) from [TB] group by [Code])
or [DateTime]=(select max([DateTime]) from [TB] where convert(varchar(20),[DateTime],112) not in (select max(convert(varchar(20),[DateTime],112)) from [TB] group by [Code]))
order by Code,[DateTime] desc/*
Code DateTime Money
1 2010-06-29 15:15:00.000 100
1 2010-06-29 15:10:00.000 100
1 2010-06-29 14:10:00.000 100
1 2010-06-28 13:15:00.000 100
2 2010-06-29 15:15:00.000 100
2 2010-06-29 15:10:00.000 100
2 2010-06-29 14:10:00.000 100
2 2010-06-28 13:15:00.000 100
*/
select * from [TB] where convert(varchar(20),[DateTime],112) in (select max(convert(varchar(20),[DateTime],112)) from [TB] group by [Code])or [DateTime] in (select max([DateTime]) from [TB] where convert(varchar(20),[DateTime],112) not in (select max(convert(varchar(20),[DateTime],112)) from [TB] group by [Code]) group by convert(varchar(20),[DateTime],112))order by Code,[DateTime] desc