如何将MySQL WITH AS语法与存储过程返回的表结合使用？

问题根源 #

• CTE语法不符合MySQL规范：WITH子句的正确格式为WITH cte_name AS (SELECT ...)，MySQL不允许在CTE定义中直接使用CALL调用存储过程。
• 存储过程调用方式错误：CALL是独立执行语句，不能嵌套在SELECT * FROM中，存储过程的结果集无法直接作为查询数据源。
• 逻辑冗余：原需求无需强行结合CTE和存储过程，有更简洁高效的实现方式。

修正方案 #

方案1：优化存储过程，一次性返回多客户端结果 #

直接修改存储过程，让它一次返回指定客户端的统计数据，避免多次调用：

DELIMITER //
CREATE PROCEDURE totalOrders()
BEGIN
    SELECT CONCAT(ClientID, ": ", COUNT(OrderID), " orders") AS Total
    FROM Orders
    WHERE YEAR(Date) = 2022 AND ClientID IN ("Cl1", "Cl2")
    GROUP BY ClientID;
END//
DELIMITER ;

-- 调用存储过程获取结果
CALL totalOrders();

方案2：保留单客户端存储过程，用临时表中转结果 #

如果必须使用原单参数存储过程，可借助临时表存储每次调用的结果再合并：

DELIMITER //
CREATE PROCEDURE totalOrders (IN client VARCHAR(10))
BEGIN
    SELECT CONCAT(client, ": ", COUNT(OrderID), " orders") AS Total;
END//
DELIMITER ;

-- 创建临时表存储结果
CREATE TEMPORARY TABLE temp_results (Total VARCHAR(50));

-- 调用存储过程并插入结果
INSERT INTO temp_results SELECT * FROM (CALL totalOrders("Cl1")) AS t;
INSERT INTO temp_results SELECT * FROM (CALL totalOrders("Cl2")) AS t;

-- 查询合并后的结果
SELECT * FROM temp_results;

-- 临时表会在会话结束后自动删除，也可手动删除
DROP TEMPORARY TABLE temp_results;

方案3：高效原生查询（无需存储过程/CTE） #

原需求最优化的写法是直接分组查询，仅需扫描一次表：

SELECT CONCAT(ClientID, ": ", COUNT(OrderID), " orders") AS Total
FROM Orders
WHERE YEAR(Date) = 2022 AND ClientID IN ("Cl1", "Cl2")
GROUP BY ClientID;

补充：简化版CTE写法（无需存储过程） #

如果坚持使用CTE，可进一步简化课程方案，避免重复查询：

WITH ClientOrders AS (
    SELECT 
        ClientID,
        COUNT(OrderID) AS OrderCount
    FROM Orders
    WHERE YEAR(Date) = 2022 AND ClientID IN ("Cl1", "Cl2")
    GROUP BY ClientID
)
SELECT CONCAT(ClientID, ": ", OrderCount, " orders") AS Total
FROM ClientOrders;

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