具有来自不同表的多个选择语句的 MySQL 存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20317971/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Stored Procedure with Multiple Select statements From Different Tables
提问by Omar Salim
I'm trying to do multiple selects from different tables in a mysql stored procedure as follows
我正在尝试从 mysql 存储过程中的不同表中进行多次选择，如下所示
DELIMITER //
CREATE PROCEDURE `NovemberSummary`(IN `branch` VARCHAR(60), IN `year` INT) NOT
DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER
BEGIN
select sum(sales.amount) as Sales from sales where month (sales.date)= 11 and
sales.branch = branch;
select sum(expenses.amount) as Expenses from expenses where month(expenses.date)= 11
and expenses.branch = branch;
END
But It returns only The first Select, as In result set only contains Sales Column.
但它只返回第一个选择，因为结果集中只包含销售列。
MySQL Version is 5.6.11 - MySQL Community Server
MySQL 版本为 5.6.11 - MySQL 社区服务器
回答by krokodilko
Try this approach:
试试这个方法：
DELIMITER //
CREATE PROCEDURE `NovemberSummary`(IN `branch` VARCHAR(60), IN `year` INT) NOT
DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER
BEGIN
SELECT
( select sum(sales.amount) from sales
where month (sales.date)= 11 and sales.branch = branch ) as Sales ,
( select sum(expenses.amount) from expenses
where month(expenses.date)= 11 and expenses.branch = branch ) as Expenses
;
END
this procedure returns only one resultsetthat contains two columns: Sales + Expenses:
此过程仅返回一个包含两列的结果集：销售额 + 费用：
+-------+----------+
| Sales | Expenses |
+-------+----------+
| 20 | 15 |
+-------+----------+
, instead of two resultsets with only one column.
, 而不是只有一列的两个结果集。
+-------+
| Sales |
+-------+
| 20 |
+-------+
+----------+
| Expenses |
+----------+
| 15 |
+----------+
回答by Ana El Bembo
CREATE PROCEDURE get_data ()
BEGIN
SELECT Code, Name, Population, Continent FROM Country
WHERE Continent = 'Oceania' AND Population < 10000;
SELECT Code, Name, Population, Continent FROM Country
WHERE Continent = 'Europe' AND Population < 10000;
SELECT Code, Name, Population, Continent FROM Country
WHERE Continent = 'North America' AND Population < 10000;
END;
回答by Shaan Ansari
Try This
尝试这个
CREATE PROCEDURE `NovemberSummary`(IN `branch` VARCHAR(60), IN `year` INT) NOT
DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER
BEGIN
DECLARE SalesAmount VARCHAR(255) DEFAULT 0;
DECLARE ExpensesAmount VARCHAR(255) DEFAULT 0;
SELECT SUM(sales.amount) AS Sales INTO SalesAmount FROM sales WHERE MONTH (sales.date)= 11 AND
sales.branch = branch;
SELECT SUM(expenses.amount) AS Expenses INTO ExpensesAmount FROM expenses WHERE MONTH(expenses.date)= 11
AND expenses.branch = branch;
SELECT SalesAmount AS Sales, ExpensesAmount AS Expenses;
END
