一个表记录的维修情况,包含异常现象,时间区分维修的先后顺序
Number, 异常现象1, 异常现象2, 日期
100-001 AAA BBB 2008-01-01
100-001 CCC DDD 2008-01-01
100-001 EEE FFF 2008-01-01
100-001 GGG FFF 2008-01-02
100-001 HHH III 2008-01-02希望得到这样的数据
Number, 异常现象1, 异常现象2, 日期, Number, 异常现象1, 异常现象2, 日期
100-001 AAA BBB 2008-01-01 100-001 GGG FFF 2008-01-02
100-001 CCC DDD 2008-01-01 100-001 HHH III 2008-01-02
100-001 EEE FFF 2008-01-01就是在一个表里能表现出维修前后的对比情况
Number, 异常现象1, 异常现象2, 日期
100-001 AAA BBB 2008-01-01
100-001 CCC DDD 2008-01-01
100-001 EEE FFF 2008-01-01
100-001 GGG FFF 2008-01-02
100-001 HHH III 2008-01-02希望得到这样的数据
Number, 异常现象1, 异常现象2, 日期, Number, 异常现象1, 异常现象2, 日期
100-001 AAA BBB 2008-01-01 100-001 GGG FFF 2008-01-02
100-001 CCC DDD 2008-01-01 100-001 HHH III 2008-01-02
100-001 EEE FFF 2008-01-01就是在一个表里能表现出维修前后的对比情况
create table os(number varchar(10),异常现象1 varchar(10),异常现象2 varchar(10),日期 datetime)
insert into os select '100-001','AAA','BBB','2008-01-01'
insert into os select '100-001','CCC','DDD','2008-01-01'
insert into os select '100-001','EEE','FFF','2008-01-01'
insert into os select '100-001','GGG','FFF','2008-01-02'
insert into os select '100-001','HHH','III','2008-01-02'
select id=identity(int,1,1),* into #1 from os a where not exists(select 1 from os where number=a.number and 日期<a.日期)
select id=identity(int,1,1),* into #2 from os a where not exists(select 1 from os where number=a.number and 日期>a.日期)select m.number,m.异常现象1,m.异常现象2,m.日期,
isnull(n.number,''),isnull(n.异常现象1,''),isnull(n.异常现象2,''),isnull(n.日期,'') from
#1 m left join #2 n on m.id=n.id
如果多个不通的number就不可以了