我有 2 個如下
表 Table Demographics2
CREATE TABLE Demographics2
(Date date,
FirstID int,
SecondID int,
FirstName varchar(50),
LastName varchar(50),
DCode int
)
;
Insert into Demographics2 VALUES
('20200402', 342, 812, 'John', 'Smith', 823),
('20200414', 263, 812, 'Jane', 'Austen', 676),
('20200414', 263, 812, 'Jane', 'Austen', 556)
;
表項2
CREATE TABLE Item2
(Date date,
FirstID int,
SecondID int,
ItemName varchar(50),
ItemForm varchar(50),
ICode int
)
;
Insert into Item2 VALUES
('20200318', 754, 812, 'Perindo', 'Blue', 169),
('20200318', 754, 812, 'Perindo', 'Blue', 156),
('20200318', 754, 812, 'Lipitor', 'Blue', 295),
('20200318', 754, 812, 'Perindo', 'Blue', 627),
('20200318', 754, 812, 'Perindo', 'Blue', 740),
('20200318', 754, 812, 'Metformin', 'Green', 992),
('20200414', 263, 812, 'Pred', 'Blue', 234),
('20200414', 263, 812, 'Pred', 'Blue', 279),
('20200414', 263, 812, 'Phen', 'Blue', 605),
('20200414', 263, 812, 'Pred', 'Blue', 979),
('20200414', 263, 812, 'Phen', 'Blue', 513),
('20200414', 263, 812, 'Pred', 'Blue', 127)
當我 FULL OUTER JOIN 2 個表，在 WHERE 中使用時間過濾器時，我得到的結果缺少表 Item2 的 Date 20200318 的行
SELECT *
FROM master.[dbo].[Demographics2] d
FULL OUTER JOIN master.[dbo].[Item2] i
ON d.FirstID = i.FirstID
WHERE CONVERT(date,d.Date) BETWEEN '2020-03-01 00:00:00.000' AND '2020-05-01 00:00:00.000'
-- Result
Date FirstID SecondID FirstName LastName DCode Date FirstID SecondID ItemNameItemFormICode
2020-04-02 342 812 John Smith 823 NULL NULL NULL NULL NULL NULL
2020-04-14 263 812 Jane Austen 676 2020-04-14 263 812 Pred Blue 234
2020-04-14 263 812 Jane Austen 676 2020-04-14 263 812 Pred Blue 279
2020-04-14 263 812 Jane Austen 676 2020-04-14 263 812 Phen Blue 605
2020-04-14 263 812 Jane Austen 676 2020-04-14 263 812 Pred Blue 979
2020-04-14 263 812 Jane Austen 676 2020-04-14 263 812 Phen Blue 513
2020-04-14 263 812 Jane Austen 676 2020-04-14 263 812 Pred Blue 127
2020-04-14 263 812 Jane Austen 556 2020-04-14 263 812 Pred Blue 234
2020-04-14 263 812 Jane Austen 556 2020-04-14 263 812 Pred Blue 279
2020-04-14 263 812 Jane Austen 556 2020-04-14 263 812 Phen Blue 605
2020-04-14 263 812 Jane Austen 556 2020-04-14 263 812 Pred Blue 979
2020-04-14 263 812 Jane Austen 556 2020-04-14 263 812 Phen Blue 513
2020-04-14 263 812 Jane Austen 556 2020-04-14 263 812 Pred Blue 127
有沒有辦法在 WHERE 子句中放置時間過濾器，但仍然獲得 2 個表的所有行
（我的預期結果是從 FULL OUTER JOIN 2 個表中得到，而 WHERE 子句中沒有時間過濾器）
-- Expected result
Date FirstID SecondID FirstName LastName DCode Date FirstID SecondID ItemName ItemForm ICode
2020-04-02 342 812 John Smith 823 NULL NULL NULL NULL NULL NULL
2020-04-14 263 812 Jane Austen 676 2020-04-14 263 812 Pred Blue 234
2020-04-14 263 812 Jane Austen 676 2020-04-14 263 812 Pred Blue 279
2020-04-14 263 812 Jane Austen 676 2020-04-14 263 812 Phen Blue 605
2020-04-14 263 812 Jane Austen 676 2020-04-14 263 812 Pred Blue 979
2020-04-14 263 812 Jane Austen 676 2020-04-14 263 812 Phen Blue 513
2020-04-14 263 812 Jane Austen 676 2020-04-14 263 812 Pred Blue 127
2020-04-14 263 812 Jane Austen 556 2020-04-14 263 812 Pred Blue 234
2020-04-14 263 812 Jane Austen 556 2020-04-14 263 812 Pred Blue 279
2020-04-14 263 812 Jane Austen 556 2020-04-14 263 812 Phen Blue 605
2020-04-14 263 812 Jane Austen 556 2020-04-14 263 812 Pred Blue 979
2020-04-14 263 812 Jane Austen 556 2020-04-14 263 812 Phen Blue 513
2020-04-14 263 812 Jane Austen 556 2020-04-14 263 812 Pred Blue 127
NULL NULL NULL NULL NULL NULL 2020-03-18 754 812 Perindo Blue 169
NULL NULL NULL NULL NULL NULL 2020-03-18 754 812 Perindo Blue 156
NULL NULL NULL NULL NULL NULL 2020-03-18 754 812 Lipitor Blue 295
NULL NULL NULL NULL NULL NULL 2020-03-18 754 812 Perindo Blue 627
NULL NULL NULL NULL NULL NULL 2020-03-18 754 812 Perindo Blue 740
NULL NULL NULL NULL NULL NULL 2020-03-18 754 812 Metformin Green 992
uj5u.com熱心網友回復：
該WHERE子句過濾掉僅存在于第二個表中的記錄，因為這些記錄d.Date是NULL.
您也可以使用OR d.Date IS NULL包含這些記錄：
SELECT *
FROM master.[dbo].[Demographics2] d
FULL OUTER JOIN master.[dbo].[Item2] i
ON d.FirstID = i.FirstID
WHERE (CONVERT(date,d.Date) BETWEEN '2020-03-01 00:00:00.000' AND '2020-05-01 00:00:00.000')
OR d.Date IS NULL
@TimBiegeleisen 的回答也是有效的，它基本上取決于你想做什么。
uj5u.com熱心網友回復：
您可以嘗試借助以下方法對兩個日期進行邏輯過濾COALESCE()：
SELECT *
FROM master.[dbo].[Demographics2] d
FULL OUTER JOIN master.[dbo].[Item2] i
ON d.FirstID = i.FirstID
WHERE CONVERT(date, COALESCE(d.Date, i.Date)) BETWEEN '2020-03-01' AND '2020-05-01';
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/515048.html