如何基于WITH查询建表、优化JSON查询并按Identifier求和？

有一张包含JSON数据的表，需要提取JSON中的特定参数并去除其中的€符号，得到可用于求和的数值。现有可正常运行的基础查询（注：原查询存在语法小问题，已修正JSON_VALUE路径字符串的闭合引号）：

With C as (
SELECT      A.identifier, 
                JSON_VALUE(A.jsonBody,'$.path') as somethingA,
                JSON_VALUE(A.jsonBody,'$.path') as somethingB
FROM table A WITH(NOLOCK)
join table B on (A.identifier = B.identifier)
WHERE A.statement = 'x')

select  C.identifier, 
    replace (C.somethingA, '€','') as TotalA, 
    replace (C.somethingB, '€','') as TotalB
from C

当前需求：

1. 优化查询，将REPLACE与JSON_VALUE合并为一步操作；
2. 按identifier统计TotalA、TotalB的合计值。

1. 合并REPLACE与JSON_VALUE的优化查询 #

可以直接在CTE的JSON_VALUE外层包裹REPLACE，同时建议将处理后的结果转换为数值类型（如DECIMAL(18,2)），避免后续求和时因字符串类型导致的错误。优化后的查询如下：

WITH C AS (
    SELECT 
        A.identifier,
        -- 一步完成JSON提取、去€符号并转数值
        CAST(REPLACE(JSON_VALUE(A.jsonBody, '$.path'), '€', '') AS DECIMAL(18,2)) AS TotalA,
        CAST(REPLACE(JSON_VALUE(A.jsonBody, '$.path'), '€', '') AS DECIMAL(18,2)) AS TotalB
    FROM table A WITH(NOLOCK)
    JOIN table B ON A.identifier = B.identifier
    WHERE A.statement = 'x'
)
SELECT 
    identifier,
    TotalA,
    TotalB
FROM C

提示：可根据实际数值范围调整DECIMAL的精度和小数位，比如DECIMAL(10,2)。

2. 按identifier统计合计值 #

基于优化后的CTE，使用GROUP BY分组，配合SUM()函数计算合计值：

WITH C AS (
    SELECT 
        A.identifier,
        CAST(REPLACE(JSON_VALUE(A.jsonBody, '$.path'), '€', '') AS DECIMAL(18,2)) AS TotalA,
        CAST(REPLACE(JSON_VALUE(A.jsonBody, '$.path'), '€', '') AS DECIMAL(18,2)) AS TotalB
    FROM table A WITH(NOLOCK)
    JOIN table B ON A.identifier = B.identifier
    WHERE A.statement = 'x'
)
SELECT 
    identifier,
    SUM(TotalA) AS TotalA_Sum,
    SUM(TotalB) AS TotalB_Sum
FROM C
GROUP BY identifier

如果需要将结果存入新表，可使用SELECT INTO语句（无需提前创建表）：

WITH C AS (
    SELECT 
        A.identifier,
        CAST(REPLACE(JSON_VALUE(A.jsonBody, '$.path'), '€', '') AS DECIMAL(18,2)) AS TotalA,
        CAST(REPLACE(JSON_VALUE(A.jsonBody, '$.path'), '€', '') AS DECIMAL(18,2)) AS TotalB
    FROM table A WITH(NOLOCK)
    JOIN table B ON A.identifier = B.identifier
    WHERE A.statement = 'x'
)
SELECT 
    identifier,
    SUM(TotalA) AS TotalA_Sum,
    SUM(TotalB) AS TotalB_Sum
INTO NewSummaryTable -- 自定义新表名称
FROM C
GROUP BY identifier

若已有提前创建好的表，可使用INSERT INTO ... SELECT语句插入数据：

INSERT INTO ExistingSummaryTable (identifier, TotalA_Sum, TotalB_Sum)
WITH C AS (
    SELECT 
        A.identifier,
        CAST(REPLACE(JSON_VALUE(A.jsonBody, '$.path'), '€', '') AS DECIMAL(18,2)) AS TotalA,
        CAST(REPLACE(JSON_VALUE(A.jsonBody, '$.path'), '€', '') AS DECIMAL(18,2)) AS TotalB
    FROM table A WITH(NOLOCK)
    JOIN table B ON A.identifier = B.identifier
    WHERE A.statement = 'x'
)
SELECT 
    identifier,
    SUM(TotalA),
    SUM(TotalB)
FROM C
GROUP BY identifier

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