关于SQL查询的数据有重复数据

来源：百度知道 编辑：UC知道 时间：2024/05/10 06:48:43
我单独查询表1：
SELECT POCode, Notes, Checked, EndDate, BeginDate, Code
FROM ProduceOrder
WHERE (Checked = 2)

单独查询表2：
SELECT ProduceOrderCode, SaleCode, TopProdCode, ParentMateCode, MateCode, WP, Lot,
Qty, InfactQty, Price
FROM ProduceOrderDtl

单独查询第表3：
SELECT Code, Name, Unit, StockCode, LastSuppCode, Script
FROM Mate

联合查询3个表：
SELECT ProduceOrderDtl.ProduceOrderCode, ProduceOrderDtl.SaleCode, Mate.Name,
Mate.Unit, Mate.Script, ProduceOrderDtl.Qty, Mate.StockCode, ProduceOrder.EndDate,
ProduceOrder.BeginDate, ProduceOrder.Checked, ProduceOrder.Notes,
ProduceOrder.POCode, ProduceOrderDtl.InfactQty
FROM ProduceOrderDtl LEFT OUTER JOIN
Mate ON ProduceOrderDtl.MateCode = Mate.Code LEFT OUTER JOIN
ProduceOrder ON ProduceOrderDtl.ProduceOrderCode = ProduceOrder.Code
ORDER BY ProduceOrder.BeginDate

前面查所有单独的表都没问题，就是在联合查询多表就出现很多重复的数据？是什么原因啊？

加distinct排下重复数据吧，另外你的查询无需使用left outer join，这样很容易产生无效值,增加了结果集的数量。另外第一个表的WHERE条件也得加上。

SELECT DISTINCT ProduceOrderDtl.ProduceOrderCode, ProduceOrderDtl.SaleCode, Mate.Name,
Mate.Unit, Mate.Script, ProduceOrderDtl.Qty, Mate.StockCode, ProduceOrder.EndDate,
ProduceOrder.BeginDate, ProduceOrder.Checked, ProduceOrder.Notes,
ProduceOrder.POCode, ProduceOrderDtl.InfactQty
FROM ProduceOrderDtl JOIN
Mate ON ProduceOrderDtl.MateCode = Mate.Code JOIN
ProduceOrder ON ProduceOrderDtl.ProduceOrderCode = ProduceOrder.Code
WHERE ProduceOrder.checked=2
ORDER BY ProduceOrder.BeginDate

查询3个表无重复数据的写法：
SELECT DICTINCT ProduceOrderDtl.ProduceOrderCode, ProduceOrderDtl.SaleCode, Mate.Name,
Mate.Unit, Mate.Script, ProduceOrderDtl.Qty, Mate.StockCode, ProduceOrder.EndDate,
ProduceOrder.BeginDate, ProduceOrder.Checked, ProduceOrder.Notes,
ProduceOrder.POCode, ProduceOrderDtl.InfactQty
FROM ProduceOrderDtl LEFT OUTER