挑战高手! 非常难的SQL题.
通过 BEGINDATE 得到,满足时间范围内最大值,和最小值.
如:x1 8点开始 -> 扩展分钟是60,即8点->9点内的最大值,和最小值.
x2 这个有点复杂,扩展分钟是300分钟,超过了当天.
测试数据如下.
-----------------------------
declare @a table(
empno varchar(20),
begin_date datetime,
begin_time char(5),
add_miniute int,
min_val varchar(50),
max_val varchar(50)
)
declare @b table(
empno varchar(20),
val_date datetime,
val_time char(5)
)
insert into @a
select 'x1','2013-05-01','08:00',60,'',''
union
select 'x2','2013-05-01','23:00',300,'',''
select * from @a
insert into @b
select 'x1','2013-05-01','07:00'
union
select 'x1','2013-05-01','08:00'
union
select 'x1','2013-05-01','09:00'
union
select 'x1','2013-05-01','10:00'
union
select 'x2','2013-05-01','23:15'
union
select 'x2','2013-05-02','01:00'
union
select 'x2','2013-05-02','02:15'
----期望得到以下结果.-------------------------------------------
--empnobegin_datebegin_timeadd_miniutemin_val max_val
--x12013-05-01 00:00:00.00008:00602013-05-01 08:00 2013-05-01 09:00
--x22013-05-01 00:00:00.00023:003002013-05-01 23:15 2013-05-02 02:15
select * from @a
select * from @b
[解决办法]
declare @a table(
empno varchar(20),
begin_date datetime,
begin_time char(5),
add_miniute int,
min_val varchar(50),
max_val varchar(50)
)
declare @b table(
empno varchar(20),
val_date datetime,
val_time char(5)
)
insert into @a
select 'x1','2013-05-01','08:00',60,'',''
union
select 'x2','2013-05-01','23:00',300,'',''
insert into @b
select 'x1','2013-05-01','07:00'
union
select 'x1','2013-05-01','08:00'
union
select 'x1','2013-05-01','09:00'
union
select 'x1','2013-05-01','10:00'
union
select 'x2','2013-05-01','23:15'
union
select 'x2','2013-05-02','01:00'
union
select 'x2','2013-05-02','02:15'
select a.empno,a.begin_date,a.begin_time,add_miniute,min(convert(nvarchar(11),b.val_date,120) + b.val_time) as min_val,
max(convert(nvarchar(11),b.val_date,120) + b.val_time) as max_val
from @a a,@b b
where a.empno = b.empno
and convert(datetime,convert(nvarchar(11),b.val_date,120) + b.val_time)
between convert(datetime,convert(nvarchar(11),a.begin_date,120) +a.begin_time)
and dateadd(mi,add_miniute,convert(datetime,convert(nvarchar(11),a.begin_date,120) +a.begin_time))
group by a.empno,a.begin_date,a.begin_time,a.add_miniute
/*
empno,begin_date,begin_time,add_miniute,min_val,max_val
x1,2013-05-01 00:00:00.000,08:00,60,2013-05-01 08:00,2013-05-01 09:00
x2,2013-05-01 00:00:00.000,23:00,300,2013-05-01 23:15,2013-05-02 02:15
with
tb1 as
(
select t1.empno,
convert(datetime,convert(varchar(10),t1.begin_date,120) + ' ' + t1.begin_time + ':00',120) as begin_time,
DATEADD(MINUTE,t1.add_miniute,convert(datetime,convert(varchar(10),t1.begin_date,120) + ' ' + t1.begin_time + ':00',120)) as end_time
from t1
),
tb2 as
(
select t2.empno,
convert(datetime,convert(varchar(10),t2.val_date,120) + ' ' + t2.val_time + ':00',120) as val_time
from t2
)
select t1.empno,t1.begin_date,t1.begin_time,t1.add_miniute,t.min_val,t.max_val
from
t1 inner join
(
select tb2.empno,
MIN(tb2.val_time) as min_val,
MAX(tb2.val_time) as max_val
from tb1
inner join tb2 on tb1.empno = tb2.empno
where tb2.val_time between tb1.begin_time and tb1.end_time
group by tb2.empno
) t
on t1.empno = t.empno
