日期最大值判断的问题？ 在线等
有表如下，要求将开始在07-01-26到07-02-25之间的数据取出来。每个业务员的同一产品只取开始日期最大的一条记录。
开始日期 结束日期 业务员 产品 数量
2006-12-26 2007-01-25 A A 1000
2006-12-26 2007-01-25 A B 1000
2007-01-12 2007-01-25 B D 1000
2007-01-12 2007-01-25 C W 1000
2007-01-26 2007-02-25 D F 1000
2007-01-26 2007-02-25 A W 1000
2007-02-12 2007-02-25 A B 1000
2007-02-12 2007-02-25 B L 1000
2007-02-26 2007-03-25 E T 1000
2007-02-26 2007-03-25 F N 1000
结果应该如下：
2007-02-12 2007-02-25 A B 1000
2007-02-12 2007-02-25 B L 1000
create table dtime
(
开始日期 datetime ,
结束日期 datetime,
A varchar(10),
B varchar(10),
C int
)
delete dtime
insert into dtime
select '2006-12-26 ' , '2007-01-25 ' , 'A ' , 'A ' , 1000
union all
select '2006-12-26 ' , '2007-01-25 ' , 'A ' , 'B ' , 1000
union all
select '2007-01-12 ' , '2007-01-25 ' , 'B ' , 'D ' , 1000
union all
select '2007-01-12 ' , '2007-01-25 ' , 'C ' , 'W ' , 1000
union all
select '2007-01-26 ' , '2007-02-25 ' , 'D ' , 'F ' , 1000
union all
select '2007-01-26 ' , '2007-02-25 ' , 'A ' , 'W ' , 1000
union all
select '2007-02-12 ' , '2007-02-25 ' , 'A ' , 'B ' , 1000
union all
select '2007-02-12 ' , '2007-02-25 ' , 'B ' , 'L ' , 1000
union all
select '2007-02-26 ' , '2007-03-25 ' , ' E ' , 'T ', 1000
union all
select '2007-02-26 ' , '2007-03-25 ' , 'F ' , 'N ' , 1000
[解决办法]
select 开始日期 , 结束日期 ,a,b,c from dtime a
where 开始日期> =Convert(datetime, '20 '+ '07-01-26 ') and 结束日期 <= Convert(datetime, '20 '+ '07-02-25 ')
and not exists (select 1 from dtime b where 开始日期> =Convert(datetime, '20 '+ '07-01-26 ') and 结束日期 <= Convert(datetime, '20 '+ '07-02-25 ')
and a.开始日期 <b.开始日期)
--result
2007-02-12 00:00:00.000 2007-02-25 00:00:00.000 A B 1000
2007-02-12 00:00:00.000 2007-02-25 00:00:00.000 B L 1000
