时间戳

首页 > 解决方案 > 我想通过一个变量将两个查询合二为一

sql - 我想通过一个变量将两个查询合二为一

问题描述

我想通过一个变量将两个查询合二为一

ALTER PROCEDURE [dbo].[Balance]
     @userId nvarchar(200)
AS
BEGIN

     DECLARE @parchesQueryAdd decimal(18,2),@parchesQueryRemove decimal(18,2), @topupQuery decimal(18,2), @Balance decimal(18,2), @totalamount decimal(18,2)
/****** this two Querys starts ******/

     SET @parchesQueryAdd = (SELECT SUM(Amount * CurrentBalanceCurrency) from UserBalance where BalanceForId = @userId and AmountType = 10)
     SET @parchesQueryRemove = (SELECT SUM(Amount * CurrentBalanceCurrency) from UserBalance where BalanceForId = @userId and AmountType = 20)
/****** End ******/

     SET @Balance = @parchesQueryAdd - @parchesQueryRemove

     SET @topupQuery = (SELECT SUM(Amount * Quentity) from TopUpRecords where TopupById = @userId)

     SET @totalamount= @Balance - @topupQuery

     PRINT @totalamount
END

标签： sqlsql-serverstored-proceduressql-server-2016

解决方案

您可以使用条件聚合函数来设置@Balance而不是两个查询。

DECLARE 
     @parchesQueryAdd decimal(18,2),
     @parchesQueryRemove decimal(18,2), 
     @topupQuery decimal(18,2), 
     @Balance decimal(18,2), 
     @totalamount decimal(18,2)


SELECT @Balance = SUM(CASE WHEN  AmountType = 10 THEN Amount * CurrentBalanceCurrency ELSE 0 END)
                  - SUM(CASE WHEN  AmountType = 20 THEN Amount * CurrentBalanceCurrency ELSE 0 END)
FROM UserBalance
WHERE BalanceForId = @userId 
GROUP BY BalanceForId


 SET @topupQuery = (SELECT SUM(Amount * Quentity) from TopUpRecords where TopupById = @userId)

SET @totalamount= @Balance - @topupQuery

PRINT @totalamount

推荐阅读