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

Question

我设法找到了格式化显示的 driver 表的解决方案。结果正是我需要的:每个 QuoteID 一行，Birthdate 和 DriverType 列由 DriverIndex 分隔.我的真实 driver 表有数百万行。

但我不相信这是要走的路。这对我来说似乎很奇怪。但我不是 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

Results :

| 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
FROM driver
GROUP BY QuoteID
ORDER BY QuoteID;

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

db<>fiddle 上的演示 here