Sql数据转换

Question

这是我的源表

Reference   ModifiedDate             Teachers   Students SchoolID    ETC
-------------------------------------------------------------------------
1023175     2017-03-03 16:02:01.723     10       25        5
1023175     2017-03-07 07:59:49.283     15       50        15
1023175     2017-03-12 11:14:40.230     25       6         5
1023176     2017-03-04 16:02:01.723     11       35        8
1023176     2017-03-08 07:59:49.283     16       60        25
1023177     2017-03-15 11:14:40.230     15       7         2

我需要下面的输出

Reference   StartDate                 EndDate  
---------------------------------------------
1023175   2017-03-03 16:02:01.723     2017-03-07 07:59:49.283
1023175   2017-03-07 07:59:49.283     2017-03-12 11:14:40.230
1023175   2017-03-12 11:14:40.230     9999-12-31 00:00:00.000 
1023176   2017-03-04 16:02:01.723     2017-03-08 07:59:49.283
1023176   2017-03-08 07:59:49.283     9999-12-31 00:00:00.000 
1023177   2017-03-15 11:14:40.230     9999-12-31 00:00:00.000 (last record should have this value)

师范生SchoolID

 10       25        5
 15       50        15
 25       6         5
 11       35        8
 16       60        25
 15       7         2

所有其他栏目，如教师，学生和SchoolId等，也必须在输出与每一个记录。

对于如何做到这一点，有什么建议吗？使用Server 2008

Answer 1

使用外部适用()

select 
    Reference
  , StartDate = t.ModifiedDate
  , EndDate = coalesce(x.ModifiedDate, convert(datetime,'9999-12-31 00:00:00.000'))
  , Teachers
  , Students
  , SchoolID
from t
  outer apply (
    select top 1 i.ModifiedDate
    from t as i
    where i.Reference = t.Reference
      and i.ModifiedDate > t.ModifiedDate
    order by i.ModifiedDate asc
    ) x

rextester演示：http://rextester.com/RFTD32624

返回：

+-----------+-------------------------+-------------------------+----------+----------+----------+
| Reference |        StartDate        |         EndDate         | Teachers | Students | SchoolID |
+-----------+-------------------------+-------------------------+----------+----------+----------+
|   1023175 | 2017-03-03 16:02:01.723 | 2017-03-07 07:59:49.283 |       10 |       25 |        5 |
|   1023175 | 2017-03-07 07:59:49.283 | 2017-03-12 11:14:40.230 |       15 |       50 |       15 |
|   1023175 | 2017-03-12 11:14:40.230 | 9999-12-31 00:00:00.000 |       25 |        6 |        5 |
|   1023176 | 2017-03-04 16:02:01.723 | 2017-03-08 07:59:49.283 |       11 |       35 |        8 |
|   1023176 | 2017-03-08 07:59:49.283 | 9999-12-31 00:00:00.000 |       16 |       60 |       25 |
|   1023177 | 2017-03-15 11:14:40.230 | 9999-12-31 00:00:00.000 |       15 |        7 |        2 |
+-----------+-------------------------+-------------------------+----------+----------+----------+

参考资料：

• - msdn
• The的应用运算符Rob Farley的威力
• 应用:切片！它掉下来了！一切都是这样！-布拉德·舒尔兹