--建议楼主的表中有一个自增加的ID列,以获取加入记录的先后顺序declare @t table(id int,a varchar(10),b varchar(10),c varchar(10),d varchar(10),e int,f int) insert @t values(1,'LineA1','Item01','10:01','10:30',12,31041) insert @t values(2,'LineA1','Item01','10:50','10:55',2,31043) insert @t values(3,'LineA1','Item01','11:50','12:10',16,31047) insert @t values(4,'LineA1','Item02','13:50','14:00',11,31050) insert @t values(5,'LineA1','Item03','16:30','17:00',30,31052) insert @t values(6,'LineA2','Item02','8:30','8:40',0,31032) insert @t values(7,'LineA2','Item02','10:01','10:31',10,31042) insert @t values(8,'LineA2','Item01','10:35','10:58',0,31044);with tb as( select *,row_number() over(partition by a order by id) row from @t) select a.A,a.B,isnull(b.d,'NULL')+'-'+a.c C,a.E from tb a left join tb b on a.a=b.a and a.row-1=b.row /* A B C E ---------- ---------- --------------------- ----------- LineA1 Item01 NULL-10:01 12 LineA1 Item01 10:30-10:50 2 LineA1 Item01 10:55-11:50 16 LineA1 Item02 12:10-13:50 11 LineA1 Item03 14:00-16:30 30 LineA2 Item02 NULL-8:30 0 LineA2 Item02 8:40-10:01 10 LineA2 Item01 10:31-10:35 0 */
--测试数据-- if object_id('dbo.tab') is not null drop table dbo.tab go create table dbo.tab ( A varchar(20), B varchar(20), C varchar(20), D varchar(20), E int , F varchar(20) ) GO insert into dbo.tab select 'LineA1','Item01', '10:01', '10:30', 12, '31041' UNION ALL select 'LineA1','Item01', '10:50', '10:55', 2 , '31043' UNION ALL select 'LineA1','Item01', '11:50', '12:10', 16, '31047' UNION ALL select 'LineA1','Item02', '13:50', '14:00', 11, '31050' UNION ALL select 'LineA1','Item03', '16:30', '17:00', 30, '31052' UNION ALL select 'LineA2','Item02', '8:30 ', '8:40 ', 0 , '31032' UNION ALL select 'LineA2','Item02', '10:01', '10:31', 10, '31042' UNION ALL select 'LineA2','Item01', '10:35', '10:58', 0 , '31044'--sql-- SELECT A,B, E,[c-d] = ISNULL((SELECT TOP 1 D FROM dbo.tab WHERE f < TAB_1.F AND A = TAB_1.A ORDER BY f DESC ),'NULL' ) + '-' + D FROM dbo.tab TAB_1--结果-- /* A B E c-d -------------------- -------------------- ----------- ----------------------------------------- LineA1 Item01 12 NULL-10:30 LineA1 Item01 2 10:30-10:55 LineA1 Item01 16 10:55-12:10 LineA1 Item02 11 12:10-14:00 LineA1 Item03 30 14:00-17:00 LineA2 Item02 0 NULL-8:40 LineA2 Item02 10 8:40 -10:31 LineA2 Item01 0 10:31-10:58(8 行受影响) */
insert @t values(1,'LineA1','Item01','10:01','10:30',12,31041)
insert @t values(2,'LineA1','Item01','10:50','10:55',2,31043)
insert @t values(3,'LineA1','Item01','11:50','12:10',16,31047)
insert @t values(4,'LineA1','Item02','13:50','14:00',11,31050)
insert @t values(5,'LineA1','Item03','16:30','17:00',30,31052)
insert @t values(6,'LineA2','Item02','8:30','8:40',0,31032)
insert @t values(7,'LineA2','Item02','10:01','10:31',10,31042)
insert @t values(8,'LineA2','Item01','10:35','10:58',0,31044);with tb as(
select *,row_number() over(partition by a order by id) row from @t)
select a.A,a.B,isnull(b.d,'NULL')+'-'+a.c C,a.E from tb a left join tb b on a.a=b.a and a.row-1=b.row
/*
A B C E
---------- ---------- --------------------- -----------
LineA1 Item01 NULL-10:01 12
LineA1 Item01 10:30-10:50 2
LineA1 Item01 10:55-11:50 16
LineA1 Item02 12:10-13:50 11
LineA1 Item03 14:00-16:30 30
LineA2 Item02 NULL-8:30 0
LineA2 Item02 8:40-10:01 10
LineA2 Item01 10:31-10:35 0
*/
if object_id('dbo.tab') is not null
drop table dbo.tab
go
create table dbo.tab
(
A varchar(20), B varchar(20), C varchar(20), D varchar(20), E int , F varchar(20)
)
GO insert into dbo.tab
select 'LineA1','Item01', '10:01', '10:30', 12, '31041' UNION ALL
select 'LineA1','Item01', '10:50', '10:55', 2 , '31043' UNION ALL
select 'LineA1','Item01', '11:50', '12:10', 16, '31047' UNION ALL
select 'LineA1','Item02', '13:50', '14:00', 11, '31050' UNION ALL
select 'LineA1','Item03', '16:30', '17:00', 30, '31052' UNION ALL
select 'LineA2','Item02', '8:30 ', '8:40 ', 0 , '31032' UNION ALL
select 'LineA2','Item02', '10:01', '10:31', 10, '31042' UNION ALL
select 'LineA2','Item01', '10:35', '10:58', 0 , '31044'--sql--
SELECT A,B, E,[c-d] = ISNULL((SELECT TOP 1 D FROM dbo.tab WHERE f < TAB_1.F AND A = TAB_1.A ORDER BY f DESC ),'NULL' ) + '-' + D
FROM dbo.tab TAB_1--结果--
/*
A B E c-d
-------------------- -------------------- ----------- -----------------------------------------
LineA1 Item01 12 NULL-10:30
LineA1 Item01 2 10:30-10:55
LineA1 Item01 16 10:55-12:10
LineA1 Item02 11 12:10-14:00
LineA1 Item03 30 14:00-17:00
LineA2 Item02 0 NULL-8:40
LineA2 Item02 10 8:40 -10:31
LineA2 Item01 0 10:31-10:58(8 行受影响)
*/
再次感谢各位大侠
本来抱着试试看的来这里咨询,没想到这么快就解决了。
但是总比自己的Excel再做一次快多了