CREATE TABLE XX (
MNo VARCHAR(20),
MType VARCHAR(20),
MStart time,
MEnd time);

INSERT INTO XX VALUES 
('CT-001','Run','01:00:00','02:00:00'),
('CT-002','Run','01:15:00','01:52:00');

CREATE TABLE YY (
MNo VARCHAR(20),
MType VARCHAR(20),
MStart time,
MEnd time);

INSERT INTO YY VALUES 
('CT-001','VA','01:10:00','01:20:00'),
('CT-001','CO','01:30:00','01:40:00'),
('CT-001','NO','01:50:00','01:55:00'),
--
('CT-002','2VB','01:03:00','01:05:00'),
('CT-002','2VA','01:10:00','01:20:00'),
('CT-002','2CO','01:30:00','01:40:00'),
('CT-002','2NO','01:50:00','01:55:00'),
('CT-002','2NH','02:00:00','02:10:00');

SELECT FF.MNo,
CASE WHEN FF.TStart >= QQ.MStart AND FF.TEnd <= QQ.MEnd 
AND FF.MARK1='S' AND FF.MARK2A='E' THEN 'Run' 
ELSE 'PreRun' END AS TType,
FF.TStart,FF.TEnd
FROM (
SELECT *,
LEAD(PP.TStart,1,NULL) OVER (PARTITION BY PP.MNo ORDER BY PP.TStart) AS TEnd,
LEAD(PP.MARK2,1,NULL) OVER (PARTITION BY PP.MNo ORDER BY PP.TStart) AS MARK2A
FROM (
SELECT MNo,MStart AS TStart,'S' AS MARK1,'' AS MARK2
FROM XX
UNION ALL
SELECT MNo,MEnd AS TStart,'' AS MARK1,'E' AS MARK2
FROM XX
--
UNION ALL
SELECT MNo,MStart AS TStart,'S' AS MARK1,'' AS MARK2
FROM YY
UNION ALL
SELECT MNo,MEnd AS TStart,'' AS MARK1,'E' AS MARK2
FROM YY
) AS PP) AS FF
LEFT JOIN XX AS QQ ON QQ.MNo = FF.MNo
WHERE FF.TEnd IS NOT NULL
ORDER BY FF.MNo,FF.TStart

Demo

我覺得不能夠

這樣子有點難啦!!
沒基本對應欄位。要怎麼搞。

電腦是很白吃的。你要告訴他用啥做基本對應值。

如樓上兩位答覆，沒有對應的Key值，要如何查詢出來?

如果我理解正確,這是一個 連續範圍問題 + CTE 遞迴 搭配 OUTER JOIN 問題

;WITH CTE AS (
   SELECT Name,StartTime,EndTime
   FROM T1
   UNION ALL
   SELECT Name,DATEADD(MINUTE, 1, StartTime) ,EndTime
   FROM CTE 
   WHERE DATEADD(MINUTE, 1, StartTime) <= EndTime
) 
SELECT Name,
       MAX(CASE WHEN t2StartTime IS NULL THEN 'Pre-Run' ELSE 'RUN' END),
       MIN(CASE WHEN t2StartTime IS NULL AND DATEADD(MINUTE, - 1, StartTime) > minTime 
          THEN DATEADD(MINUTE, - 1, StartTime) ELSE StartTime END) starttime,
       MAX(CASE WHEN t2StartTime IS NULL AND DATEADD(MINUTE, 1, StartTime) < maxTime 
          THEN DATEADD(MINUTE, 1, StartTime) ELSE StartTime END) endtime
FROM (
 SELECT c.*,
        ROW_NUMBER() OVER(ORDER BY c.StartTime) -
           ROW_NUMBER() OVER(ORDER BY t2.StartTime) grp,
        t2.StartTime t2StartTime,
        MIN(c.starttime) OVER(partition by c.NAME) minTime,
        Max(c.starttime) OVER(partition by c.NAME) maxTime
  FROM CTE c 
  LEFT JOIN T2 t2
  ON  c.StartTime BETWEEN t2.StartTime AND t2.EndTime
  AND c.Name = t2.Name
) tt
GROUP BY grp,Name
ORDER BY 3

db<>fiddle here

使用 PostgreSQL 14, Range 以及 Multirange

create table it220325a (
  machine text
, mchstat text
, statrange tsrange
);

insert into it220325a values
('CT-001', 'RUN', tsrange('2022-03-25 01:00:00', '2022-03-25 02:00:00)', '[)'));

create table it220325b (
  machine text
, mchstat text
, statrange tsrange
, exclude using gist (statrange WITH &&)
);

insert into it220325b values
('CT-001', 'VACUUM', tsrange('2022-03-25 01:10:00', '2022-03-25 01:20:00', '[)')), 
('CT-001', 'COATING', tsrange('2022-03-25 01:30:00', '2022-03-25 01:40:00', '[)')),
('CT-001', 'NONVACUUM', tsrange('2022-03-25 01:50:00', '2022-03-25 01:55:00', '[)'));

--
with t1(machine, rangeagg) as (
select machine
     , range_agg(statrange)
  from it220325b
 group by machine
)
select a.machine
     , 'PRE-RUN' as "type"
     , unnest(tsmultirange(a.statrange) - t1.rangeagg) as "statrange"
  from it220325a a
  join t1
    on a.machine = t1.machine
union all
select a.machine
     , 'RUN'
     , b.statrange
  from it220325a a
  join it220325b b
    on a.machine = b.machine
   and a.statrange @> b.statrange
 order by 1,3;
 
machine  |  type   |                   statrange
---------+---------+-----------------------------------------------
 CT-001  | PRE-RUN | ["2022-03-25 01:00:00","2022-03-25 01:10:00")
 CT-001  | RUN     | ["2022-03-25 01:10:00","2022-03-25 01:20:00")
 CT-001  | PRE-RUN | ["2022-03-25 01:20:00","2022-03-25 01:30:00")
 CT-001  | RUN     | ["2022-03-25 01:30:00","2022-03-25 01:40:00")
 CT-001  | PRE-RUN | ["2022-03-25 01:40:00","2022-03-25 01:50:00")
 CT-001  | RUN     | ["2022-03-25 01:50:00","2022-03-25 01:55:00")
 CT-001  | PRE-RUN | ["2022-03-25 01:55:00","2022-03-25 02:00:00")
(7 rows)

Demo

參考樓上大大，提供一個簡單版本，將RUN和PRE-RUN拆開再合併

WITH RankTmp
AS     (SELECT T2.NAME,
        T2.StartTime,
        T2.EndTime,
        ROW_NUMBER() OVER(ORDER BY StartTime) ranks
        FROM   T2
        GROUP  BY T2.NAME,　T2.StartTime,　T2.EndTime)


        
SELECT A.*
FROM   (SELECT T2.NAME,
               'RUN'        AS 'Type',
               T2.StartTime AS 'StartTime',
               T2.EndTime   AS 'EndTime'
        FROM   T2
        
        UNION
        
        SELECT T2.NAME,
               'PRE-RUN'         AS 'Type',
               MIN(T1.StartTime) AS 'StartTime',
               Min(T2.StartTime) AS 'EndTime'
        FROM   T1 LEFT JOIN T2 
        ON T1.NAME = T2.NAME
        GROUP  BY T2.NAME
        
        UNION
        
        SELECT tmp1.NAME,
               'PRE-RUN' AS 'Type',
               tmp1.EndTime AS 'StartTime',
               (CASE WHEN tmp2.StartTime IS NULL THEN b.EndTime ELSE Dateadd(minute, Datediff(minute, tmp1.EndTime, tmp2.StartTime), tmp1.EndTime) END )  AS 'EndTime'
        FROM   ranktmp tmp1 LEFT JOIN ranktmp tmp2
        ON tmp1.ranks = tmp2.ranks - 1
        AND tmp1.NAME = tmp2.NAME
        LEFT JOIN T1 b ON tmp1.NAME = b.NAME) A
ORDER  BY A.NAME,
          A.Endtime