sql - 带有窗口函数的多个 Sqlite3 连接
问题描述
我有三个要加入的表。
- 主客户表。我还想根据 FLAG 列排除“TERM”记录。
- BSP 表 - 与客户端表匹配，按日期最近。
- HRC 表 - 与客户表匹配，按日期最近。
这应该在他们自己的列中包含最近 BSP 和最近 HRC 日期的所有非长期客户。
我无法正确连接第三张桌子。
我试过的：
SELECT *
from
clients
left join
(
select
Hipaa_Short b_h, BSP_Date,
row_number() over (PARTITION by Hipaa_Short
ORDER by BSP_Date DESC)
as rn_b
from BSP
) B
on Clients.Hipaa_Short = B.b_h
where Clients.Status is not "TERM"
;
编辑
改为这个，但不能只从 BSP 和 HRC 获得第一条记录：
SELECT *
from
clients
left join
(
select
Hipaa_Short b_h, BSP_Date,
row_number() over (PARTITION by Hipaa_Short
ORDER by BSP_Date DESC)
as rn_b
from BSP
) B
on Clients.Hipaa_Short = B.b_h
left join
(
select
Hipaa_Short h_h, HRC_Date,
row_number() over (PARTITION by Hipaa_Short
ORDER by HRC_Date DESC)
as rn_h
from HRC
) H
on Clients.Hipaa_Short = H.h_h
where Clients.Status is not "TERM"
;
表设置
CREATE TABLE Clients (
"id_pk" INTEGER,
Hipaa_Short TEXT,
Status TEXT,
Start_Date TEXT,
PRIMARY KEY("id_pk")
);
INSERT INTO Clients (Hipaa_Short, Status, Start_Date) VALUES
("Aaliyah", "" ,"2021-02-01")
,("Aiden", "" ,"2020-12-01")
,("Amelia", "TERM" ,"2021-08-01")
,("Aria", "" ,"2021-07-01")
,("Ava", "" ,"2021-06-01")
,("Avery", "" ,"2021-03-01")
,("Benjamin","" ,"2021-10-01")
,("Bob","" ,"2021-02-01")
,("Caden", "" ,"2021-02-01")
,("Camilla", "" ,"2021-08-01")
,("Carter", "TERM" ,"2021-01-01")
,("Charlotte","" ,"2021-07-01")
,("Eliana", "" ,"2021-05-01")
,("Elijah", "" ,"2021-10-01")
,("Emma", "" ,"2021-02-01")
,("Ethan", "" ,"2020-12-01")
,("Evelyn", "" ,"2021-03-01")
,("Everly", "" ,"2021-01-01")
,("Grayson","TERM" ,"2021-08-01")
,("Isabella","" ,"2021-07-01")
,("Jackson","" ,"2021-05-01")
,("James", "" ,"2021-07-01")
,("Jayden", "" ,"2021-07-01")
,("Layla", "" ,"2021-01-01")
,("Liam", "" ,"2021-01-01")
,("Logan", "" ,"2020-03-01")
,("Lucas", "" ,"2020-05-01")
,("Luna", "" ,"2021-06-01")
,("Mason", "" ,"2021-08-01")
,("Mateo", "" ,"2021-05-01")
,("Mia", "" ,"2020-11-01")
,("Michael","" ,"2021-10-01")
,("Mila", "" ,"2021-05-01")
,("Noah", "" ,"2021-07-01")
,("Oliver", "" ,"2021-01-01")
,("Olivia", "" ,"2021-01-01")
,("Riley", "" ,"2021-08-01")
,("Sebastian","" ,"2021-02-01")
,("Sophia","" ,"2021-02-01")
,("Zoe", "" ,"2021-05-01")
;
create table BSP (
"id_pk" INTEGER,
Hipaa_Short TEXT,
BSP_Date TEXT,
PRIMARY KEY("id_pk")
);
INSERT INTO BSP (Hipaa_Short, BSP_Date)
VALUES
("Ava" ,"2019-11-30")
,("Eliana" ,"2020-07-31")
,("Ethan" ,"2020-08-28")
,("Grayson" ,"2019-12-15")
,("Jackson" ,"2019-10-20")
,("Jackson" ,"2020-10-20")
,("Layla" ,"2019-04-13")
,("Luna" ,"2019-04-12")
,("Mateo" , "2020-09-14");
create table HRC (
"id_pk" INTEGER,
Hipaa_Short TEXT,
HRC_Date TEXT,
PRIMARY KEY("id_pk")
);
INSERT INTO HRC (Hipaa_Short, HRC_Date)
VALUES
("Ava" , "2020-08-21")
,("Eliana" , "2020-07-31")
,("Ethan" , "2019-07-24")
,("Grayson" , "2020-02-07")
,("Jackson" , "2020-09-14")
,("Layla" , "2019-11-30")
,("Mila" , "2019-01-01");
期望的输出
| id_pk | Hipaa_Short | Status | Start_Date | b_h | BSP_Date | rn_b | HRC_Date |
| ----- | ----------- | ------ | ---------- | ------- | ---------- | ---- | ---------- |
| 1 | Aaliyah | | 2021-02-01 | | | | |
| 2 | Aiden | | 2020-12-01 | | | | |
| 4 | Aria | | 2021-07-01 | | | | |
| 5 | Ava | | 2021-06-01 | Ava | 2019-11-30 | 1 | 2020-08-21 |
| 6 | Avery | | 2021-03-01 | | | | |
| 7 | Benjamin | | 2021-10-01 | | | | |
| 8 | Bob | | 2021-02-01 | | | | |
| 9 | Caden | | 2021-02-01 | | | | |
| 10 | Camilla | | 2021-08-01 | | | | |
| 12 | Charlotte | | 2021-07-01 | | | | |
| 13 | Eliana | | 2021-05-01 | Eliana | 2020-07-31 | 1 | 2020-07-31 |
| 14 | Elijah | | 2021-10-01 | | | | |
| 15 | Emma | | 2021-02-01 | | | | |
| 16 | Ethan | | 2020-12-01 | Ethan | 2020-08-28 | 1 | 2019-07-24 |
| 17 | Evelyn | | 2021-03-01 | | | | |
| 18 | Everly | | 2021-01-01 | | | | |
| 20 | Isabella | | 2021-07-01 | | | | |
| 21 | Jackson | | 2021-05-01 | Jackson | 2020-10-20 | 1 | |
| 22 | James | | 2021-07-01 | | | | |
| 23 | Jayden | | 2021-07-01 | | | | |
| 24 | Layla | | 2021-01-01 | Layla | 2019-04-13 | 1 | 2019-11-30 |
| 25 | Liam | | 2021-01-01 | | | | |
| 26 | Logan | | 2020-03-01 | | | | |
| 27 | Lucas | | 2020-05-01 | | | | |
| 28 | Luna | | 2021-06-01 | Luna | 2019-04-12 | 1 | |
| 29 | Mason | | 2021-08-01 | | | | |
| 30 | Mateo | | 2021-05-01 | Mateo | 2020-09-14 | 1 | |
| 31 | Mia | | 2020-11-01 | | | | |
| 32 | Michael | | 2021-10-01 | | | | |
| 33 | Mila | | 2021-05-01 | | | | 2019-01-01 |
| 34 | Noah | | 2021-07-01 | | | | |
| 35 | Oliver | | 2021-01-01 | | | | |
| 36 | Olivia | | 2021-01-01 | | | | |
| 37 | Riley | | 2021-08-01 | | | | |
| 38 | Sebastian | | 2021-02-01 | | | | |
| 39 | Sophia | | 2021-02-01 | | | | |
| 40 | Zoe | | 2021-05-01 | | | | |
https://www.db-fiddle.com/f/aQJv5HTj5vsApmPEUBR2n/4
解决方案
只需添加每个计算 ROW_NUMBER到的ON子句等于 1：
SELECT c.id_pk, c.Hipaa_Short, c.Status, c.Start_Date,
B.b_h, B.BSP_Date, H.h_h, H.HRC_Date
FROM clients c
LEFT JOIN
(SELECT
Hipaa_Short AS b_h
, BSP_Date
, ROW_NUMBER() OVER (PARTITION by Hipaa_Short
ORDER by BSP_Date DESC) AS rn_b
FROM BSP
) B
ON c.Hipaa_Short = B.b_h
AND B.rn_b = 1 -- ADDED CONDITION
LEFT JOIN
(SELECT
Hipaa_Short AS h_h
, HRC_Date
, ROW_NUMBER() OVER (PARTITION by Hipaa_Short
ORDER by HRC_Date DESC) AS rn_h
FROM HRC
) H
ON c.Hipaa_Short = H.h_h
AND H.rn_h = 1 -- ADDED CONDITION
WHERE c.Status IS NOT 'TERM';
推荐阅读
- sass - VS 2019 Web编译多个scss文件为一个css文件
- sql - 带有 SQL 注入保护的简单查询比没有的要花费更长的时间
- java - 具有抽象的构建器模式，无需覆盖所有常用方法
- java - 使用串行监视器作为 LibGDX 游戏的按钮输入
- r - 以 Markdown pdf 格式导出数据透视表 R
- python - Python中的自定义圆形函数
- javascript - 如何在父子com[onents使用vue3组合API绑定两个输入
- qt - Qt Qml ChartView 左对齐
- python - 尝试将 csv 文件从 url 插入到 mongodb 时出现 HTTP 错误
- reactjs - 如何在打字稿中导入 json 文件？