为什么我的SQL查询返回重复结果?
问题描述:
SQL在下面 - 每个结果出现3次而不是仅仅一次。为什么我的SQL查询返回重复结果?
SELECT Consignments.LegacyID, TripDate, CollectionName, DeliveryName, Pallets, Weight, BaseRate, Consignments.FuelSurcharge, AdditionalCharges, BaseRate * Quantity AS 'InvoiceValue', Consignments.Customer, InvoiceNumber,
CASE
WHEN child.LegacyID = Consignments.Customer THEN child.LegacyID
WHEN parent.LegacyID = Consignments.Customer THEN parent.LegacyID
ELSE this.LegacyID
END AS 'InvoiceAcc'
FROM SageAccount this
LEFT JOIN SageAccount parent on parent.LegacyID = this.InvoiceAccount
LEFT JOIN SageAccount child on this.LegacyID = child.InvoiceAccount
JOIN Consignments on (Consignments.Customer = this.LegacyID AND this.Customer = 'True')
OR (Consignments.Customer = parent.LegacyID AND parent.Customer = 'True')
OR (Consignments.Customer = child.LegacyID AND child.Customer = 'True')
WHERE (this.LegacyID = @Customer) AND (TripDate BETWEEN @fromdate AND @todate) AND (InvoiceNumber IS NOT NULL)
的SQL是给我的另一个类似的查询,但这个时候,我已经修改了它尝试使用它,现在我做的查询,所以我假设我做的事笨。
非常感谢。
答
JOIN Consignments on (Consignments.Customer = this.LegacyID AND this.Customer = 'True')
OR (Consignments.Customer = parent.LegacyID AND parent.Customer = 'True')
OR (Consignments.Customer = child.LegacyID AND child.Customer = 'True')
我猜ü应使用 “this.InvoiceAccount”,而不是this.LegacyID
答
可能是因为您的左连接每个父行有3个子行。 做一个select *来查看你的结果在相乘的行中会有不同的结果。