A id count time 01 10 2009-01-01 01 6 2009-02-02 01 18 2009-02-03 01 20 2009-01-03 01 3 2009-02-05 B id time 01 2009-02-01最终的结果是 id max max_time min min_time 01 18 2009-02-03 6 2009-02-02
所取得的最小值是最大值对应的A.time和B.time 之间的
select slttb.id ,max_count,x.time as maxtime,min_count,y.time as mintime from (select t.id,t.max_count,min(m.count) min_count from (select a.id , max(a.[count]) max_count from testa a , testb b where a.id = b.id and a.time > b.time group by a.id ) t,testa c,testa m,testb n where t.id=c.id and t.max_count=c.count and t.id=m.id and t.id=n.id and m.time<=c.time and m.time>=n.time group by t.id,t.max_count) slttb,testa x,testa y where slttb.id=x.id and slttb.max_count=x.count and slttb.id=y.id and slttb.min_count=y.count
testa就是你的a表,testb就是你的b表哈
还有,你的b表中同一个id的数据应该是唯一的。
if object_id('[ta]') is not null drop table [ta] go create table [ta]([id] varchar(2),[count] int,[time] datetime) insert [ta] select '01',10,'2009-01-01' union all select '01',6,'2009-02-02' union all select '01',18,'2009-02-03' union all select '01',20,'2009-01-03' union all select '01',3,'2009-02-05' if object_id('[tb]') is not null drop table [tb] go create table [tb]([id] varchar(2),[time] datetime) insert [tb] select '01','2009-02-01'select * from [ta] select * from [tb]with szx as ( select top 1 a.id,a.count as max,a.time as max_time,b.time as min_time from ta a join tb b on a.id=b.id and a.time>=b.time order by a.count desc ) select id,max,max_time ,(select min(count) from ta where id=t.id and time between t.min_time and t.max_time) as min ,min_time from szx t --测试结果: /* id max max_time min min_time ---- ----------- ----------------------- ----------- ----------------------- 01 18 2009-02-03 00:00:00.000 6 2009-02-01 00:00:00.000(1 行受影响) */
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 a.time > b.time), [min_time] = (select time from a where [count] = (select min([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 3 2009-02-05 00:00:00.000(所影响的行数为 1 行) */
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 行) */
id count time
01 10 2009-01-01
01 6 2009-02-02
01 18 2009-02-03
01 20 2009-01-03
01 3 2009-02-05
B
id time
01 2009-02-01最终的结果是
id max max_time min min_time
01 18 2009-02-03 6 2009-02-02
select slttb.id ,max_count,x.time as maxtime,min_count,y.time as mintime
from
(select t.id,t.max_count,min(m.count) min_count
from
(select a.id , max(a.[count]) max_count
from testa a , testb b
where a.id = b.id and a.time > b.time
group by a.id ) t,testa c,testa m,testb n
where t.id=c.id and t.max_count=c.count
and t.id=m.id and t.id=n.id
and m.time<=c.time and m.time>=n.time
group by t.id,t.max_count) slttb,testa x,testa y
where slttb.id=x.id and slttb.max_count=x.count
and slttb.id=y.id and slttb.min_count=y.count
go
create table [ta]([id] varchar(2),[count] int,[time] datetime)
insert [ta]
select '01',10,'2009-01-01' union all
select '01',6,'2009-02-02' union all
select '01',18,'2009-02-03' union all
select '01',20,'2009-01-03' union all
select '01',3,'2009-02-05'
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] varchar(2),[time] datetime)
insert [tb]
select '01','2009-02-01'select * from [ta]
select * from [tb]with szx as
(
select top 1 a.id,a.count as max,a.time as max_time,b.time as min_time
from ta a join tb b on a.id=b.id and a.time>=b.time
order by a.count desc
)
select id,max,max_time
,(select min(count) from ta where id=t.id and time between t.min_time and t.max_time) as min
,min_time
from szx t
--测试结果:
/*
id max max_time min min_time
---- ----------- ----------------------- ----------- -----------------------
01 18 2009-02-03 00:00:00.000 6 2009-02-01 00:00:00.000(1 行受影响)
*/
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 a.time > b.time),
[min_time] = (select time from a where [count] = (select min([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 3 2009-02-05 00:00:00.000(所影响的行数为 1 行)
*/
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 行)
*/