组合来自不同表的多个Select语句

本文关键字：Select 语句 组合 | 更新日期: 2023-09-27 18:01:15

我有这个MYSQL SP对不同的表做SUm但我希望SP返回一个表中的所有结果一行我尝试使用+来组合结果从选择，但它得到错误

 DROP PROCEDURE IF EXISTS x.GetFinanceContent;
 delimiter //
 CREATE PROCEDURE x.GetFinanceContent
    (
         IN  userId INT
    )
 BEGIN
 (SELECT SUM(AMOUNT) AS creditTotal
    FROM x.Card1 
    WHERE user_Id = userId and card_type=1) 
    +
 (SELECT SUM(AMOUNT) AS debitTotal
    FROM x.Card2 
    WHERE user_Id = userId and card_type=2) 

 END;//

错误码:1064你的SQL语法;查看手册对应MySQL服务器使用正确语法的版本+ (SELECT SUM(AMOUNT) ASdebitTotal从x.CardWHERE user_Id ' at line 10 .000 sec

组合来自不同表的多个Select语句

SELECT 
    ( SELECT SUM(AMOUNT) AS creditTotal
      FROM x.Card1
      WHERE user_Id = userId AND card_type=1
    )  
  , ( SELECT SUM(AMOUNT) AS debitTotal
      FROM x.Card2
      WHERE user_Id = userId AND card_type=2
    )

如果你想对这些总和求和，你可以这样查询:

SELECT ((SELECT SUM(AMOUNT) AS creditTotal
    FROM x.Card1 
    WHERE user_Id = userId and card_type=1) + 
(SELECT SUM(AMOUNT) AS debitTotal
    FROM x.Card2 
    WHERE user_Id = userId and card_type=2)) as total

使用联合http://dev.mysql.com/doc/refman/5.0/en/union.html如果你想得到一个结果集....

DROP PROCEDURE IF EXISTS x.GetFinanceContent;
 delimiter //
 CREATE PROCEDURE x.GetFinanceContent
    (
         IN  userId INT
    )
 BEGIN
 (SELECT SUM(AMOUNT) AS creditTotal
    FROM x.Card1 
    WHERE user_Id = userId and card_type=1) 
 UNION
 (SELECT SUM(AMOUNT) AS debitTotal
    FROM x.Card2 
    WHERE user_Id = userId and card_type=2) 

 END;//

虽然我现在生锈的SQL和太懒检查语法是否正确，我认为它应该工作

相关文章：