联接结果给出重复的行-SQL Server [英] JOIN results give duplicate rows - SQL Server

问题描述

我当前正在处理的数据库未按我认为的方式设置.几乎没有任何主键/外键，因此联接表可能会非常棘手.

I am currently dealing with a database that is not set up the way i think it should be. There are barely any primary/foreign keys so joining tables can be quite tricky.

我试图重新创建与我创建的联接类似的问题，这是由于缺少键而导致的不正确的重复行.

I have tried to recreate a similar issue to the one i have where joins that i create result in incorrect duplicate rows which is a result of the lack of keys.

sql小提琴- http://sqlfiddle.com/#!9/ff59ad/1

查询:

SELECT I.InvoiceNumber, I.AmountPaid,
O.Amount
FROM OrderInvoice O
JOIN Invoice I 
ON O.InvoiceNumber = I.InvoiceNumber

现在您可以看到发票编号重复输入

Now as you can see there is duplicate entrys for invoice number

InvoiceNumber   AmountPaid  Amount
    123           10          8
    123           10          2
    567           10          4
    567           10          6

每个支付的总金额应该仅为10，而由于匹配的行，联接为每个创建20.因此，如果我将这些结果分组并求和，则总数是错误的.它与我的实际示例略有不同，因为由于行重复，即使使用普通的连接语法，我实际上也得到了交叉连接.

The total amount paid should just be 10 for each whereas the join creates 20 for each because of the matching rows. So if i group these results and sum them the total amount is wrong. Its slightly different from my real life example as it looks like i am actually getting a cross join even with normal join syntax because of the duplicated rows.

删除这些重复行的最佳方法是什么?我尝试加入多个列以创建唯一行，但没有其他列可以加入.

Whats the best way to remove these duplicated rows? I have tried joining on multiple columns to create a unique row but i have no other columns to join on.

有人可以给我建议吗?

推荐答案

只需使用 sum 聚合函数添加 group by 子句

Just add group by clause with sum aggregation function

SELECT I.InvoiceNumber, I.AmountPaid,
sum(O.Amount) Amount
FROM OrderInvoice O
JOIN Invoice I 
ON O.InvoiceNumber = I.InvoiceNumber group by I.InvoiceNumber, I.AmountPaid

结果:

InvoiceNumber   AmountPaid  Amount
123             10          10
567             10          10

这篇关于联接结果给出重复的行-SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！