主页 / dba / 问题 / 3396
Accepted
B3ret
Asked: 2011-06-21 22:11:25 +0800 CST

SQL Server：使用“有效”日期连接两个表

  • 772

我有两个这样的表：

T1_Key, T1_Data, T1_ValidUntil
1         A       2000-01-01
1         B       2000-06-30
2         C       2005-05-31
3         D       2004-12-31
3         E       2007-04-30
3         F       2008-01-31

T2_Key, T2_Data, T2_ValidUntil
1         R       2002-03-31
2         S       2001-06-30
2         T       2003-02-28
2         U       2005-05-31
3         V       2006-09-30
3         W       2007-06-30

这定义了特定数据有效的多个间隔。例如对于键 2 和日期 2002-01-01，条目 (2, C, 2005-05-31) 和 (2, T, 2003-02-28) 是有效的（即具有下一个较大日期的条目）。对于键 2 和日期 2010-01-01，没有有效的条目。

对于像上面这样的单个查询，我只是分别查询两个表并将数据连接到我的应用程序中。但是，对于维护应用程序，我想加入这两个表。结果应该包含所有有效的间隔/组合，就像这样（键 3 是最复杂的，因为没有共享日期）：

T1_Key, T1_Data, T1_ValidUntil, T2_Key, T2_Data, T2_ValidUntil
1        A        2000-01-01    1       R        2002-03-31
1        B        2000-06-30    1       R        2002-03-31
2        C        2005-05-31    2       S        2001-06-30
2        C        2005-05-31    2       T        2003-02-28
2        C        2005-05-31    2       U        2005-05-31
3        D        2004-12-31    3       V        2006-09-30  <- I1
3        E        2007-04-30    3       V        2006-09-30  <- I2
3        E        2007-04-30    3       W        2007-06-30  <- I3
3        F        2008-01-31    3       W        2007-06-30  <- I4

ASCII-Art vor Case 3（时间线）：

     2004-12-31        2007-04-30             2008-01-31
T1  -----|-----------------|-----------------------|----------
     I1      I2       I3       I4      Nothing valid here
T2 ---------------|-----------------|--------------------------
              2006-09-30       2007-06-30

目前我只是使用键加入，但这给出了很多无效的组合。因此，我需要一种方法来说“将其与另一个表中具有下一个更高（或相等）日期的条目加入”，以及一种在两个方向上都这样做的方法......

该方案是预定义的，我无法对其进行太多更改。第三个多对多表对我来说似乎是多余的数据。

提前致谢！

sql-server join

1 个回答

  1. Best Answer
    DECLARE @t1 TABLE (T1_key int, T1_Data char(1), T1_ValidUntil datetime)
DECLARE @t2 TABLE (T2_key int, T2_Data char(1), T2_ValidUntil datetime)

INSERT @t1 VALUES (1, 'A', '2000-01-01')
INSERT @t1 VALUES (1, 'B', '2000-06-30')
INSERT @t1 VALUES (2, 'C', '2005-05-31')
INSERT @t1 VALUES (3, 'D', '2004-12-31')
INSERT @t1 VALUES (3, 'E', '2007-04-30')
INSERT @t1 VALUES (3, 'F', '2008-01-31')

INSERT @t2 VALUES (1, 'R', '2002-03-31')
INSERT @t2 VALUES (2, 'S', '2001-06-30')
INSERT @t2 VALUES (2, 'T', '2003-02-28')
INSERT @t2 VALUES (2, 'U', '2005-05-31')
INSERT @t2 VALUES (3, 'V', '2006-09-30')
INSERT @t2 VALUES (3, 'W', '2007-06-30')

SELECT
   T1.*, T2x.*
FROM
   @t1 T1
   CROSS APPLY
   (SELECT TOP 1*
   FROM @t2 
   WHERE T1_key = T2_key AND T2_ValidUntil >= T1_ValidUntil
   ORDER BY T2_ValidUntil
   ) T2x
UNION
SELECT
   T1x.*, T2.*
FROM
   @t2 T2
   CROSS APPLY
   (SELECT TOP 1*
   FROM @t1
   WHERE T1_key = T2_key AND T1_ValidUntil >= T2_ValidUntil
   ORDER BY T1_ValidUntil
   ) T1x
    • 3

相关问题