遇到不会写的SQL，哪位高手帮忙看看？
希望从数据源表里面，读出“rework”为1的行。然后再找到这一行的上面一行（根据日期和时间排序），得到结果表。
数据源表
Row Number Notes 日期 时间 Employee rework
1 M00002 2/13/2007 13 11 0
9 M00002 Noisy fan. 2/13/2007 14 54 0
8 M00002 Replaced fan 2/14/2007 18 102 1
7 M00024 3/5/2007 11 66 0
2 M00024 3/5/2007 12 25 0
11 M00024 Replaced QQ 3/5/2007 15 102 1
要得出下面的结果
Number Failer Fail Note 日期1 Resolver Resolve Note 日期2
M00002 54 Noisy fan. 2/13/2007 102 Replaced fan 2/14/2007
M00024 25 3/5/2007 102 Replaced QQ 3/5/2007
[解决办法]
Select
Number,
(Select TOP 1 Employee From 数据源表 Where Number = A.Number And (日期 < A.日期 Or (日期 = A.日期 And 时间 < A.时间)) Order By 日期 Desc, 时间 Desc) As Failer,
(Select TOP 1 Notes From 数据源表 Where Number = A.Number And (日期 < A.日期 Or (日期 = A.日期 And 时间 < A.时间)) Order By 日期 Desc, 时间 Desc) As [Fail Note],
(Select TOP 1 日期 From 数据源表 Where Number = A.Number And (日期 < A.日期 Or (日期 = A.日期 And 时间 < A.时间)) Order By 日期 Desc, 时间 Desc) As 日期1,
Employee As Resolver,
Notes As [Resolve Note],
日期 As 日期2
From
数据源表 A
Where rework = 1
[解决办法]
declare @table table(Row int,Number varchar(20),Notes varchar(20),日期 datetime,时间 int,Employee int,rework int)
insert into @table
select 1, 'M00002 ', ' ', '2/13/2007 ',13,11,0
union all select 9, 'M00002 ', 'Noisy fan. ', '2/13/2007 ',14,54,0
union all select 8, 'M00002 ', 'fan ', '2/14/2007 ',18,102,1
union all select 7, 'M00024 ', ' ', '3/5/2007 ',11,66,0
union all select 2, 'M00024 ', 'MSN ', '3/5/2007 ',12,25,0
union all select 11, 'M00024 ', 'QQ ', '3/5/2007 ',15,102,1
select*from @table order by 日期,时间
select
a.Number,
(select top 1 b.Employee from @table b where b.日期 <a.日期 or(b.日期=a.日期 and b.时间 <a.时间)order by b.日期 desc,b.时间 desc)as Emp1,
(select top 1 b.Notes from @table b where b.日期 <a.日期 or(b.日期=a.日期 and b.时间 <a.时间)order by b.日期 desc,b.时间 desc)as Note1,
(select top 1 b.日期 from @table b where b.日期 <a.日期 or(b.日期=a.日期 and b.时间 <a.时间)order by b.日期 desc,b.时间 desc)as 日期1,
a.Employee as Emp2,a.Notes as Note2,a.日期 as 日期2
from @table a
where rework=1
order by a.日期,a.时间
/*
(6 row(s) affected)
Row Number Notes 日期 时间 Employee rework
----------- -------------------- -------------------- ----------------------- ----------- ----------- -----------
1 M00002 2007-02-13 00:00:00.000 13 11 0
9 M00002 Noisy fan. 2007-02-13 00:00:00.000 14 54 0
8 M00002 fan 2007-02-14 00:00:00.000 18 102 1
7 M00024 2007-03-05 00:00:00.000 11 66 0
2 M00024 MSN 2007-03-05 00:00:00.000 12 25 0
11 M00024 QQ 2007-03-05 00:00:00.000 15 102 1
(6 row(s) affected)
Number Emp1 Note1 日期1 Emp2 Note2 日期2
-------------------- ----------- -------------------- ----------------------- ----------- -------------------- -----------------------
M00002 54 Noisy fan. 2007-02-13 00:00:00.000 102 fan 2007-02-14 00:00:00.000
M00024 25 MSN 2007-03-05 00:00:00.000 102 QQ 2007-03-05 00:00:00.000
*/
