从sql查询中获取重复记录 [英] Getting duplicate record from sql query

问题描述

大家好



以下是我的查询

 < span class =code-keyword> SELECT  ROW_NUMBER（） OVER （ ORDER   BY  om.OrderFormNo） AS  SI，
 om.OrderFormNo，
 om.OrderDate， 
 ag.CompanyName  AS 代理商，
 sup.Name  AS 供应商，
 dis.Name  AS 分销商，
 it.ItemName，
 od.Quantity，
 od.Rate，
 odc.ReceivedQty，
 odc.ReceivedDate，
 odca.CancelQty，
 odca.CancelDate，
 od.Quantity-（odc.ReceivedQty + odca.CancelQty） AS 余额
  FROM  OrderMaster om 
  JOIN  OrderDetails od  ON  om.OMID = od.OMIDFormRef 
  JOIN  OrderDetailsChild odc  ON  od.ODID = odc.OrderDetailsIDRef 
  JOIN  OrderDetailsCancel odca  ON  od .ODID = odca.OrderDetailsIDRef 
  JOIN  ItemInfo it  ON  od.IIIDItemRef = it.IIID 
  JOIN 代理商ag  ON  om.AIDAgencyRef = ag.AID 
  JOIN  SupplierMainGroup sup  ON  om.SMGIDRef = sup.SMGID 
  JOIN  MainDistributor dis  ON  om.MDIDRef = dis.MDID 
  WHERE  sup.Name = '  trtrttrt' 

和我得到的输出是





SI OrderFormNo OrderDate代理商供应商分销商ItemName数量已收到订单数量已收到日期取消日期取消更新日期





1 ORD2231REE 2013-11-21 Azad trtrttrt sds Shirtings 25 435 4 2013-11-21 3 2013-11-21 18



2 ORD2231REE 2013-11-21 Azad trtrttrt sds Shirtings 25 435 5 2013-11-21 3 2013-11-21 17



3 ORD2231REE 2013-11-21 Azad trtrttrt sds Shirtings 25 435 4 2013-11-21 3 2013-11-21 18



4 ORD2231REE 2013-11-21 Azad trtrttrt sds Shirtings 25 435 5 2013- 11-21 3 2013-11-21 17



现在问题实际上我只有2行，即SI。 1和2

但是这里它重复并再次显示相同的行。



请告诉我在查询中需要修改的内容。



修改：我使用Distinct，只有当我删除此行ROW_NUMBER（）OVER（ORDER BY om.OrderFormNo）AS SI时才能正常工作，否则它不起作用。

所以任何人都可以告诉我替代ROW_NUMBER（）OVER（ORDER BY om.OrderFormNo）AS SI line。

解决方案

至少有几种可能性;）



查找和/或删除重复行 [ ^ ]

排名功能 [ ^ ]

使用DISTINCT消除重复项 [ ^ ]

如何从中删除重复的行SQL Server中的表 [ ^ ]

Hi all

Below is my query

SELECT ROW_NUMBER() OVER (ORDER BY om.OrderFormNo) AS SI ,
       om.OrderFormNo,
       om.OrderDate,
       ag.CompanyName AS Agency,
       sup.Name AS Supplier,
       dis.Name AS Distributor,
       it.ItemName,
       od.Quantity,
       od.Rate,
       odc.ReceivedQty,
       odc.ReceivedDate,
       odca.CancelQty,
       odca.CancelDate,
       od.Quantity-(odc.ReceivedQty+odca.CancelQty) AS Balance
FROM OrderMaster om
JOIN OrderDetails od ON om.OMID=od.OMIDFormRef
JOIN OrderDetailsChild odc ON od.ODID=odc.OrderDetailsIDRef
JOIN OrderDetailsCancel odca ON od.ODID=odca.OrderDetailsIDRef
JOIN ItemInfo it ON od.IIIDItemRef=it.IIID
JOIN Agency ag ON om.AIDAgencyRef=ag.AID
JOIN SupplierMainGroup sup ON om.SMGIDRef=sup.SMGID
JOIN MainDistributor dis ON om.MDIDRef=dis.MDID
WHERE sup.Name='trtrttrt'

and the output i am getting is


SI OrderFormNo OrderDate Agency Supplier Distributor ItemName Quantity Rate ReceivedQty ReceivedDate CancelQty CancelDate Balance


1 ORD2231REE 2013-11-21 Azad trtrttrt sds Shirtings 25 435 4 2013-11-21 3 2013-11-21 18

2 ORD2231REE 2013-11-21 Azad trtrttrt sds Shirtings 25 435 5 2013-11-21 3 2013-11-21 17

3 ORD2231REE 2013-11-21 Azad trtrttrt sds Shirtings 25 435 4 2013-11-21 3 2013-11-21 18

4 ORD2231REE 2013-11-21 Azad trtrttrt sds Shirtings 25 435 5 2013-11-21 3 2013-11-21 17

Now the problem is actually i am having only 2 rows i.e. SI. 1 and 2
but here it's duplicating and displaying same rows again.

Please tell me what i have to modify in the query.

Modify: I use Distinct and it's working fine only if i remove this line ROW_NUMBER() OVER (ORDER BY om.OrderFormNo) AS SI, else it's not working.
so can anyone tell me the alternate for ROW_NUMBER() OVER (ORDER BY om.OrderFormNo) AS SI line.

解决方案

There is several possibilities at least ;)

Find and/or Delete Duplicate Rows[^]
Ranking functions[^]
Eliminating Duplicates with DISTINCT[^]
How to remove duplicate rows from a table in SQL Server[^]

这篇关于从sql查询中获取重复记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！