SQL需求：按指定条件求和Retail列并输出指定格式结果

1. 优化基础查询（输出指定格式） #

你当前的SQL写法冗余，可简化为通过WHERE过滤条件后直接求和，同时输出Sales_Num、Week、Total_Retail三个字段，完全匹配你要的格式：

SELECT 
    Sales_Num,
    Week,
    SUM(Retail) AS Total_Retail
FROM YourTableName  -- 替换为你的数据表实际名称
WHERE 
    Product IN ('PLA', 'PLA2')
    AND Sales_Num = 45281
    AND Week = 38
GROUP BY Sales_Num, Week

2. 封装为带参数的函数 #

根据不同SQL方言，函数实现略有差异，以下提供两种常见场景的写法：

SQL Server 表值函数（推荐，直接返回完整结果集） #

CREATE FUNCTION dbo.GetTotalRetail (@ProductList NVARCHAR(MAX), @TargetWeek INT, @TargetSalesNum INT)
RETURNS TABLE
AS
RETURN
(
    SELECT 
        @TargetSalesNum AS Sales_Num,
        @TargetWeek AS Week,
        SUM(Retail) AS Total_Retail
    FROM YourTableName
    WHERE 
        -- 拆分传入的产品列表字符串
        Product IN (SELECT value FROM STRING_SPLIT(@ProductList, ','))
        AND Sales_Num = @TargetSalesNum
        AND Week = @TargetWeek
    GROUP BY Sales_Num, Week
)

调用示例：

SELECT * FROM dbo.GetTotalRetail('PLA,PLA2', 38, 45281);

MySQL 自定义函数 #

DELIMITER //
CREATE FUNCTION GetTotalRetail(ProductList VARCHAR(255), TargetWeek INT, TargetSalesNum INT)
RETURNS DECIMAL(18,2)
DETERMINISTIC
BEGIN
    DECLARE Total DECIMAL(18,2);
    SELECT SUM(Retail) INTO Total
    FROM YourTableName
    WHERE 
        FIND_IN_SET(Product, ProductList)
        AND Sales_Num = TargetSalesNum
        AND Week = TargetWeek;
    RETURN Total;
END //
DELIMITER ;

调用示例：

SELECT 45281 AS Sales_Num, 38 AS Week, GetTotalRetail('PLA,PLA2', 38, 45281) AS Total_Retail;

3. 将结果写入另一表 #

不管用基础查询还是函数，都可以通过INSERT INTO语句把计算结果写入目标表：

用基础查询插入 #

INSERT INTO TargetTableName (Sales_Num, Week, Total_Retail)  -- 替换为你的目标表实际名称
SELECT 
    Sales_Num,
    Week,
    SUM(Retail) AS Total_Retail
FROM YourTableName
WHERE 
    Product IN ('PLA', 'PLA2')
    AND Sales_Num = 45281
    AND Week = 38
GROUP BY Sales_Num, Week;

用SQL Server函数插入 #

INSERT INTO TargetTableName (Sales_Num, Week, Total_Retail)
SELECT * FROM dbo.GetTotalRetail('PLA,PLA2', 38, 45281);

用MySQL函数插入 #

INSERT INTO TargetTableName (Sales_Num, Week, Total_Retail)
SELECT 45281, 38, GetTotalRetail('PLA,PLA2', 38, 45281);

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