正在合并SQL中的同一日期事务

Question

我有三个表。。。tblLoanTaker、tblGiveLoan和tblReceiveLoan。。。其中，tblLoanTaker表包含为贷款交易注册的人员，tblGiveLoan表包含向人员提供贷款的行动的交易，tblReceiveLoan表包含从人员处接收的贷款的行动交易，以清除

我遇到问题的SQL命令在底部显示输出

SELECT * FROM(  
SELECT GL.EntryDate,SUM(GL.TotalAmount) as LoanGiven,0 as LoanReceived
FROM tblGiveLoan as GL WHERE GL.UserId = @UserId and GL.LoanTakerId=@LoanTakerId
GROUP BY GL.EntryDate   
UNION ALL   
SELECT RL.EntryDate,0 as LoanGiven,SUM(RL.Amount) as LoanReceived
FROM tblReceiveLoan as RL WHERE RL.UserId = @UserId and RL.LoanTakerId=@LoanTakerId
GROUP BY RL.EntryDate) as A 
ORDER BY EntryDate ASC

EntryDate  LoanGiven                               LoanReceived
---------- --------------------------------------- ---------------------------------------
2020-12-03 0.00                                    1000.00
2020-12-03 11500.00                                0.00
2020-12-04 0.00                                    5000.00
2020-12-05 20000.00                                0.00
2020-12-06 0.00                                    1000.00
2020-12-07 0.00                                    500.00
2020-12-08 105.00                                  0.00
2020-12-09 250.00                                  0.00
2020-12-10 0.00                                    55.00
2020-12-22 0.00                                    500.00

如何将具有相同日期的行合并为一行，例如2020年12月3日的第一个日期？我希望这一行看起来像

EntryDate  LoanGiven                               LoanReceived
---------- --------------------------------------- ---------------------------------------
2020-12-03 11500.00                                1000.00

Answer 1

您希望聚合在外部查询中，而不是在union成员中：

SELECT EntryDate, SUM(LoanGiven) AS LoanGiven, SUM(LoanReceived) AS LoanReceived
FROM(  
SELECT EntryDate, TotalAmount as LoanGiven, 0 as LoanReceived
FROM tblGiveLoan 
WHERE UserId = @UserId and LoanTakerId = @LoanTakerId
UNION ALL   
SELECT EntryDate, 0, Amount
FROM tblReceiveLoan
WHERE UserId = @UserId and LoanTakerId = @LoanTakerId
) t
GROUP BY EntryDate
ORDER BY EntryDate

请注意，在子查询中，列名称的前缀不是强制性的；每个UNION ALL成员都有自己的作用域，所以列名在这里是明确的。