同一个存储过程中如何把两个不同条件查询的值再相加

同一个存储过程中怎么把两个不同条件查询的值再相加

SQL code


ALTER PROCEDURE dbo.StockCount
    (
    @CompanyID int = 5
    )
AS
    Begin
    SELECT 
        SUM(CASE WHEN TypeID=1 THEN m8 ELSE -m8 END) AS m8,
        SUM(CASE WHEN TypeID=1 THEN m9 ELSE -m9 END) AS m9,
        SUM(CASE WHEN TypeID=1 THEN m10 ELSE -m10 END) AS m10,
        SUM(CASE WHEN TypeID=1 THEN m11 ELSE -m11 END) AS m11,
        SUM(CASE WHEN TypeID=1 THEN m12 ELSE -m12 END) AS m12,
        SUM(CASE WHEN TypeID=1 THEN m13 ELSE -m13 END) AS m13,
        SUM(CASE WHEN TypeID=1 THEN m14 ELSE -m14 END) AS m14,
        SUM(CASE WHEN TypeID=1 THEN m15 ELSE -m15 END) AS m15,
        SUM(CASE WHEN TypeID=1 THEN m16 ELSE -m16 END) AS m16
    FROM RLStock
    where Status in(1,2) and CompanyID=@CompanyID
    -----------
    SELECT 
        SUM(m8) AS m8,
        SUM(m9) AS m9,
        SUM(m10) AS m10,
        SUM(m11) AS m11,
        SUM(m12) AS m12,
        SUM(m13) AS m13,
        SUM(m14) AS m14,
        SUM(m15) AS m15,
        SUM(m16) AS m16    
    FROM RLStock
    where Status=4 and TypeID=3 and ToCompanyID=@CompanyID
    END

这两个查询的结果怎么再对应列再相加起来?

------解决方案--------------------
相加?
是这样么:

SQL code


select sum(m8)m8,sum(m9)m9,sum(m10)m10,sum(m11)m11,sum(m12)m12,sum(m13)m13,sum(m14)m14,sum(m15)m15,sum(m16)m16
from(
    SELECT 
        SUM(CASE WHEN TypeID=1 THEN m8 ELSE -m8 END) AS m8,
        SUM(CASE WHEN TypeID=1 THEN m9 ELSE -m9 END) AS m9,
        SUM(CASE WHEN TypeID=1 THEN m10 ELSE -m10 END) AS m10,
        SUM(CASE WHEN TypeID=1 THEN m11 ELSE -m11 END) AS m11,
        SUM(CASE WHEN TypeID=1 THEN m12 ELSE -m12 END) AS m12,
        SUM(CASE WHEN TypeID=1 THEN m13 ELSE -m13 END) AS m13,
        SUM(CASE WHEN TypeID=1 THEN m14 ELSE -m14 END) AS m14,
        SUM(CASE WHEN TypeID=1 THEN m15 ELSE -m15 END) AS m15,
        SUM(CASE WHEN TypeID=1 THEN m16 ELSE -m16 END) AS m16
    FROM RLStock
    where Status in(1,2) and CompanyID=@CompanyID
  union all
    SELECT 
        SUM(m8) AS m8,
        SUM(m9) AS m9,
        SUM(m10) AS m10,
        SUM(m11) AS m11,
        SUM(m12) AS m12,
        SUM(m13) AS m13,
        SUM(m14) AS m14,
        SUM(m15) AS m15,
        SUM(m16) AS m16    
    FROM RLStock
    where Status=4 and TypeID=3 and ToCompanyID=@CompanyID
)t
    
END

同一个存储过程中如何把两个不同条件查询的值再相加

相关推荐