加入時處理丟失的資料

考慮下面的查詢

DECLARE @DateTimeStart DATETIME
DECLARE @DateTimeEnd DATETIME

SET @dateTimeStart = '2001-04-04'
SET @dateTimeEnd = '2001-04-06'

DECLARE @QH TABLE 
            (
                dt date, 
                QueueName varchar(10)
            )

INSERT INTO @QH (Dt, QueueName)
VALUES ('2001-04-04', 'Queue01'),
       ('2001-04-05', 'Queue01'),
       ('2001-04-06', 'Queue01'),
       ('2001-04-04', 'Queue02'),
       ('2001-04-05', 'Queue02'),
       ('2001-04-06', 'Queue02')

-- SELECT * FROM @QH

DECLARE @SNH TABLE
             (
                 dt date, 
                 QueueName varchar(10),
                 SN varchar(10)
             )

INSERT INTO @SNH (Dt, QueueName, SN)
VALUES ('2001-04-04', 'Queue01', 'Q01SN01'),
       ('2001-04-05', 'Queue01', 'Q01SN01'),
       ('2001-04-06', 'Queue01', 'Q01SN01'),
       ('2001-04-04', 'Queue02', 'Q02SN01'),
       ('2001-04-05', 'Queue02', 'Q02SN01'),
       ('2001-04-06', 'Queue02', 'Q02SN02')

-- SELECT * FROM @SNH

SELECT
    MIN(QHCore.dt) clnStartDate, 
    MAX(QHCore.dt) clnEndDate, 
    QHCore.QueueName AS clnQueueName, 
    SNHStart.SN AS clnSNStart, 
    SNHEnd.SN AS clnSNEnd
FROM    
    @QH QHCore, @QH QHStart, @QH QHEnd, @SNH SNHStart, @SNH SNHEnd
WHERE
    QHCore.QueueName = QHStart.QueueName
    AND QHCore.QueueName = QHEnd.QueueName
    AND QHCore.QueueName = SNHStart.QueueName
    AND QHCore.QueueName = SNHEnd.QueueName
    AND SNHStart.dt = @DateTimeStart
    AND SNHEnd.dt = @DateTimeEnd
GROUP BY 
    QHCore.QueueName, SNHStart.SN, SNHEnd.SN

當@DateTimeStart和@DateTimeEnd匹配@SNH 表中存在的那些時，它會選擇漂亮的表，

但是如果@SNH 中沒有對應的條目如下

insert into @SNH(Dt, QueueName, SN)
values
--('2001-04-04','Queue01','Q01SN01'),
('2001-04-05','Queue01','Q01SN01'),
('2001-04-06','Queue01','Q01SN01'),
('2001-04-04','Queue02','Q02SN01'),
('2001-04-05','Queue02','Q02SN01'),
('2001-04-06','Queue02','Q02SN02')

那么整行都沒有被選中。

那么，有沒有辦法以某種方式選擇結果表的空值？

它看起來如下

uj5u.com熱心網友回復：

我認為to和 window 函式的LEFT連接會做你想要的。@QH@SNH

目前尚不清楚您是希望將日期限制應用于兩個表還是僅應用于@SNH.

如果您想要 的所有日期@QH，即使它們不在日期限制之間：

SELECT DISTINCT
       MIN(q.Dt) OVER (PARTITION BY q.QueueName) clnStartDate,
       MAX(q.Dt) OVER (PARTITION BY q.QueueName) clnEndDate,
       q.QueueName clnQueueName,    
       FIRST_VALUE(s.SN) OVER (PARTITION BY q.QueueName ORDER BY q.Dt) clnSNStart,
       FIRST_VALUE(s.SN) OVER (PARTITION BY q.QueueName ORDER BY q.Dt DESC) clnSNEnd
FROM @QH q LEFT JOIN @SNH s
ON s.QueueName = q.QueueName AND s.Dt = q.Dt
AND s.Dt BETWEEN @DateTimeStart AND @DateTimeEnd;

如果要將限制應用于兩個表：

SELECT DISTINCT
       MIN(q.Dt) OVER (PARTITION BY q.QueueName) clnStartDate,
       MAX(q.Dt) OVER (PARTITION BY q.QueueName) clnEndDate,
       q.QueueName clnQueueName,    
       FIRST_VALUE(s.SN) OVER (PARTITION BY q.QueueName ORDER BY q.Dt) clnSNStart,
       FIRST_VALUE(s.SN) OVER (PARTITION BY q.QueueName ORDER BY q.Dt DESC) clnSNEnd
FROM @QH q LEFT JOIN @SNH s
ON s.QueueName = q.QueueName AND s.Dt = q.Dt
WHERE q.Dt BETWEEN @DateTimeStart AND @DateTimeEnd;

標籤：sql sql服务器 左连接 窗函数

上一篇：選擇飛機完成的旅行次數

下一篇：如何在Databricks的SQL查詢中使用python變數？