时间戳

首页 > 解决方案 > SQL 使用存储过程组合垂直表中的值

sql-server - SQL 使用存储过程组合垂直表中的值

问题描述

（我正在尝试在 SQL Server 存储过程中执行此操作）

我有一个垂直结构的临时表，我需要从不同行的字段中计算一个值

        DECLARE @tempTable TABLE(

        nodeId INT IDENTITY(1,1),
        parentNodeId INT,
        fieldName NVARCHAR(255),
        fieldValue decimal(7,2)
    );

以下是一些示例数据：

        INSERT INTO @tempTable(parentNodeId,fieldName,fieldValue) VALUES(0,'deliveryLines',null)
    INSERT INTO @tempTable(parentNodeId,fieldName,fieldValue) VALUES(1,'deliveryLine',null)
    INSERT INTO @tempTable(parentNodeId,fieldName,fieldValue) VALUES(2,'unitPrice',12)
    INSERT INTO @tempTable(parentNodeId,fieldName,fieldValue) VALUES(2,'taxRate',0.5)
    INSERT INTO @tempTable(parentNodeId,fieldName,fieldValue) VALUES(2,'unitTax',0)
    INSERT INTO @tempTable(parentNodeId,fieldName,fieldValue) VALUES(1,'deliveryLine',null)
    INSERT INTO @tempTable(parentNodeId,fieldName,fieldValue) VALUES(6,'unitPrice',25)
    INSERT INTO @tempTable(parentNodeId,fieldName,fieldValue) VALUES(6,'taxRate',0.1)
    INSERT INTO @tempTable(parentNodeId,fieldName,fieldValue) VALUES(6,'unitTax',0)
    INSERT INTO @tempTable(parentNodeId,fieldName,fieldValue) VALUES(1,'deliveryLine',null)
    INSERT INTO @tempTable(parentNodeId,fieldName,fieldValue) VALUES(10,'unitPrice',333)
    INSERT INTO @tempTable(parentNodeId,fieldName,fieldValue) VALUES(10,'taxRate',0.17)
    INSERT INTO @tempTable(parentNodeId,fieldName,fieldValue) VALUES(10,'unitTax',0)

所以表格看起来像这样：

nodeId parentNodeId fieldName       fieldValue
1      0            deliveryLines   NULL
2      1            deliveryLine    NULL
3      2            unitPrice       12.00
4      2            taxRate         0.50
5      2            unitTax         0.00
6      1            deliveryLine    NULL
7      6            unitPrice       25.00
8      6            taxRate         0.10
9      6            unitTax         0.00
10     1            deliveryLine    NULL
11     10           unitPrice       333.00
12     10           taxRate         0.17
13     10           unitTax         0.00

并且 unitTax 应该是 unitPrice * taxRate（来自具有相同 parentNodeId 的前 2 行）。

因此，对于 parentNodeId = 2（例如），数据应如下所示：

3      2            unitPrice       12.00
4      2            taxRate         0.50
5      2            unitTax         6.00

我需要使用 UPDATE 设置 unitTax 值，但我不知道该怎么做。数据是从 xml 文档生成的，我无法控制结构。谁能帮我吗？

标签： sql-servertsqlsql-update

解决方案

嗨，我认为这些查询可以响应：

CREATE TABLE #tempTable(
        nodeId INT IDENTITY(1,1),
        parentNodeId INT,
        fieldName NVARCHAR(255),
        fieldValue decimal(7,2)
    );


    INSERT INTO #tempTable(parentNodeId,fieldName,fieldValue) VALUES(0,'deliveryLines',null)
    INSERT INTO #tempTable(parentNodeId,fieldName,fieldValue) VALUES(1,'deliveryLine',null)
    INSERT INTO #tempTable(parentNodeId,fieldName,fieldValue) VALUES(2,'unitPrice',12)
    INSERT INTO #tempTable(parentNodeId,fieldName,fieldValue) VALUES(2,'taxRate',0.5)
    INSERT INTO #tempTable(parentNodeId,fieldName,fieldValue) VALUES(2,'unitTax',0)
    INSERT INTO #tempTable(parentNodeId,fieldName,fieldValue) VALUES(1,'deliveryLine',null)
    INSERT INTO #tempTable(parentNodeId,fieldName,fieldValue) VALUES(6,'unitPrice',25)
    INSERT INTO #tempTable(parentNodeId,fieldName,fieldValue) VALUES(6,'taxRate',0.1)
    INSERT INTO #tempTable(parentNodeId,fieldName,fieldValue) VALUES(6,'unitTax',0)
    INSERT INTO #tempTable(parentNodeId,fieldName,fieldValue) VALUES(1,'deliveryLine',null)
    INSERT INTO #tempTable(parentNodeId,fieldName,fieldValue) VALUES(10,'unitPrice',333)
    INSERT INTO #tempTable(parentNodeId,fieldName,fieldValue) VALUES(10,'taxRate',0.17)
    INSERT INTO #tempTable(parentNodeId,fieldName,fieldValue) VALUES(10,'unitTax',0)


    SELECT * FROM #tempTable;
    SELECT parentNodeId, 
                    MAX(CASE WHEN fieldName = 'unitPrice' THEN fieldValue ELSE NULL END) AS 'unitPrice', 
                    MAX(CASE WHEN fieldName = 'taxRate' THEN fieldValue ELSE NULL END) AS 'taxRate'
         FROM #tempTable  GROUP BY parentNodeId;


    UPDATE T SET fieldValue = TT.unitPrice * TT.taxRate
    FROM #tempTable T 
        JOIN (SELECT parentNodeId, 
                    MAX(CASE WHEN fieldName = 'unitPrice' THEN fieldValue ELSE NULL END) AS 'unitPrice', 
                    MAX(CASE WHEN fieldName = 'taxRate' THEN fieldValue ELSE NULL END) AS 'taxRate'
         FROM #tempTable  GROUP BY parentNodeId) TT ON T.parentNodeId = TT.parentNodeId AND T.fieldName = 'unitTax' 



    SELECT * FROM #tempTable;
    DROP TABLE #tempTable;

我知道其他替代方法，我认为 PIVOT 更新查询也可以工作。

https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-2017

推荐阅读