SQL按Bill.No分组对TB1表qty、Price求和并实现指定展示格式

数据老陈

2026-10-6

问题原因

你现有SQL的逻辑错误出在GROUP BY子句中包含了Xray字段，这会将同一个Bill.No下不同的Xray项拆分为独立分组，最终sum(Price)和sum(qty)统计的是单个Xray项的小计，而非整个账单的合计值。

实现方案（支持所有主流数据库MySQL8+/PostgreSQL/SQL Server/Oracle）

使用窗口函数先统计每个账单的合计值，再控制仅在每个账单的首行展示公共字段和汇总值，其余行仅保留Xray明细：

WITH bill_stat AS (
    SELECT
        `Bill.No`,
        Patient_Name,
        Xray,
        Doctor,
        SUM(Price) OVER(PARTITION BY `Bill.No`) AS total_price,
        SUM(qty) OVER(PARTITION BY `Bill.No`) AS total_qty,
        ROW_NUMBER() OVER(PARTITION BY `Bill.No` ORDER BY Xray) AS row_order
    FROM TB1
)
SELECT
    CASE WHEN row_order = 1 THEN `Bill.No` ELSE NULL END AS `Bill.No`,
    CASE WHEN row_order = 1 THEN Patient_Name ELSE NULL END AS Patient_Name,
    Xray,
    CASE WHEN row_order = 1 THEN total_price ELSE NULL END AS Price,
    CASE WHEN row_order = 1 THEN total_qty ELSE NULL END AS qty,
    CASE WHEN row_order = 1 THEN Doctor ELSE NULL END AS Doctor
FROM bill_stat
ORDER BY `Bill.No`, row_order;

低版本MySQL适配方案（不支持窗口函数场景）

通过子查询先计算每个账单的汇总值，再关联原表控制展示逻辑：

SELECT
    IF(t.min_xray = tb1.Xray, tb1.`Bill.No`, NULL) AS `Bill.No`,
    IF(t.min_xray = tb1.Xray, tb1.Patient_Name, NULL) AS Patient_Name,
    tb1.Xray,
    IF(t.min_xray = tb1.Xray, t.total_price, NULL) AS Price,
    IF(t.min_xray = tb1.Xray, t.total_qty, NULL) AS qty,
    IF(t.min_xray = tb1.Xray, tb1.Doctor, NULL) AS Doctor
FROM TB1
LEFT JOIN (
    SELECT
        `Bill.No`,
        MIN(Xray) AS min_xray,
        SUM(Price) AS total_price,
        SUM(qty) AS total_qty
    FROM TB1
    GROUP BY `Bill.No`
) t ON tb1.`Bill.No` = t.`Bill.No`
ORDER BY tb1.`Bill.No`, tb1.Xray;