表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
-------------------------------------------------------------------
