使用动态选择mysql存储过程的列 [英] Using Dynamically selecting columns of mysql stored procedure

问题描述

我正在尝试编写一个存储过程来从数据库中提取一些聚合统计信息.

I am trying to write a stored procedure to pull off some aggregate statistics from a database.

我想修改程序以允许动态选择列.

I would like to amend the procedure to allow dynamic selection of columns.

我的第一个想法是使用 Case 或 IF 语句来选择不同的列

My first thoughts were to use a Case or IF statement to select different columns

DELIMITER//
CREATE PROCEDURE 'procStats'(IN buySell varchar(4))
SELECT 

    CASE 
        WHEN buySell = 'Buy' THEN AVG(salesTransactions.BuyPrice) AS AveragePrice,
        WHEN buySell = 'Sell' THEN AVG(salesTransactions.SellPrice) AS AveragePrice,
    END CASE;

    MONTHNAME(salesTransactions.DateOfTransaction) as TransactionMonth
FROM
    salesTransactions
GROUP BY 
    TransactionMonth
LIMIT 6;
END//

现在我不认为case语句是为了这个目的，而且目前还行不通……有没有可能实现上面的??

Now I don't think that case statements are intended for this purpose, and it currently doesn't work... Is it possible to achieve the above??

仅供参考 - 我完全知道我可以只选择两列，但我不希望将这两列都公开给我的网络应用程序.

FYI - I am fully aware that i could just select both columns however I do not wish to expose both columns to my web app.

推荐答案

改变这个

CASE 
    WHEN buySell = 'Buy' THEN AVG(salesTransactions.BuyPrice) AS AveragePrice,
    WHEN buySell = 'Sell' THEN AVG(salesTransactions.SellPrice) AS AveragePrice,
END CASE;

到

CASE buySell
    WHEN 'Buy' THEN AVG(salesTransactions.BuyPrice) 
    WHEN 'Sell' THEN AVG(salesTransactions.SellPrice)
END AS AveragePrice,

最后应该是这样的:

DELIMITER//
CREATE PROCEDURE procStats (IN buySell varchar(4))
BEGIN
SELECT 

    CASE buySell
        WHEN 'Buy' THEN AVG(salesTransactions.BuyPrice) 
        WHEN 'Sell' THEN AVG(salesTransactions.SellPrice)
    END AS AveragePrice,

    MONTHNAME(salesTransactions.DateOfTransaction) as TransactionMonth
FROM
    salesTransactions
GROUP BY 
    TransactionMonth
LIMIT 6;
END//

我修正了更多的语法错误.

I fixed some more syntax errors.

这篇关于使用动态选择mysql存储过程的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！