sql server左联接生成重复项

ruarlubt  于 2021-08-09  发布在  Java
关注(0)|答案(2)|浏览(375)

我有三张table,预算,收入和支出。
预算表:

收入表:

费用表:

这是我的sql语句:

SELECT 
    Budgets.BudgetID, Budgets.BudgetName, Budgets.Username_FK,   
    Budgets.BudgetAmount, Budgets.SavePercentage,
    Expenses.ExpensesID, Expenses.ExpensesAmount, Expenses.ExpensesCategory,
    Income.IncomeID, Income.IncomeAmount, Income.IncomeCategory
FROM 
    Budgets
LEFT JOIN 
    Income ON Budgets.BudgetID = Income.BudgetID_FK
LEFT JOIN 
    Expenses ON Budgets.BudgetID = Expenses.BudgetID_FK
WHERE 
    BudgetName = '2019

结果如下:

基于我的 Income 表中,只有一个记录绑定到 BudgetID = 3 ,但在左连接中,它是重复的。
理想情况下,我希望它在副本上返回“null”。我该怎么做?

7kjnsjlb

7kjnsjlb1#

你有好几排 expensesbudgetID ,所以您的联接会产生那么多行。我倾向于怀疑同样的情况也会发生在 income 我也是。
如果你想要一排 budgetID ,则一个选项是预聚合和 left join (或 outer apply ). 假设您想要每个预算的总费用和收入,您可以执行以下操作:

select b.*, e.expenseAmount, i.amountAmount
from budgets b
left join (
    select budgetID_FK, sum(expenseAmount) expenseAmount 
    from expenses 
    group by budgetID_FK
) e on e.budgetID_FK = b.budgetID
left join (
    select budgetID_FK, sum(incomeAmount) incomeAmount 
    from income 
    group by budgetID_FK
) i on i.budgetID_FK = b.budgetID

现在,您将依赖表中的行分组为 budgetID ,因此无法看到这些表的其他列,例如 incomeCategory 或者 expenseCategory (每个值有多个值) budgetID ).

baubqpgj

baubqpgj2#

对于budgetid 3,同一用户的开支表中有4个开支(不同的开支ID)为50.00。我想你想要的是相同类别和预算id的总费用,在这种情况下是预算id 3

SELECT 
Budgets.BudgetID, Budgets.BudgetName, Budgets.Username_FK,   
Budgets.BudgetAmount, Budgets.SavePercentage,
Income.IncomeID, Income.IncomeAmount, Income.IncomeCategory,
ex.ExpensesCategory,
ex.Total_ExpensesAmount, 

FROM Budgets 
LEFT JOIN Income ON Budgets.BudgetID = Income.BudgetID_FK
LEFT JOIN (Select BudgetID_FK, ExpensesCategory, SUM(ExpensesAmount) as Total_ExpensesAmount
           FROM Expenses 
           GROUP BY BudgetID_FK, ExpensesCategory) ex ON Budgets.BudgetID = ex.BudgetID_FK
WHERE BudgetName = '2019'

相关问题