这个支点能否更有效地完成？

Question

我设法找到了格式化显示的驱动程序表的解决方案。结果正是我所需要的：每个 QuoteID 一行，其中 Birthdate 和 DriverType 列由 DriverIndex 分隔。我真正的驱动程序表有数百万行。

但我不相信这是正确的出路。我觉得这很奇怪。但我不是 SQL 专家。我的问题：这可以以更有效的方式完成吗？

SQL Fiddle

MS SQL Server 2017 架构设置：

CREATE TABLE driver
([QuoteID] int, [DriverIndex] int,[Birthdate] date,[DriverType] int)
;
INSERT INTO driver
([QuoteID], [DriverIndex],[Birthdate], [DriverType])
VALUES
('72', '1','2022/01/01','11'),
('72', '2','2022/02/01','12'),
('73', '1','2022/03/01','13'),
('74', '1','2022/04/01','13'),
('73', '2','2022/05/01','10'),
('73', '3','2022/06/01','11');

驱动程序表：

| QuoteID | DriverIndex |  Birthdate | DriverType |
|---------|-------------|------------|------------|
|      72 |           1 | 2022-01-01 |         11 |
|      72 |           2 | 2022-02-01 |         12 |
|      73 |           1 | 2022-03-01 |         13 |
|      74 |           1 | 2022-04-01 |         13 |
|      73 |           2 | 2022-05-01 |         10 |
|      73 |           3 | 2022-06-01 |         11 |

查询：

with sq as(select QuoteID AS QuoteID_sq, [1] AS DriverIndex_1_DriverType , [2] AS DriverIndex_2_DriverType , [3] as DriverIndex_3_DriverType
from
( select [QuoteID], [DriverIndex],[Birthdate], [DriverType] from driver) src
pivot
( max([DriverType]) for DriverIndex in ([1], [2], [3]) ) piv),
sq2 as(select QuoteID as QuoteID_sq2, [1] AS DriverIndex_1_Birthdate , [2] AS DriverIndex_2_Birthdate , [3] as DriverIndex_3_Birthdate
from
( select [QuoteID], [DriverIndex],[Birthdate], [DriverType] from driver) src
pivot
( max([Birthdate]) for DriverIndex in ([1], [2], [3]) ) piv),
sq3 as(Select * from sq,sq2 Where sq.QuoteID_sq=sq2.QuoteID_sq2)
Select QuoteID_sq as QuoteID, max([DriverIndex_1_DriverType]) AS DriverIndex_1_DriverType,MAX([DriverIndex_2_DriverType]) AS DriverIndex_2_DriverType,Max([DriverIndex_3_DriverType]) AS DriverIndex_3_DriverType ,
max([DriverIndex_1_Birthdate]) AS DriverIndex_1_Birthdate , max([DriverIndex_2_Birthdate]) AS DriverIndex_2_Birthdate , max([DriverIndex_3_Birthdate]) as DriverIndex_3_Birthdate
from sq3
group by QuoteID_sq

结果：

| QuoteID | DriverIndex_1_DriverType | DriverIndex_2_DriverType | DriverIndex_3_DriverType | DriverIndex_1_Birthdate | DriverIndex_2_Birthdate | DriverIndex_3_Birthdate |
|---------|--------------------------|--------------------------|--------------------------|-------------------------|-------------------------|-------------------------|
|      72 |                       11 |                       12 |                   (null) |              2022-01-01 |              2022-02-01 |                  (null) |
|      73 |                       13 |                       10 |                       11 |              2022-03-01 |              2022-05-01 |              2022-06-01 |
|      74 |                       13 |                   (null) |                   (null) |              2022-04-01 |                  (null) |                  (null) |

Answer 1

您可以通过仅使用条件聚合来简化此过程。

SELECT QuoteID
, MAX(CASE WHEN DriverIndex = 1 THEN DriverType END) AS DriverIndex_1_DriverType
, MAX(CASE WHEN DriverIndex = 2 THEN DriverType END) AS DriverIndex_2_DriverType
, MAX(CASE WHEN DriverIndex = 3 THEN DriverType END) AS DriverIndex_3_DriverType
, MAX(CASE WHEN DriverIndex = 1 THEN Birthdate END) AS DriverIndex_1_Birthdate
, MAX(CASE WHEN DriverIndex = 2 THEN Birthdate END) AS DriverIndex_2_Birthdate
, MAX(CASE WHEN DriverIndex = 3 THEN Birthdate END) AS DriverIndex_3_Birthdate
来自驱动程序
按报价 ID 分组
按报价 ID 排序；

<表类=“s-表”>
<标题>

报价ID
DriverIndex_1_DriverType
DriverIndex_2_DriverType
DriverIndex_3_DriverType
DriverIndex_1_Birthdate
DriverIndex_2_Birthdate
DriverIndex_3_Birthdate

<正文>

72
11
12
空
2022-01-01
2022-02-01
空

73
13
10
11
2022-03-01
2022-05-01
2022-06-01

74
13
空
空
2022-04-01
空
空

db<>fiddle 此处