sql-server-2008 - 将派生表与自身连接

Question

简而言之:

我有事件表:
[表]

time | newState
1200 | 1
1300 | 2
1400 | 5

我需要将此表转换为间隔表 [intervals]:

t0   | t1   | state
1200 | 1300 | 1
1300 | 1400 | 2

限制:SQL 精简版

查询:

SELECT leftPart.time AS t0, min(rightPart.time) AS t1, leftPart.newState
FROM tbl AS leftPart 
LEFT OUTER JOIN tbl As rightPart
ON leftPart.time<rightPart.time
GROUP BY leftPart.Time,leftPart.newState

当 [tbl] 是数据库中的永久表时，它工作得很好，但在我的情况下，[tbl] 是从另一个选择子查询派生的!像这样:

(SELECT time,newState
from ...) AS derivedTb

所以，当我尝试这样的事情时:

SELECT derivedTbl.time As t0,derivedTbl.state,min(rigthTblPart.time) FROM
(SELECT time,newState
from ...) AS derivedTbl
LEFT OUTER JOIN with derivedTbl AS rigthTblPart
ON derivedTbl.Time<rightTblPart.Time ...

它抛出错误:“derivedTbl 不存在”...

似乎它的别名下的派生表对更高级别的查询不可见(谢谢，谷歌翻译!))...

有没有办法在查询中存储派生表并在查询的不同部分使用它们？ SQL CE 不支持临时表、 View 和公用表表达式...

任务详情(如果感兴趣):
我有 2 个事件表:

[states1]
time | state1
1200 | 1
1300 | 2
1400 | 3

[states2]
time | state2
1200 | 0
1230 | 10
1330 | 20
1430 | 30

我需要在间隔表中转换它们:

[intervals]
t0      t1      state1  state2
1200    1230    1   0
1230    1300    1   10
1300    1330    2   10
1330    1400    2   20
1400    1430    3   20
1430    NULL    3   30

转换阶段:
1. 总体时间表

(SELECT Time FROM States1 
UNION
SELECT Time FROM States2) AS timetbl

加入 states1 表

加入 states2 表

此刻一切顺利:

SELECT     states12tbl.time, states12tbl.state1, states2tbl.State AS state2
   FROM          (SELECT     states12tbl_1.time, states12tbl_1.state1, MAX(states2tbl.Time) AS states2time
       FROM          (SELECT     timetbl.time, states1tbl.State AS state1
           FROM          (SELECT     timetbl_1.Time AS time, MAX(States1tbl.Time) AS state1time
               FROM          (SELECT     Time
                   FROM          States1
                   UNION
                   SELECT     Time
                   FROM         States2) AS timetbl_1 LEFT OUTER JOIN
                  States1 AS States1tbl ON States1tbl.Time <= timetbl_1.Time
               GROUP BY timetbl_1.Time) AS timetbl INNER JOIN
              States1 AS states1tbl ON timetbl.state1time = states1tbl.Time

将表与自身连接...这是问题，我需要将代码(3)与自身连接，因为 sql ce 无法记住临时表...如果您有更好的想法，请分享:)

Answer 1

根据您的第一个 SELECT 创建一个 VIEW。