加入兩個表并用另一個表上的日期替換null

我有兩張桌子

1. 用戶表

2. 使用表

我需要誰沒有明智地使用該工具。

預期輸出：

我嘗試加入（右加入）表，但我得到了正確的用戶名，但沒有得到日期（得到 NULL）。

select a.username,b.username,b.date from
(select distinct date, b.username username
from UsageTable 
) b
right join
toolusers a
on
b.username = a.username

uj5u.com熱心網友回復：

您可以使用反連接：

select u.usernamte, d.date
from usertable u
cross join (select distinct date as dt from usagetable) d
left join usagetable ut on ut.username = u.username and ut.date = d.dt
where ut.username is null
order by d.date, u.username

uj5u.com熱心網友回復：

這里的問題是您沒有僅包含日期的表格。所以你需要自己生成。

這里有兩種解決方案...您要查找所有在指定范圍內沒有使用過的用戶。或者您想查找在其他用戶使用系統的日子里沒有使用的用戶。

這可能會令人困惑...但基本上...如果沒有人使用 on 2022-02-01，并且您嘗試使用 aDISTINCT來獲取該日期串列...那么您將不會回傳當天的任何行，而實際上want 是所有用戶的串列。

我將根據我認為最有可能的情況提供答案，即查找在指定日期范圍內沒有使用的所有用戶。

我要做的第一件事是生成一個表格，其中包含我想要檢查的每一天的一行。

DECLARE @DateRangeStart date = '2022-02-01',
        @DateRangeEnd   date = '2022-02-03';

-- FYI, this tally table generator code only produces 101 records total
IF OBJECT_ID('tempdb..#daterange','U') IS NOT NULL DROP TABLE #daterange; --SELECT * FROM #daterange
WITH c1 AS (SELECT x.x FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) x(x))           -- 10
    , c2(x) AS (SELECT 1 FROM c1 x CROSS JOIN c1 y)                                         -- 10 * 10
    , c3(rn) AS (SELECT 0 UNION ALL SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) FROM c2) -- Add zero record, and row numbers
SELECT DateValue = DATEADD(DAY, x.rn, @DateRangeStart)
INTO #daterange
FROM c3 x
WHERE x.rn <= DATEDIFF(DAY, @DateRangeStart, @DateRangeEnd)

我知道這看起來很復雜，但這只是生成數字串列的一種常用方法，有時稱為計數表。然后我用它來生成一個范圍內的所有日期。有些人喜歡使用系統表。有很多方法可以做到這一點。

主要思想是您只需要一個包含可以使用的日期值的表格。

然后查詢很簡單...

SELECT u.Username, d.DateValue
FROM #User u    
    CROSS JOIN #daterange d
WHERE NOT EXISTS (SELECT * FROM #Usage ug WHERE ug.Username = u.Username AND ug.DateValue = d.DateValue)

我將我們的日期串列交叉加入到用戶串列中。這為我們提供了用戶名 日期的所有可能組合。

然后我添加了一個NOT EXISTS()檢查，上面寫著要排除在使用表中具有該日期記錄的任何用戶。

作為參考，這是我的示例資料設定查詢：

IF OBJECT_ID('tempdb..#User','U') IS NOT NULL DROP TABLE #User; --SELECT * FROM #User
CREATE TABLE #User (
    Username    varchar(20) NOT NULL,
);

INSERT INTO #User (Username)
VALUES ('User1'), ('User2'), ('User3')

IF OBJECT_ID('tempdb..#Usage','U') IS NOT NULL DROP TABLE #Usage; --SELECT * FROM #Usage
CREATE TABLE #Usage (
    Username    varchar(20) NOT NULL,
    DateValue   date        NOT NULL,
);

INSERT INTO #Usage (Username, DateValue)
VALUES ('User1', '2022-02-01'), ('User2', '2022-02-02'), ('User1', '2022-02-03'), ('User2', '2022-02-03');

uj5u.com熱心網友回復：

日期范圍應首先在最小日期和最大日期之間得出，或者如果可以創建單獨的日期表。然后在日期和用戶表之間進行笛卡爾積，并與使用表進行左連接并在 where 子句中查找空值。我是這樣做的：

create table UserTable(Username varchar(10));
create table UsageTable(Username varchar(10), UsageDate Date);

insert into UserTable values ('User1');
insert into UserTable values ('User2');
insert into UserTable values ('User3');

insert into UsageTable values ('User1','1-FEB-2022');
insert into UsageTable values ('User2','2-FEB-2022');
insert into UsageTable values ('User1','3-FEB-2022');
insert into UsageTable values ('User2','3-FEB-2022');

commit;

with rnge as (select min(UsageDate) min_date, max(UsageDate) max_date from UsageTable),
dt as (select generate_series(min_date,max_date,'1 day') as dt from rnge),
Usr as (select Username, dt from dt, UserTable)
select Usr.* from Usr left join UsageTable usg on usr.username = usg.username
and usr.dt = usg.UsageDate
where usg.username is null; 

注意：上面的 sql 在 postgres 中作業以生成日期范圍。但是，您可以使用下面的方法在 oracle 中生成日期范圍。用下面的一些更改替換 dt 表：在 oracle 中，這是生成日期范圍的方式：

select
to_date('04-01-2016','dd-mm-yyyy')   lvl
from
(select level - 1 lvl
from
  dual
connect by
level <= (to_date('10-01-2015','dd-mm-yyyy') - to_date('04-01-2016','dd-mm-yyyy'))  1);

標籤：sql sql服务器 加入

上一篇：特殊條件的內連接

下一篇：根據服務日期加入表