Как слить несколько запросов на выбор в одном запросе
У меня есть эти 4 Запроса, которые я хочу выбрать из таблицы.
--------------------------------------------------------------- Month Total Fee Fee Deposit Fee Due Fine --------------------------------------------------------------- October 200,000 100,000 100,000 5,00 Мои запросы:
1
SELECT Sum(Fee)AS'TotalFee', DueDate AS'Date' FROM VoucherTB GROUP BY DueDate
2
SELECT Sum(VoucherTB.Fee)AS'FeeDeposit', DueDate AS'Date' FROM VoucherTB WHERE FeeSubmission='Paid' GROUP BY VoucherTB.DueDate
3
SELECT Sum(Fee)AS'FeeDue', DueDate AS'Date' FROM VoucherTB WHERE FeeSubmission='UnPaid' GROUP BY DueDate
4
SELECT Sum(RevenueTB.Fine)AS'Fine', VoucherTB.DueDate AS'Date' FROM RevenueTB INNER JOIN VoucherTB ON venueTB.VoucherNo=VoucherTB.VoucherNo GROUP BY VoucherTB.DueDate
Вы можете сделать это:
SELECT v.DueDate AS'Date', Sum(CASE WHEN v.FeeSubmission = 'Paid' THEN v.Fee ELSE 0 END) AS 'FeeDeposit', Sum(CASE WHEN v.FeeSubmission = 'UnPaid' THEN v.Fee ELSE 0 END) AS 'FeeDue', Sum(r.Fine)AS'Fine', Sum(r.Fee) AS 'TotalFee' FROM RevenueTB AS r INNER JOIN VoucherTB AS v ON v.VoucherNo = r.VoucherNo GROUP BY v.DueDate
Select * from ( SELECT Sum(Fee)AS'TotalFee', DueDate AS'Date' FROM VoucherTB GROUP BY DueDate ) A left join ( SELECT Sum(VoucherTB.Fee)AS'FeeDeposit', DueDate AS'Date' FROM VoucherTB WHERE FeeSubmission='Paid' GROUP BY VoucherTB.DueDate )B on B.Date = A.Date left join ( SELECT Sum(Fee)AS'FeeDue', DueDate AS'Date' FROM VoucherTB WHERE FeeSubmission='UnPaid' GROUP BY DueDate ) C on C.Date = A.Date left join ( SELECT Sum(RevenueTB.Fine)AS'Fine', VoucherTB.DueDate AS'Date' FROM RevenueTB INNER JOIN VoucherTB ON venueTB.VoucherNo=VoucherTB.VoucherNo GROUP BY VoucherTB.DueDate ) D on D.Date = A.Date