sql - 避免使用连接在 SQL 中显示重复记录

Question

我有一个问题，我需要在执行查询时避免重复记录。这是我的查询，我需要避免显示突出显示的行。我想改为显示 NULLS，那是因为我对报告中的记录求和导致错误的总和，从而使所有计算错误。突出显示的记录是上述记录的重复，如图所示。

declare

    @DateFrom Date = '07/30/2019',
    @DateTo   Date='07/30/2019',
    @PharmacyId int =13,
    @VendorId int = 0,
    @CategoryId int = 0,
    @Brand varchar(100) = '',
    @Agent varchar(100) = '',
    @ControlType varchar(100) = '',
    @ProductId as varchar(MAX)=''



select 

irm.PharmaReturnId as ReturnId,
irr.RecStockID,        
     pm.ProductName ,
     s.BatchNo,
     CONVERT(varchar(10),s.ExpiryDate,105) as ExpiryDate,
     CONVERT(varchar(10), irm.ReturnedDate,105) as ReturnedDate,
     ird.CostPrice,
    --ISNULL(ird.TotalCostVat,0) as TotalCostVat,
     (ird.CostPrice * ird.ReturnedQty)*(vt.Cost/100) as TotalCostVat,
     (ird.CostPrice * ird.ReturnedQty)*(vt.Sale/100) as TotalSaleVat,

     ird.SalePrice, 
    -- ird.TotalSaleVat,
     ird.ReturnedQty as ReturnQty, 
     irr.Quantity as ReturnRecived, 
    SUM(ird.ReturnedQty) AS TotalReturn, 
    ss.[Name] as StatusName,
    --t.[Type] as TypeName,
    CASE WHEN irm.FromTo_Type_Id = 25 THEN 'Merchandise' WHEN irm.FromTo_Type_Id = 26 THEN 'Debit Note' ELSE '' END as TypeName,
    --irm.ReturnMode, 
CONVERT(decimal(18,4), ird.CostPrice*ird.ReturnedQty) as TotalAmount,
    ISNULL
                             ((SELECT Name
                                 FROM tbl_Vendor
                                 WHERE (ID = irm.To_Id)), '') AS VendorPharmacy,
                                  pm_1.ProductName AS ProductRec,
                                  irr.DateCreated, 
                                  irr.Quantity, 
                                  irr.BatchNumber AS BatchNoRec,
                                  CONVERT(varchar(10), irr.ExpiryDate,105) as ExpiryDateRec,
                                  irr.CostPrice as CostPriceRec, 
                                  irr.SalePrice as SalePriceRec, 
                                  v.Name AS VendorRec,
                         CONVERT(decimal(18,4), irr.Quantity*irr.CostPrice) as TotalAmountRec
                         ,irm.To_Id

 from tbl_ItemReturnMaster irm
inner join tbl_ItemReturnDetail as ird on irm.Id=ird.ReturnId and irm.From_Id=ird.PharmaId
left join tbl_ItemReturnReceive as irr on irr.ReturnDetailId=ird.Id and irr.PharmaId=ird.PharmaId
inner join tbl_ProductMaster pm on pm.Id=ird.ProductID
left join tbl_ProductMaster pm_1 on pm_1.Id=irr.ProductId
inner join tbl_Vendor v on v.Id=irm.To_Id
inner join tbl_SubCategory sb on sb.Id = pm.SubCategoryId
inner join tbl_Stock s on s.Id = ird.StockId
inner join tbl_Vat vt on vt.Id = ird.VatId
inner join tbl_Status ss on irm.StatusId = ss.Id


 where cast(irm.ReturnedDate as date) BETWEEN cast(@DateFrom as date) AND cast(@DateTo as date)
                        AND (@CategoryId = 0 OR sb.CategoryId = @CategoryId) AND
                        (@Brand = '' OR @Brand is null OR pm.BrandName = @Brand) AND
                        (@Agent = '' OR @Agent is null OR pm.Distributor = @Agent) AND 
                        (@ControlType = '' OR @ControlType is null OR pm.[Control] = @ControlType)
                        AND (@ProductId='' OR @ProductId is null OR ird.ProductId in (select * from dbo.fnSplit(@ProductId,',')))
                        AND (@VendorId = 0 OR irm.To_Id = @VendorId)
                        AND irm.From_Id=@PharmacyId
                        AND irm.FromTo_Type_Id in (25,26)

GROUP BY 
--irr.ReturnQty,
--irr.ReturnRecived,
pm.ProductName,
s.BatchNo, 
ird.ReturnedQty,
ird.CostPrice,
ird.SalePrice,
s.ExpiryDate,
irr.RecStockID, 
ird.ProductID,
--irm.ReturnMode,
irm.To_Id,
pm_1.ProductName,
irr.DateCreated,
irr.Quantity, 
irr.BatchNumber,
irr.ExpiryDate,
irm.ReturnedDate,
irr.CostPrice,
irr.SalePrice,
v.Name,
ird.CostPrice,
ird.SalePrice,
--ird.TotalCostVat,
--ird.TotalSaleVat,
irr.VendorID,
vt.Cost,
vt.Sale,
irm.PharmaReturnId,
ss.[Name],
s.Id,
irm.FromTo_Type_Id

order by irm.PharmaReturnId asc

This query return the following records.

标签： sqlsql-server