sql - 如何合并不同表中的范围

Question

给出以下 2 个表格:

T1
------------------
From | To  | Value
------------------
10   | 20  | XXX
20   | 30  | YYY
30   | 40  | ZZZ


T2
------------------
From | To  | Value
------------------
10   | 15  | AAA
15   | 19  | BBB
19   | 39  | CCC
39   | 40  | DDD

在 SQL Server 2008 上使用 T-SQL 获得以下结果的最佳方法是什么？

从/到范围是连续的(没有间隙)，下一个From始终与前一个To具有相同的值

Desired result
-------------------------------
From | To  | Value1 |  Value2
-------------------------------
10   | 15  | XXX    |  AAA
15   | 19  | XXX    |  BBB
19   | 20  | XXX    |  CCC
20   | 30  | YYY    |  CCC
30   | 39  | ZZZ    |  CCC
39   | 40  | ZZZ    |  DDD

Answer 1

首先，我声明与您发布的数据类似的数据。如果我所做的任何假设是错误的，请纠正我。更好的办法是在问题中发布您自己的声明，以便我们都使用相同的数据。

DECLARE @T1 TABLE (
  [From] INT,
  [To] INT,
  [Value] CHAR(3)
);

INSERT INTO @T1 (
  [From],
  [To],
  [Value]
)
VALUES
  (10, 20, 'XXX'),
  (20, 30, 'YYY'),
  (30, 40, 'ZZZ');

DECLARE @T2 TABLE (
  [From] INT,
  [To] INT,
  [Value] CHAR(3)
);

INSERT INTO @T2 (
  [From],
  [To],
  [Value]
)
VALUES
  (10, 15, 'AAA'),
  (15, 19, 'BBB'),
  (19, 39, 'CCC'),
  (39, 40, 'DDD');

这是我的选择查询，用于生成您的预期结果:

SELECT
  CASE
    WHEN [@T1].[From] > [@T2].[From]
    THEN [@T1].[From]
    ELSE [@T2].[From]
  END AS [From],
  CASE
    WHEN [@T1].[To] < [@T2].[To]
    THEN [@T1].[To]
    ELSE [@T2].[To]
  END AS [To],
  [@T1].[Value],
  [@T2].[Value]
FROM @T1
INNER JOIN @T2 ON
  (
    [@T1].[From] <= [@T2].[From] AND
    [@T1].[To] > [@T2].[From]
  ) OR
  (
    [@T2].[From] <= [@T1].[From] AND
    [@T2].[To] > [@T1].[From]
  );