create table A(id varchar(10), [count] int, time datetime) insert into A values('01' , 10, '2009-01-01') insert into A values('01' , 6 , '2009-02-02') insert into A values('01' , 18, '2009-02-03') insert into A values('01' , 20, '2009-01-03') insert into A values('01' , 3 , '2009-02-05') create table B(id varchar(10) , time datetime) insert into B values('01' , '2009-02-01') go select b.id , [max] = (select max([count]) from a where a.id = b.id and a.time > b.time), [max_time] = (select time from a where [count] = (select max([count]) from a where a.id = b.id and a.time > b.time)), [min] = (select min([count]) from a where a.id = b.id and time between b.time and (select time from a where [count] = (select max([count]) from a where a.id = b.id and a.time > b.time))), [min_time] = (select time from a where [count] = (select min([count]) from a where a.id = b.id and time between b.time and (select time from a where [count] = (select max([count]) from a where a.id = b.id and a.time > b.time)))) from bdrop table A , B /* id max max_time min min_time ---------- ----------- ------------------------------------------------------ ----------- ------------------------------------------------------ 01 18 2009-02-03 00:00:00.000 6 2009-02-02 00:00:00.000(所影响的行数为 1 行) */
每条都要查一次aaa表, not exists( select 1 from aaa t4 where t3.code = t4.code and t4.tclose >t3.tclose and t4.tdate >= convert(varchar(10),t0.fistAttentionTime,112) ) 直接取最大值 不就好了,其他项放在group by从句了
insert into A values('01' , 10, '2009-01-01')
insert into A values('01' , 6 , '2009-02-02')
insert into A values('01' , 18, '2009-02-03')
insert into A values('01' , 20, '2009-01-03')
insert into A values('01' , 3 , '2009-02-05')
create table B(id varchar(10) , time datetime)
insert into B values('01' , '2009-02-01')
go
select
b.id ,
[max] = (select max([count]) from a where a.id = b.id and a.time > b.time),
[max_time] = (select time from a where [count] = (select max([count]) from a where a.id = b.id and a.time > b.time)),
[min] = (select min([count]) from a where a.id = b.id and time between b.time and (select time from a where [count] = (select max([count]) from a where a.id = b.id and a.time > b.time))),
[min_time] = (select time from a where [count] = (select min([count]) from a where a.id = b.id and time between b.time and (select time from a where [count] = (select max([count]) from a where a.id = b.id and a.time > b.time))))
from bdrop table A , B /*
id max max_time min min_time
---------- ----------- ------------------------------------------------------ ----------- ------------------------------------------------------
01 18 2009-02-03 00:00:00.000 6 2009-02-02 00:00:00.000(所影响的行数为 1 行)
*/
not exists(
select 1 from aaa t4
where t3.code = t4.code and
t4.tclose >t3.tclose and
t4.tdate >= convert(varchar(10),t0.fistAttentionTime,112)
)
直接取最大值 不就好了,其他项放在group by从句了