我尝试了许多解决方案,以摆脱查询中的重复结果,但没有用。这是我的桌子:
sparepartordordetails d表:
id ----- SparePartID --------OrderID -------- Qty ----- Price
101-----------47----------------84-------------2--------1500
102-----------49----------------86-------------3--------3000
103-----------52----------------86-------------5--------800
104-----------45----------------87-------------3--------450
105-----------48----------------87-------------2--------1200
和sparepartfeedback fb表:
PFID -----Review-----------Rating------- SparePartID -----OrderID
1---------good job------------5--------------49--------------86
2---------nice product--------4--------------52--------------86
现在这是我的 QUERY ,它给出重复的结果:
SELECT DISTINCT d.SparePartID, s.Name, d.Quantity, d.Price, d.OrderID ,
fb.PFID, fb.Review, fb.Rating
FROM sparepartorderdetails d LEFT JOIN
sparepartfeedback fb
ON fb.OrderID = d.OrderID INNER JOIN
sparepart s
ON d.SparePartID= s.SparePartID INNER JOIN
orders o
ON o.OrderID = d.OrderID
WHERE d.OrderID = "86"
现在这些是重复的结果,即我得到:
SparePartID-----Name------Qty----Price----OrderID ----PFID ------Review---- Rating**
49 ----------- Lights ---- 3---- 3000 ------86--------1----------good job ------ 5
52 ----------- Mirrors---- 5----- 800-------86--------2----------nice product ------ 5
49 ----------- Lights ---- 3---- 3000 ------86--------1----------good job ------ 5
52 ----------- Mirrors---- 5----- 800-------86--------2----------nice product ------ 5
但是,此查询给出 no Replicates 如果订单没有评论/评分,例如for orderID:87 它只是正确地显示了订单详细信息和null值的详细信息,并且评分。但是,当我为有序的订购:86具有评论/评分时,它会提供重复的结果。
以下是我期望的结果:
SparePartID-----Name------Qty----Price----OrderID ----PFID ------Review------- Rating**
49 ----------- Lights ---- 3---- 3000 ------86--------1----------good job ------- 5
52 ----------- Mirrors---- 5----- 800-------86--------2----------nice product --- 5
我认为您已经在备件ID上遗漏了JOIN
条件:
SELECT d.SparePartID, s.Name, d.Quantity, d.Price, d.OrderID ,
fb.PFID, fb.Review, fb.Rating
FROM sparepartorderdetails d LEFT JOIN
sparepartfeedback fb
ON fb.OrderID = d.OrderID
fp.SparePartID = d.SparePartID INNER JOIN
sparepart s
ON d.SparePartID= s.SparePartID INNER JOIN
orders o
ON o.OrderID = d.OrderID
WHERE d.OrderID = 86