sql - 用 Pivot 替换子查询
问题描述
我一直在使用输出列的扩展子查询重写查询(目前为 45 秒的海量表)。有申请人(又名 Comp)、查询和活动(又名 GS)。当申请人有某种类型的查询和与该查询类型相对应的某种类型的事件时,它们会被包含在内,因此我在联接中对条件进行了配对。
我尝试使用数据透视表重写列,但这是正确的方法吗?一个缺点是有时两种查询类型匹配一种事件类型,我不得不将它们分开。
使用的 SQL:
CREATE TABLE F_ENQUIRY (
E_KEY VARCHAR(15) NOT NULL,
E_APP_LINK VARCHAR(15) NOT NULL,
E_DATE_REG datetime NOT NULL,
E_PRIORITY_LINK int NOT NULL,
PRIMARY KEY(E_KEY)
)
GO
CREATE TABLE F_EVENTS (
EVENT_NO int,
EVENT_KEY VARCHAR(15),
EVENT_APP_LINK VARCHAR(15),
EVENT_DATE datetime,
EVENT_TYPE int,
EVENT_TYPE_LINK int,
PRIMARY KEY(EVENT_NO)
)
GO
CREATE TABLE F_APPLICANT (
"A_KEY" VARCHAR(15) NOT NULL,
PRIMARY KEY(A_KEY)
)
GO
INSERT INTO F_ENQUIRY (E_KEY,E_APP_LINK,E_DATE_REG,E_PRIORITY_LINK) VALUES
('303+1001','303+10019','2020-01-12 00:00:00.000',65)
,('303+1002','303+10019','2015-03-03 00:00:00.000',65)
,('303+1003','303+10019','2002-01-18 00:00:00.000',65)
,('303+1004','303+10029','2019-04-29 00:00:00.000',65)
,('303+1005','303+10029','2019-04-29 00:00:00.000',65)
,('303+1006','303+10029','2019-04-29 00:00:00.000',65)
,('303+1007','303+10029','2019-04-29 00:00:00.000',65)
,('303+1008','303+10029','2019-04-29 00:00:00.000',65)
,('303+1016','303+10029','2019-04-29 00:00:00.000',20)
,('303+1017','303+10029','2019-04-29 00:00:00.000',20)
,('303+1018','303+10029','2019-04-29 00:00:00.000',20)
,('303+1019','303+10019','2019-04-29 00:00:00.000',33)
,('303+1020','303+10019','2019-04-29 00:00:00.000',33)
,('303+1021','303+10019','2019-04-29 00:00:00.000',33)
GO
INSERT INTO F_EVENTS (EVENT_NO,EVENT_KEY,EVENT_APP_LINK,EVENT_DATE,EVENT_TYPE,EVENT_TYPE_LINK) VALUES
(893693,'303+1001','303+10019','2019-11-13 00:00:00.000',1308,99)
,(840905,'303+1002','303+10019','2019-06-28 00:00:00.000',1308,99)
,(893694,'303+1003','303+10019','2019-11-13 00:00:00.000',1308,99)
,(893695,'303+1004','303+10029','2019-11-13 00:00:00.000',1308,99)
,(781122,'303+1005','303+10029','2019-02-14 00:00:00.000',1308,99)
,(784170,'303+1001','303+10029','2019-02-20 00:00:00.000',1308,99)
,(788124,'303+1002','303+10029','2019-03-01 00:00:00.000',1308,99)
,(795941,'303+1003','303+10029','2019-03-25 00:00:00.000',1308,99)
,(797944,'303+1004','303+10029','2019-03-27 00:00:00.000',1308,99)
,(802037,'303+1005','303+10029','2019-03-28 00:00:00.000',1308,99)
,(821342,'303+1013','303+10029','2019-05-10 00:00:00.000',1308,130)
,(826063,'303+1014','303+10029','2019-05-29 00:00:00.000',1308,130)
,(828412,'303+1015','303+10029','2019-05-30 00:00:00.000',1308,130)
,(831525,'303+1016','303+10029','2019-06-07 00:00:00.000',1308,130)
,(833202,'303+1017','303+10029','2019-06-11 00:00:00.000',1308,130)
,(837145,'303+1019','303+10019','2019-06-21 00:00:00.000',1308,143)
,(842768,'303+1020','303+10019','2019-07-19 00:00:00.000',1308,143)
,(845624,'303+1021','303+10019','2019-07-30 00:00:00.000',1308,143)
,(848579,'303+1022','303+10019','2019-08-06 00:00:00.000',1308,143)
,(851026,'303+1023','303+10019','2019-08-13 00:00:00.000',1308,143)
GO
INSERT INTO F_APPLICANT (a_key) VALUES
('303+10019'),
('303+10029')
GO
select
distinct a_key as Comp_Link,
EandGS.GS_Type_Link,
EandGS.GS_Date,
(
select
max(e_date_reg)
from
f_enquiry
where
e_app_link = a_key
and e_priority_link in (69,65)
and e_date_reg >= '01/APR/2019'
and e_date_reg <= '31/MAR/2020') as "A Date",
(
select
max(e_date_reg)
from
f_enquiry
where
e_app_link = a_key
and e_priority_link in (34)
and e_date_reg >= '01/APR/2019'
and e_date_reg <= '31/MAR/2020') as "B Date",
(
select
max(e_date_reg)
from
f_enquiry
where
e_app_link = a_key
and e_priority_link in (27,
50)
and e_date_reg >= '01/APR/2019'
and e_date_reg <= '31/MAR/2020') as "C Date",
(
select
max(e_date_reg)
from
f_enquiry
where
e_app_link = a_key
and e_priority_link in (28)
and e_date_reg >= '01/APR/2019'
and e_date_reg <= '31/MAR/2020') as "D Date",
(
select
max(e_date_reg)
from
f_enquiry
where
e_app_link = a_key
and e_priority_link in (26)
and e_date_reg >= '01/APR/2019'
and e_date_reg <= '31/MAR/2020') as "E Date",
(
select
max(e_date_reg)
from
f_enquiry
where
e_app_link = a_key
and e_priority_link in (20)
and e_date_reg >= '01/APR/2019'
and e_date_reg <= '31/MAR/2020') as "F Date",
(
select
max(e_date_reg)
from
f_enquiry
where
e_app_link = a_key
and e_priority_link in (36)
and e_date_reg >= '01/APR/2019'
and e_date_reg <= '31/MAR/2020') as "G Date",
(
select
max(e_date_reg)
from
f_enquiry
where
e_app_link = a_key
and e_priority_link in (33)
and e_date_reg >= '01/APR/2019'
and e_date_reg <= '31/MAR/2020') as "H Date",
(
select
max(e_date_reg)
from
f_enquiry
where
e_app_link = a_key
and e_priority_link in (23)
and e_date_reg >= '01/APR/2019'
and e_date_reg <= '31/MAR/2020') as "I Date",
(
select
max(e_date_reg)
from
f_enquiry
where
e_app_link = a_key
and e_priority_link in (25)
and e_date_reg >= '01/APR/2019'
and e_date_reg <= '31/MAR/2020') as "J Date",
(
select
max(e_date_reg)
from
f_enquiry
where
e_app_link = a_key
and e_priority_link in (24,
43)
and e_date_reg >= '01/APR/2019'
and e_date_reg <= '31/MAR/2020') as "K Date",
(
select
max(e_date_reg)
from
f_enquiry
where
e_app_link = a_key
and e_priority_link in (72)
and e_date_reg >= '01/APR/2019'
and e_date_reg <= '31/MAR/2020') as "L Date",
(
select
max(e_date_reg)
from
f_enquiry
where
e_app_link = a_key
and e_priority_link in (22,
14)
and e_date_reg >= '01/APR/2019'
and e_date_reg <= '31/MAR/2020') as "M Date",
(
select
max(e_date_reg)
from
f_enquiry
where
e_app_link = a_key
and e_priority_link in (21)
and e_date_reg >= '01/APR/2019'
and e_date_reg <= '31/MAR/2020') as "N Date",
(
select
max(e_date_reg)
from
f_enquiry
where
e_app_link = a_key
and e_priority_link in (30)
and e_date_reg >= '01/APR/2019'
and e_date_reg <= '31/MAR/2020') as "O Date",
(
select
max(e_date_reg)
from
f_enquiry
where
e_app_link = a_key
and e_priority_link in (73)
and e_date_reg >= '01/APR/2019'
and e_date_reg <= '31/MAR/2020') as "P Date"
from
f_enquiry
inner join f_applicant on
e_app_link = a_key
inner join (
select
a_key as Comp_Link,
e_priority_link as Enq_Type,
e_date_reg as Enq_Date_Reg,
event_type_link as GS_Type_Link,
event_no as GS_No,
event_date as GS_Date
from
f_applicant
inner join f_enquiry on
e_app_link = a_key
inner join f_events on
event_app_link = a_key
where
e_date_reg >= '01/APR/2019'
and e_date_reg <= '31/MAR/2020'
and event_date >= '01/APR/2019'
and event_date <= '31/MAR/2020'
and event_type = 1308
and (
(e_priority_link in (69,65) and event_type_link = 99)
or (e_priority_link = 34 and event_type_link = 144)
or (e_priority_link in (50,27) and event_type_link in (400067,80))
or (e_priority_link = 28 and event_type_link = 136)
or (e_priority_link = 26 and event_type_link = 135)
or (e_priority_link = 20 and event_type_link = 130)
or (e_priority_link = 36 and event_type_link = 146)
or (e_priority_link = 33 and event_type_link = 143)
or (e_priority_link = 23 and event_type_link = 133)
or (e_priority_link in (25,43) and event_type_link = 82)
or (e_priority_link in (24,43) and event_type_link = 134)
or (e_priority_link = 72 and event_type_link = 400297)
or (e_priority_link in (14,22) and event_type_link in (400071,84))
or (e_priority_link = 30 and event_type_link = 132)
or (e_priority_link = 21 and event_type_link = 131)
or (e_priority_link = 73 and event_type_link = 140)
)
) as EandGS on Comp_Link = a_key
GO
WITH EandGS AS (
select
a_key as Comp_Link,
event_type_link as GS_Type_Link,
event_date as GS_Date,
event_no as GS_No,
e_priority_link as Enq_Type,
e_date_reg as Enq_Date_Reg
from
f_applicant
inner join f_enquiry on
e_app_link = a_key
inner join f_events on
event_app_link = a_key
where
e_date_reg >= '01/APR/2019'
and e_date_reg <= '31/MAR/2020'
and event_date >= '01/APR/2019'
and event_date <= '31/MAR/2020'
and event_type = 1308
and ((e_priority_link in (69,65) and event_type_link = 99)
or (e_priority_link = 34 and event_type_link = 144)
or (e_priority_link in (50,27) and event_type_link in (400067,80))
or (e_priority_link = 28 and event_type_link = 136)
or (e_priority_link = 26 and event_type_link = 135)
or (e_priority_link = 20 and event_type_link = 130)
or (e_priority_link = 36 and event_type_link = 146)
or (e_priority_link = 33 and event_type_link = 143)
or (e_priority_link = 23 and event_type_link = 133)
or (e_priority_link in (25,43) and event_type_link = 82)
or (e_priority_link in (24,43) and event_type_link = 134)
or (e_priority_link = 72 and event_type_link = 400297)
or (e_priority_link in (14,22) and event_type_link in (400071,84))
or (e_priority_link = 30 and event_type_link = 132)
or (e_priority_link = 21 and event_type_link = 131)
or (e_priority_link = 73 and event_type_link = 140))
),
E AS (
select Comp_Link,Enq_Type,Enq_Date_Reg from EandGS --Used by pivot table to stop duplicate rows caused by columns not included
),
EP AS (
select Comp_Link,[14],[20],[21],[22],[23],[24],[25],[26],[27],[28],[30],[33],[34],[36],[43],[50],[65],[69],[72],[73]
from
E PIVOT (max(E.Enq_Date_Reg) FOR E.Enq_Type IN ([14],[20],[21],[22],[23],[24],[25],[26],[27],[28],[30],[33],[34],[36],[43],[50],[65],[69],[72],[73])) as pvt
)
select distinct EandGS.Comp_Link,GS_Type_Link,GS_Date,GS_No,[14],[20],[21],[22],[23],[24],[25],[26],[27],[28],[30],[33],[34],[36],[43],[50],[65],[69],[72],[73]
from EandGS
inner join EP on EP.Comp_Link = EandGS.Comp_Link
order by EandGS.Comp_Link asc
解决方案
推荐阅读
- c# - Get Windows environment variables passed to new process (not of current process)
- c# - 从非 UI dll 显示对话框
- python - 使用 setup.py 更新 Python 包
- python - 如何解决 ValueError: not enough values to unpack 错误
- github - npm 错误!使用 npm install 时过早关闭
- git - 我可以将“git rebase”表示为一系列精选内容吗?
- c - getnameinfo 无法执行反向 DNS
- javascript - karma-webpack 插件:捆绑文件在哪里?
- python-3.x - 使用 python3 为热图添加注释
- slack - 用于消息格式化的松弛块