在sql中使用哪个连接来获取字段的总和 [英] Which join to be used in sql to get sum of a fields

问题描述

我有一张桌子Expensemaster，下面有数据

I had table a table Expensemaster with data like below

ExpenseID   ExpAmount   ExpPK   ExpDate
1            20            23  17-Jul-2014
2            40            34  17-Jul-2014
3            45            23  17-Jul-2014
4            69            34  22-Jul-2014
5            45            54  22-Jul-2014
6            67            63  21-Jul-2014


IncomeID    IncomeAmount    IncomPk Income Date
34           20             24       17-Jul-2014
36           40             24       16-Jul-2014
45           45             234      17-Jul-2014
46           69             214      26-Jul-2014
51           45             34       27-Jul-2014
57           67             34       21-Jul-2014

我想要的是一个查询来显示日期（可能是Expdate或incomeDate）费用金额（如果有）和收入金额i的总和f任何安排日期



表示两个表中的所有日期我们应该得到当天收入和费用的总和



我曾尝试过外连接但失败了，因为我们必须考虑表日期，任何人都可以建议更好的查询或选项

What I want is a query to show the Date (it may be Expdate or incomeDate) sum of expense amount if any and sum of Income Amount if any angainst the Date

means for all the dates in both table we should get the sum of income and expense of that day

I had tried the Outer join but failed as we have to consider both the table date can anyone suggest a better query or option

推荐答案

尝试这个：

Try this:

SELECT SUM(t1.ExpAmount) AS ExpAmount, SUM(t2.IncomeAmount)
FROM Expense AS t1 INNER JOIN Income AS t2 ON t1.ExpDate = t2.IncomeDate
GROUP BY t1.ExpDate
ORDER BY t1.ExpDate

以上e xample将返回相等日期的金额总和。



但是......



如果你想要得到sume的每一天，你需要使用这样的东西：

Above example will return sum of amounts for equal dates.

BUT...

If you want to get sume for each day, you need to use something like this:

SELECT aDate, [E], [I]
FROM (
    SELECT ExpDate AS aDate, 'E' AS [Description], ExpAmount AS Amount
    FROM Expense
    UNION ALL
    SELECT IncomeDate AS aDate, 'I' AS [Description], IncomeAmount AS Amount
    FROM Income
) AS DT
PIVOT (SUM(Amount) FOR [Description] IN ([E], [I])) AS PT

这篇关于在sql中使用哪个连接来获取字段的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！