SQL Server中XML列内计算每日TotalPrice（Quantity×UnitPrice）

针对你遇到的XML列金额统计需求，直接用CROSS APPLY拆分XML节点，结合数值计算和分组就能实现。以下是具体的SQL写法和关键说明：

核心查询代码 #

假设你的表名为Orders，UTC日期列是OrderUtcDate，XML列Items的结构为<Items><Item><Quantity>...</Quantity><UnitPrice>...</UnitPrice></Item></Items>：

-- 定义要统计的最近X天，替换成你的需求值
DECLARE @DaysToLookBack INT = 7;

SELECT
    -- 把UTC日期转成日期格式，用于按日分组
    CAST(o.OrderUtcDate AS DATE) AS OrderDate,
    -- 计算每日总金额：每个Item的数量*单价后求和
    SUM(CAST(i.ItemNode.value('(Quantity)[1]', 'DECIMAL(18,2)') AS DECIMAL(18,2)) 
        * CAST(i.ItemNode.value('(UnitPrice)[1]', 'DECIMAL(18,2)') AS DECIMAL(18,2))) AS TotalDailyPrice,
    -- 保留你已实现的每日总数量统计
    SUM(CAST(i.ItemNode.value('(Quantity)[1]', 'INT') AS INT)) AS TotalDailyQuantity
FROM Orders o
-- 拆分XML列中的每个Item节点，转换成行数据
CROSS APPLY o.Items.nodes('/Items/Item') i(ItemNode)
-- 筛选最近X天的UTC数据，截至当日UTC结束
WHERE o.OrderUtcDate >= DATEADD(DAY, -@DaysToLookBack, GETUTCDATE())
  AND o.OrderUtcDate < DATEADD(DAY, 1, CAST(GETUTCDATE() AS DATE))
-- 按日期分组统计
GROUP BY CAST(o.OrderUtcDate AS DATE)
ORDER BY OrderDate;

关键细节说明 #

• XML节点拆分：CROSS APPLY ... nodes()会把每个XML里的<Item>节点拆成单独的行，这样就能逐个计算每个商品的金额。如果你的XML结构不同（比如根节点直接是<Item>集合），需要调整nodes()里的XPath路径，比如改成nodes('/Item')。
• 数据类型匹配：value()方法提取值时要指定正确的数据类型，金额用DECIMAL避免精度丢失，数量根据实际情况选INT或DECIMAL。
• UTC日期筛选：用GETUTCDATE()确保和表中存储的UTC日期一致，DATEADD计算的范围能准确包含最近X天到当日的所有数据。

注意事项 #

• 如果表数据量较大，给OrderUtcDate加索引能显著提升查询速度；
• 若XML结构复杂，先确认XPath路径的正确性（可以用SELECT Items.query('/Items/Item') FROM Orders测试节点提取）；
• 调整DECIMAL的精度（比如DECIMAL(18,4)）以匹配你的金额数据精度要求。

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