SQL视图中如何展示SELECT返回的多客户值？修复统计NULL问题

原SQL的问题分析 #

你的原语句存在几个核心问题：

1. 分组逻辑错误：将Name和LastName加入GROUP BY，导致每个产品-客户组合生成单独一行，无法实现“一个产品对应所有客户合并显示”的需求。
2. 排序逻辑不符需求：你要的是销量TOP3，但原语句按TotalProfit ASC（利润升序）排序，会把利润最低的产品排在前面，应该改为按销量（TotalQty）降序排序。
3. 客户重复风险：发票与客户关联时，可能因一个客户多次购买同一产品导致重复出现在列表中，需要去重。

解决方案：合并客户到同一单元格 #

根据你使用的数据库类型，选择对应的字符串聚合函数实现客户名称合并：

1. SQL Server（使用STRING_AGG）

CREATE VIEW INVOICE_VIEW AS
WITH ProductSales AS (
    -- 先计算2022年6月所有产品的销量、利润，筛选销量TOP3
    SELECT 
        P.idProduct,
        P.ProductName,
        SUM(F.Quantity) AS TotalQty,
        SUM(F.Price) AS TotalProfit
    FROM PRODUCT P
    INNER JOIN INVOICE F ON P.idProduct = F.Det_Products
    WHERE F.Date BETWEEN '20220601' AND '20220630'
    GROUP BY P.idProduct, P.ProductName
    ORDER BY TotalQty DESC
    OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY
),
ProductCustomers AS (
    -- 获取购买对应产品的唯一客户列表
    SELECT DISTINCT
        F.Det_Products,
        CONCAT(C.Name, ' ', C.LastName) AS CustomerName
    FROM INVOICE F
    LEFT JOIN CUSTOMER C ON F.Num_Invoice = C.Num_Invoice
    WHERE F.Date BETWEEN '20220601' AND '20220630'
      AND C.Name IS NOT NULL -- 过滤无客户的记录
)
-- 合并客户名称到同一单元格
SELECT
    PS.ProductName,
    PS.TotalQty,
    PS.TotalProfit,
    STRING_AGG(PC.CustomerName, ', ') AS Customers
FROM ProductSales PS
LEFT JOIN ProductCustomers PC ON PS.idProduct = PC.Det_Products
GROUP BY PS.ProductName, PS.TotalQty, PS.TotalProfit
ORDER BY PS.TotalQty DESC;

2. MySQL（使用GROUP_CONCAT）

CREATE VIEW INVOICE_VIEW AS
WITH ProductSales AS (
    SELECT 
        P.idProduct,
        P.ProductName,
        SUM(F.Quantity) AS TotalQty,
        SUM(F.Price) AS TotalProfit
    FROM PRODUCT P
    INNER JOIN INVOICE F ON P.idProduct = F.Det_Products
    WHERE F.Date BETWEEN '2022-06-01' AND '2022-06-30'
    GROUP BY P.idProduct, P.ProductName
    ORDER BY TotalQty DESC
    LIMIT 3
),
ProductCustomers AS (
    SELECT DISTINCT
        F.Det_Products,
        CONCAT(C.Name, ' ', C.LastName) AS CustomerName
    FROM INVOICE F
    LEFT JOIN CUSTOMER C ON F.Num_Invoice = C.Num_Invoice
    WHERE F.Date BETWEEN '2022-06-01' AND '2022-06-30'
      AND C.Name IS NOT NULL
)
SELECT
    PS.ProductName,
    PS.TotalQty,
    PS.TotalProfit,
    GROUP_CONCAT(PC.CustomerName SEPARATOR ', ') AS Customers
FROM ProductSales PS
LEFT JOIN ProductCustomers PC ON PS.idProduct = PC.Det_Products
GROUP BY PS.ProductName, PS.TotalQty, PS.TotalProfit
ORDER BY PS.TotalQty DESC;

3. Oracle（使用LISTAGG）

CREATE VIEW INVOICE_VIEW AS
WITH ProductSales AS (
    SELECT 
        P.idProduct,
        P.ProductName,
        SUM(F.Quantity) AS TotalQty,
        SUM(F.Price) AS TotalProfit
    FROM PRODUCT P
    INNER JOIN INVOICE F ON P.idProduct = F.Det_Products
    WHERE F.Date BETWEEN TO_DATE('20220601', 'YYYYMMDD') AND TO_DATE('20220630', 'YYYYMMDD')
    GROUP BY P.idProduct, P.ProductName
    ORDER BY TotalQty DESC
),
Top3Products AS (
    SELECT * FROM ProductSales WHERE ROWNUM <= 3
),
ProductCustomers AS (
    SELECT DISTINCT
        F.Det_Products,
        C.Name || ' ' || C.LastName AS CustomerName
    FROM INVOICE F
    LEFT JOIN CUSTOMER C ON F.Num_Invoice = C.Num_Invoice
    WHERE F.Date BETWEEN TO_DATE('20220601', 'YYYYMMDD') AND TO_DATE('20220630', 'YYYYMMDD')
      AND C.Name IS NOT NULL
)
SELECT
    TP.ProductName,
    TP.TotalQty,
    TP.TotalProfit,
    LISTAGG(PC.CustomerName, ', ') WITHIN GROUP (ORDER BY PC.CustomerName) AS Customers
FROM Top3Products TP
LEFT JOIN ProductCustomers PC ON TP.idProduct = PC.Det_Products
GROUP BY TP.ProductName, TP.TotalQty, TP.TotalProfit
ORDER BY TP.TotalQty DESC;

关于拆分为多列（Customer1、Customer2等） #

这种方式实用性较低，因为客户数量不固定，需要提前确定最大客户数或使用动态SQL实现。如果业务必须拆分，以SQL Server为例，可以使用PIVOT结合行号生成，但需要额外处理行号逻辑，这里优先推荐合并为单个单元格的方案。

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