SQL Server中对比匹配表行时如何正确显示负数值

核心修改逻辑 #

原有逻辑已经完成了按首行分组拆分子行数量的功能，仅需新增分组首行负号同步的逻辑即可：读取每个分组首行（InvoiceDate非空行）的Quantity字段符号，将该符号应用到当前分组下所有关联的子行数量上。

完整可运行代码 #

DECLARE @tmpTable TABLE
(
    RowID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    InvoiceDate NVARCHAR(20),
    Description NVARCHAR(MAX),
    Quantity NVARCHAR(20)
)
INSERT INTO @tmpTable
SELECT * FROM (VALUES
('07/18/2021','MedName','-18'),
(NULL,'18 Patient1',''),
('07/18/2021','MedName','-5'),
(NULL,'2 Patient2',''),
(NULL,'3 Patient3',''),
('07/18/2021','MedName','7'),
(NULL,'1 Patient2',''),
(NULL,'6 Patient3','')
) AS temp
(InvoiceDate,
    Description,
    Quantity)

SELECT * FROM @tmpTable

DECLARE @tblTmp TABLE
    (
        RowID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
        InvoiceDate NVARCHAR(20),
        Description NVARCHAR(MAX),
        Quantity NVARCHAR(20)
    )
    DECLARE @tblTmp1 TABLE
    (
        [RowID] INT NOT NULL PRIMARY KEY IDENTITY(1,1),
        [InvoiceDate] DATETIME,
        [MedDescription] [NVARCHAR](MAX),
        [Quantity] [DECIMAL](18,2)
    )
    BEGIN TRY
    INSERT INTO @tblTmp
    SELECT InvoiceDate,   Description, Quantity FROM @tmpTable
    ; WITH
        CTE (InvoiceDate , Description,  Quantity) AS (
    SELECT t1.InvoiceDate,  t1.Description, 
    -- 核心修改：同步首行的正负符号
    CASE 
        WHEN LEFT(t1.Quantity, 1) = '-' 
        THEN -1 * CAST(t2.Quantity AS DECIMAL(18,2)) 
        ELSE CAST(t2.Quantity AS DECIMAL(18,2)) 
    END Quantity
    FROM (
       SELECT *
       , ISNULL((SELECT MIN(RowID) FROM @tblTmp st WHERE InvoiceDate is NOT NULL and st.RowId > t.RowId) -1, (SELECT MAX(RowID) FROM @tblTmp)) NewVal
       FROM @tblTmp t
       WHERE InvoiceDate is NOT NULL
       ) t1
       JOIN (
       SELECT RowID, LTRIM(RIGHT(Description, LEN(Description) - PATINDEX('%[0-9][^0-9]%', Description ))) CustomerPo,
       CASE WHEN ISNUMERIC(LEFT(Description, CHARINDEX(' ', Description) -1)) = 1 THEN
      LEFT( Description, CHARINDEX (' ', Description) -1) ELSE 0 END Quantity
       FROM @tblTmp
       WHERE InvoiceDate is NULL
       ) t2 ON t2.RowId BETWEEN t1.RowID and t1.NewVal
       )

    INSERT INTO @tblTmp1 (InvoiceDate , MedDescription,  Quantity)
    SELECT CONVERT(DATETIME,InvoiceDate, 101)  , Description,
    Quantity
    FROM CTE;

    SELECT * FROM @tblTmp1
    
 END TRY
 BEGIN CATCH
 SELECT
    ERROR_NUMBER() AS ErrorNumber,
    ERROR_STATE() AS ErrorState,
    ERROR_SEVERITY() AS ErrorSeverity,
    ERROR_PROCEDURE() AS ErrorProcedure,
    ERROR_LINE() AS ErrorLine,
    ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

输出说明 #

运行后@tblTmp1的Quantity字段会自动继承对应分组首行的符号：

• 前两个分组首行Quantity带负号，对应子行数量分别为-18、-2、-3
• 第三个分组首行Quantity为正数，对应子行数量保持1、6不变

内容的提问来源于stack exchange，提问作者Developer