时间戳

首页 > 解决方案 > 以序列顺序透视数据并按值分组

sql - 以序列顺序透视数据并按值分组

问题描述

在 SQL Server 数据仓库表中，按给定详细信息的顺序查找 V、T、C group by Shipment_id order by TASK_SEQUENCE_NUMBER，其中的顺序TASK_TYPE是 V，然后是 T，然后是 C。

如果 V,T,C 依次没有值，则该值可以为 0 如果 V,T,C 不止一次，我们可以将这些值相加并显示

下面是具有预期结果的表模式和数据，必须将其合并到表中具有更多列的存储过程中Task_Main。

CREATE TABLE [dbo].[Task_Detail]
(
    [Task_Sid] [int] NULL,      
    [Shipment_ID] [decimal](18, 0) NULL,
    [TASK_SEQUENCE_No] [decimal](18, 0) NULL,
    [TASK_TYPE] [varchar](1) NULL,      
    [TASK_DURATION] [decimal](18, 0) NULL,      
    [LOCATION_CODE] [varchar](15) NULL,         
    [TaskStart] [bigint] NULL    
) ON [PRIMARY]

CREATE TABLE [dbo].[Task_Header]
(
    [Task_Sno] [int] NULL,      
    [Shipment_ID] [decimal](18, 0) NULL,
    [Vehicle_Id] [nchar](10) NULL,      
    [DepotVisitStartTime] [datetime2](7) NULL,      
    [Time_V] [bigint] NULL,         
    [Time_T] [bigint] NULL,     
    [Time_C] [bigint] NULL     
) ON [PRIMARY]


INSERT [dbo].[Task_Detail] ([Task_Sid], [Shipment_ID], [TASK_SEQUENCE_No], [TASK_TYPE], [TASK_DURATION], [LOCATION_CODE], [TaskStart]) 
VALUES (1, CAST(1 AS Decimal(18, 0)), CAST(1 AS Decimal(18, 0)), N'K', CAST(20 AS Decimal(18, 0)), N'LC39', 20),
       (2, CAST(1 AS Decimal(18, 0)), CAST(7 AS Decimal(18, 0)), N'L', CAST(21 AS Decimal(18, 0)), N'10019514', 21),
       (3, CAST(1 AS Decimal(18, 0)), CAST(3 AS Decimal(18, 0)), N'L', CAST(58 AS  Decimal(18, 0)), N'LC38', 58),
       (4, CAST(1 AS Decimal(18, 0)), CAST(6 AS Decimal(18, 0)), N'C', CAST(10 AS Decimal(18, 0)), NULL, 10),
       (5, CAST(1 AS Decimal(18, 0)), CAST(8 AS Decimal(18, 0)), N'V', CAST(30 AS Decimal(18, 0)), N'10019514', 30),
       (6, CAST(1 AS Decimal(18, 0)), CAST(10 AS Decimal(18, 0)), N'C', CAST(11 AS Decimal(18, 0)), N'RJC', 11),
       (7, CAST(1 AS Decimal(18, 0)), CAST(9 AS Decimal(18, 0)), N'T', CAST(19 AS Decimal(18, 0)), N'10019027', 19),
       (8, CAST(2 AS Decimal(18, 0)), CAST(7 AS Decimal(18, 0)), N'V', CAST(29 AS Decimal(18, 0)), N'10018952', 29),
       (9, CAST(2 AS Decimal(18, 0)), CAST(4 AS Decimal(18, 0)), N'K', CAST(20 AS Decimal(18, 0)), NULL, 20),
       (10, CAST(2 AS Decimal(18, 0)), CAST(5 AS Decimal(18, 0)), N'V', CAST(0 AS Decimal(18, 0)), N'302', 0),
       (11, CAST(3 AS Decimal(18, 0)), CAST(12 AS Decimal(18, 0)), N'T', CAST(19 AS Decimal(18, 0)), N'10019514', 19),
       (12, CAST(3 AS Decimal(18, 0)), CAST(13 AS Decimal(18, 0)), N'E', CAST(11 AS Decimal(18, 0)), N'10019514', 11),
       (13, CAST(2 AS Decimal(18, 0)), CAST(3 AS Decimal(18, 0)), N'V', CAST(32 AS Decimal(18, 0)), N'', 32),
       (14, CAST(3 AS Decimal(18, 0)), CAST(4 AS Decimal(18, 0)), N'K', CAST(20 AS Decimal(18, 0)), N'10018952', 20),
       (15, CAST(3 AS Decimal(18, 0)), CAST(5 AS Decimal(18, 0)), N'V', CAST(0 AS Decimal(18, 0)), N'LC57', 0),
       (16, CAST(3 AS Decimal(18, 0)), CAST(8 AS Decimal(18, 0)), N'T', CAST(19 AS Decimal(18, 0)), N'10019514', 19),
       (17, CAST(3 AS Decimal(18, 0)), CAST(9 AS Decimal(18, 0)), N'C', CAST(10 AS Decimal(18, 0)), N'10019027', 10),
       (18, CAST(3 AS Decimal(18, 0)), CAST(3 AS Decimal(18, 0)), N'L', CAST(21 AS Decimal(18, 0)), N'LC38', 21),
       (19, CAST(3 AS Decimal(18, 0)), CAST(11 AS Decimal(18, 0)), N'V', CAST(30 AS Decimal(18, 0)), N'10019514', 30),
       (20, CAST(3 AS Decimal(18, 0)), CAST(10 AS Decimal(18, 0)), N'L', CAST(21 AS Decimal(18, 0)), N'10019514', 21),
       (21, CAST(3 AS Decimal(18, 0)), CAST(1 AS Decimal(18, 0)), N'T', CAST(19 AS Decimal(18, 0)), N'LC39', 19),
       (22, CAST(3 AS Decimal(18, 0)), CAST(2 AS Decimal(18, 0)), N'C', CAST(10 AS Decimal(18, 0)), N'302', 10),
       (23, CAST(1 AS Decimal(18, 0)), CAST(2 AS Decimal(18, 0)), N'V', CAST(0 AS Decimal(18, 0)), N'302', 0),
       (24, CAST(1 AS Decimal(18, 0)), CAST(4 AS Decimal(18, 0)), N'V', CAST(29 AS Decimal(18, 0)), N'10018952', 29),
       (25, CAST(1 AS Decimal(18, 0)), CAST(5 AS Decimal(18, 0)), N'T', CAST(19 AS Decimal(18, 0)), N'LC57', 19)
, (26, CAST(3 AS Decimal(18, 0)), CAST(6 AS Decimal(18, 0)), N'L', CAST(58 AS Decimal(18, 0)), N'10019514', 58)
, (27, CAST(3 AS Decimal(18, 0)), CAST(7 AS Decimal(18, 0)), N'V', CAST(29 AS Decimal(18, 0)), N'10019514', 29)
, (28, CAST(2 AS Decimal(18, 0)), CAST(6 AS Decimal(18, 0)), N'L', CAST(58 AS Decimal(18, 0)), N'10018952', 58)
, (29, CAST(2 AS Decimal(18, 0)), CAST(2 AS Decimal(18, 0)), N'L', CAST(35 AS Decimal(18, 0)), NULL, 35)



INSERT [dbo].[Task_Header] ([Task_Sno], [Shipment_ID], [Vehicle_Id], [DepotVisitStartTime], [Time_V], [Time_T], [Time_C]) VALUES 
(1, CAST(1 AS Decimal(18, 0)), N'TN1       ', CAST(N'2019-02-15T07:25:33.0000000' AS DateTime2), NULL, NULL, NULL)
,(2, CAST(1 AS Decimal(18, 0)), N'TN1       ', CAST(N'2019-02-15T07:25:33.0000000' AS DateTime2), NULL, NULL, NULL)
,(3, CAST(1 AS Decimal(18, 0)), N'TN1       ', CAST(N'2019-02-15T07:25:33.0000000' AS DateTime2), NULL, NULL, NULL)
,(4, CAST(1 AS Decimal(18, 0)), N'TN1       ', CAST(N'2019-02-15T07:25:33.0000000' AS DateTime2), NULL, NULL, NULL)
,(5, CAST(2 AS Decimal(18, 0)), N'KA2       ', CAST(N'2019-02-15T06:12:52.0000000' AS DateTime2), NULL, NULL, NULL)
,(6, CAST(2 AS Decimal(18, 0)), N'KA2       ', CAST(N'2019-02-15T06:12:52.0000000' AS DateTime2), NULL, NULL, NULL)
,(7, CAST(3 AS Decimal(18, 0)), N'AP3       ', CAST(N'2019-02-15T06:32:52.0000000' AS DateTime2), NULL, NULL, NULL)
,(8, CAST(3 AS Decimal(18, 0)), N'AP3       ', CAST(N'2019-02-15T06:32:52.0000000' AS DateTime2), NULL, NULL, NULL)
,(9, CAST(3 AS Decimal(18, 0)), N'AP3       ', CAST(N'2019-02-15T13:12:21.0000000' AS DateTime2), NULL, NULL, NULL)

带有过滤器的预期结果：

按 Shipment_ID 对数据排序，TASK_SEQUENCE_NUMBER 过滤器，其中 TaskMaster 和 TaskDetails 的 Location_code <> 'RJC' Join 是 Shipment_id TASK_TYPE 应该是给定序列中的 V ，下一个立即与 T ，下一个立即与 C 只考虑输出

示例数据：Shipment_ID 1 的结果，我们有列 V：59，这是给定 Shipment_Id 结果数据的“V”值的总和，按 shipping_id 分组，即 sequence_number 4 和 8 = 29+30 = 59 的总和 T 列是出现在 V 之后的序列中，它是序列号 5 和 9 的总和 = 19 +19 = 38 列 C 出现在 T 之后的序列中，它是序列号 6 和 10 的总和 = 10 +11 = 21 对于发货 2，我们没有任务类型在 VTC Secquence 对于发货 3，我们在 VTC Secquence 中的任务类型为 no 7 ,8 , 9 ，其值为 29 , 19, 10 ，只有一次，该值被发送到主表

必须考虑的带有标记的样本数据

Expected Result
-----------------------------
Sno Shipment_ID Vehicle ID  DepotVisitStartTime         V   T   C
1   1           TN1         2019-02-15 07:25:33.0000000 59  38  21
2   1           TN1         2019-02-15 07:25:33.0000000 59  38  21
3   1           TN1         2019-02-15 07:25:33.0000000 59  38  21
4   1           TN1         2019-02-15 07:25:33.0000000 59  38  21
5   2           KA2         2019-02-15 06:12:52.0000000 0   0   0
6   2           KA2         2019-02-15 06:12:52.0000000 0   0   0
7   3           AP3         2019-02-15 06:32:52.0000000 29  19  10
8   3           AP3         2019-02-15 06:32:52.0000000 29  19  10
9   3           AP3         2019-02-15 13:12:21.0000000 29  19  10

这些列是下面帖子的延续，我希望有一个存储过程来将数据插入 Task_Master 表 [在同一表中为给定 ID 汇总日期和数量

标签： sqlsql-servertsqlsql-server-2008

解决方案

这会产生您想要的输出select：

select h.*, coalesce(d.v, 0) as v,
       coalesce(d.t, 0) as t, coalesce(d.c, 0) as c
from task_header h outer apply
     (select sum(dv.task_duration) as v,
             sum(dt.task_duration) as t,
             sum(dc.task_duration) as c
      from task_detail dv join
           task_detail dt
           on dt.Shipment_ID = dv.Shipment_ID and dt.TASK_SEQUENCE_No = dv.TASK_SEQUENCE_No + 1 join
           task_detail dc
           on dc.Shipment_ID = dt.Shipment_ID and dc.TASK_SEQUENCE_No = dt.TASK_SEQUENCE_No + 1 
      where dv.Shipment_ID = h.Shipment_ID and dv.task_type = 'V' and dt.task_type = 'T' and dc.task_type = 'C'
     ) d;

这会更新：

update h
    set time_v = coalesce(d.v, 0),
        time_t = coalesce(d.t, 0),
        time_c = coalesce(d.c, 0)
from task_header h outer apply
     (select sum(dv.task_duration) as v,
             sum(dt.task_duration) as t,
             sum(dc.task_duration) as c
      from task_detail dv join
           task_detail dt
           on dt.Shipment_ID = dv.Shipment_ID and dt.TASK_SEQUENCE_No = dv.TASK_SEQUENCE_No + 1 join
           task_detail dc
           on dc.Shipment_ID = dt.Shipment_ID and dc.TASK_SEQUENCE_No = dt.TASK_SEQUENCE_No + 1 
      where dv.Shipment_ID = h.Shipment_ID and dv.task_type = 'V' and dt.task_type = 'T' and dc.task_type = 'C'
     ) d;

这是一个 db<>fiddle。

推荐阅读