mysql写存储查看两张表格不同,具有来自不同表的多个Select语句的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 Version is 5.6.11 - MySQL Community Server

解决方案

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

+-------+----------+

| Sales | Expenses |

+-------+----------+

| 20 | 15 |

+-------+----------+

, instead of two resultsets with only one column.

+-------+

| Sales |

+-------+

| 20 |

+-------+

+----------+

| Expenses |

+----------+

| 15 |

+----------+