表A
declare @a table (a_xm varchar(6),a_time varchar(5),a_log varchar(1),a_rs int,a_ys int)
insert into @a
select '张三','06:00','q',1,7 union all
select '张三','07:00','f',2,8 union all
select '张三','08:00','q',3,9 union all
select '张三','09:00','f',3,10 union all
select '李四','08:00','q',5,11 union all
select '李四','19:00','f',6,12 union all
select '王五','06:00','q',7,8 union all
select '王五','07:00','f',8,9 select a_xm,
max(case (px - 1)%2 when 0 then a_time end) a_time1,
max(case (px - 1)%2 when 1 then a_time end) a_time2,
sum(a_rs) a_rs,
sum(a_ys) a_ys
from
(
select * , px = (select count(1) from @a n where a_xm = m.a_xm and n.a_time < m.a_time) + 1 from @a m
) m
group by a_xm , (px - 1) / 2结果为:
a_xm a_time1 a_time2 a_rs a_ys
李四 08:00 19:00 11 23
王五 06:00 07:00 15 17
张三 06:00 07:00 3 15
张三 08:00 09:00 6 19---------------------------------------------------------------------------------------
如果表A变为:
declare @a table (a_xm varchar(6),a_time varchar(5),a_log varchar(1),a_rs int,a_ys int)
insert into @a
select '张三','06:00','f',1,7 union all
select '张三','07:00','q',2,8 union all
select '张三','08:00','f',3,9 union all
select '张三','09:00','q',3,10 union all
select '李四','08:00','q',5,11 union all
select '李四','19:00','f',6,12 union all
select '王五','06:00','q',7,8 union all
select '王五','07:00','f',8,9 希望得到结果为:
a_xm a_time1 a_time2 a_rs a_ys
李四 08:00 19:00 11 23
王五 06:00 07:00 15 17
张三 null 06:00 1 7
张三 07:00 08:00 5 17
张三 09:00 null 3 10
说明:a_time由小到大排列到a_time1、a_time2,
当a_log='q'时,先从a_time1排起;当a_log='f'时,先从a_time2排起。
有点复杂,不知道能看得懂不??
declare @a table (a_xm varchar(6),a_time varchar(5),a_log varchar(1),a_rs int,a_ys int)
insert into @a
select '张三','06:00','q',1,7 union all
select '张三','07:00','f',2,8 union all
select '张三','08:00','q',3,9 union all
select '张三','09:00','f',3,10 union all
select '李四','08:00','q',5,11 union all
select '李四','19:00','f',6,12 union all
select '王五','06:00','q',7,8 union all
select '王五','07:00','f',8,9 select a_xm,
max(case (px - 1)%2 when 0 then a_time end) a_time1,
max(case (px - 1)%2 when 1 then a_time end) a_time2,
sum(a_rs) a_rs,
sum(a_ys) a_ys
from
(
select * , px = (select count(1) from @a n where a_xm = m.a_xm and n.a_time < m.a_time) + 1 from @a m
) m
group by a_xm , (px - 1) / 2结果为:
a_xm a_time1 a_time2 a_rs a_ys
李四 08:00 19:00 11 23
王五 06:00 07:00 15 17
张三 06:00 07:00 3 15
张三 08:00 09:00 6 19---------------------------------------------------------------------------------------
如果表A变为:
declare @a table (a_xm varchar(6),a_time varchar(5),a_log varchar(1),a_rs int,a_ys int)
insert into @a
select '张三','06:00','f',1,7 union all
select '张三','07:00','q',2,8 union all
select '张三','08:00','f',3,9 union all
select '张三','09:00','q',3,10 union all
select '李四','08:00','q',5,11 union all
select '李四','19:00','f',6,12 union all
select '王五','06:00','q',7,8 union all
select '王五','07:00','f',8,9 希望得到结果为:
a_xm a_time1 a_time2 a_rs a_ys
李四 08:00 19:00 11 23
王五 06:00 07:00 15 17
张三 null 06:00 1 7
张三 07:00 08:00 5 17
张三 09:00 null 3 10
说明:a_time由小到大排列到a_time1、a_time2,
当a_log='q'时,先从a_time1排起;当a_log='f'时,先从a_time2排起。
有点复杂,不知道能看得懂不??
征求更简单写法-0-declare @a table (a_xm varchar(6),a_time varchar(5),a_log varchar(1),a_rs int,a_ys int)
insert into @a
select '张三','06:00','f',1,7 union all
select '张三','07:00','q',2,8 union all
select '张三','08:00','f',3,9 union all
select '张三','09:00','q',3,10 union all
select '李四','08:00','q',5,11 union all
select '李四','19:00','f',6,12 union all
select '王五','06:00','q',7,8 union all
select '王五','07:00','f',8,9
select tb.a_xm,a_time1,a_time2,(isnull(ta.a_rs,0)+isnull(ta1.a_rs,0))as a_rs
,(isnull(ta.a_ys,0)+isnull(ta1.a_ys,0)) as a_ys from
(
select a_xm, a_time as a_time1 ,
(
select min(t2.a_time) from @a as t2
where t2.a_xm=t1.a_xm and t2.a_time>t1.a_time
and a_log='f'
) as a_time2
from @a as t1
where a_log='q'
union
select a_xm,
(
select max(t2.a_time) from @a as t2
where t2.a_xm=t1.a_xm and t2.a_time<t1.a_time
and a_log='q'
) as a_time1 ,
a_time as a_time2
from @a as t1
where a_log='f'
) as tb
left join @a as ta
on tb.a_xm=ta.a_xm and tb.a_time1=ta.a_time and ta.a_log='q'
left join @a as ta1
on tb.a_xm=ta1.a_xm and tb.a_time2=ta1.a_time and ta1.a_log='f'
order by row_number() over (order by tb.a_xm,a_time1,a_time2)