具有来自不同表的多个Select语句的MySQL存储过程 [英] MySQL Stored Procedure with Multiple Select statements From Different Tables

问题描述

我正在尝试从mysql存储过程中的不同表中进行多项选择，如下所示

I'm trying to do multiple selects from different tables in a mysql stored procedure as follows

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版本为5.6.11-MySQL社区服务器

MySQL Version is 5.6.11 - MySQL Community Server

推荐答案

尝试这种方法:

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 resultset that contains two columns: Sales + Expenses:

+-------+----------+
| Sales | Expenses |
+-------+----------+
|    20 |       15 |
+-------+----------+

，而不是只有一列的两个结果集.

, instead of two resultsets with only one column.

+-------+
| Sales |
+-------+
|    20 |
+-------+

+----------+
| Expenses |
+----------+
|       15 |
+----------+

这篇关于具有来自不同表的多个Select语句的MySQL存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！