First I declare data that looks like the data you posted. Please correct me if any assumptions I have made are wrong. Better would be to post your own declaration in the question so we are all working with the same data.

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]
  );

我写道:

;With EPs as (
    select [From] as EP from @T1
    union
    select [To] from @T1
    union
    select [From] from @T2
    union
    select [To] from @T2
), OrderedEndpoints as (
    select EP,ROW_NUMBER() OVER (ORDER BY EP) as rn from EPs
)
select
    oe1.EP,
    oe2.EP,
    t1.Value,
    t2.Value
from
    OrderedEndpoints oe1
        inner join
    OrderedEndpoints oe2
        on
            oe1.rn = oe2.rn - 1
        inner join
    @T1 t1
        on
            oe1.EP < t1.[To] and
            oe2.EP > t1.[From]
        inner join
    @T2 t2
        on
            oe1.EP < t2.[To] and
            oe2.EP > t2.[From]

也就是说,你创设了一套包含所有可能的终点站(EPs),然后由您“选择”这些期限,并逐个分配(<>OrderedEPs)。

Then the final query assembles each "adjacent" pair of rows together, and joins back to the original tables to find which rows from each one overlap the selected range.

The below query finds the smallest ranges, then picks the values back out the tables again:

SELECT ranges.from, ranges.to, T1.Value, T2.Value
FROM (SELECT all_from.from, min(all_to.to) as to
    FROM (SELECT T1.FROM
        FROM T1
        UNION 
        SELECT T2.FROM 
        FROM T2) all_from
    JOIN (SELECT T1.TO
        FROM T1
        UNION
        SELECT T2.FROM
        FROM T2) all_to ON all_from.from < all_to.to
    GROUP BY all_from.from) ranges
JOIN T1 ON ranges.from >= T1.from AND ranges.to <= T1.to
JOIN T2 ON ranges.from >= T2.from AND ranges.to <= T2.to
ORDER BY ranges.from

由于这些答复,但我最后利用了“智囊团”,我认为,Wgich是更清洁的。

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

INSERT INTO @T1 (  [From],  [To],  [Value]) VALUES  (10, 20,  XXX ),  (20, 30,  YYY ),  (30, 40,  ZZZ );
INSERT INTO @T2 (  [From],  [To],  [Value]) VALUES  (10, 15,  AAA ),  (15, 19,  BBB ),  (19, 39,  CCC ),  (39, 40,  DDD );

;with merged1 as 
(
    select 
        t1.[From] as from1,
        t1.[to] as to1,
        t1.Value as Value1,
        t2.[From] as from2,
        t2.[to] as to2,
        t2.Value as Value2
    from @t1 t1
    inner join @T2 t2 
        on t1.[From] <  t2.[To]
        and t1.[To] >= t2.[From]

)
,merged2 as 
(
    select 
          case when from2>=from1 then from2 else from1 end as [From]
         ,case when to2<=to1 then to2 else to1 end as [To]
         ,value1
         ,value2
    from merged1
)
select * from merged2