修改存储过程后消除重复行

Question

我们的采购部门希望我修改我们的申请，以便使用零件号搜索请购单。

每个请购单将至少包含一个部分，但可能包含多个部分。

存储过程设置为可以填充字段，也可以不填充字段，因此它根据传入的内容提取数据。

以下是存储过程在我修改之前的工作方式：

ALTER PROCEDURE [dbo].[GetRequisitions](@status_id int, @startdate varchar(50), @enddate varchar(50),
  @Vendor_Name varchar(200), @PO_Number varchar(50), @Req_ID int, @datesearch bit, @Part_ID varchar(50)) AS
BEGIN
  SET NOCOUNT ON;
  select
    a.Req_ID, a.Vendor_ID, a.Vendor_Name, a.Req_Date, a.PO_Number, a.Requested_Buy, a.approved,
    b.status,
    c.supervisor_login,
    case when d.req_id is not null then 1 else 0 end as HasDocs
  from Req as a
    join Status as b on (a.Status_ID=b.Status_ID)
    left outer join Supervisor as c on (a.Requested_Login=c.Emp_Login)
    left outer join req_doc as d on (a.Req_ID=d.req_id)
  where
    ((@datesearch is null or @datesearch=0) or (a.Req_Date between @startdate and @enddate))
    and ((@status_id is null or @status_id=0) or (a.Status_ID=@status_id))
    and ((@Vendor_Name is null or LEN(RTrim(@Vendor_Name))=0) or (a.Vendor_Name like '%'+@Vendor_Name+'%'))
    and ((@PO_Number is null or LEN(RTrim(@PO_Number))=0) or (a.PO_Number like @PO_Number+'%'))
    and ((@Req_ID is null or @Req_ID=0) or (a.Req_ID=@Req_ID))
END

为了使用可选的零件号Part_ID字段查询同一数据集，我添加了以下与Req_PartDataTable连接的代码：

ALTER PROCEDURE [dbo].[GetRequisitions](@status_id int, @startdate varchar(50), @enddate varchar(50),
  @Vendor_Name varchar(200), @PO_Number varchar(50), @Req_ID int, @datesearch bit, @Part_ID varchar(50)) AS
BEGIN
  SET NOCOUNT ON;
  select
    a.Req_ID, a.Vendor_ID, a.Vendor_Name, a.Req_Date, a.PO_Number, a.Requested_Buy, a.approved,
    b.status,
    c.supervisor_login,
    case when d.req_id is not null then 1 else 0 end as HasDocs
  from Req as a
    join Status as b on (a.Status_ID=b.Status_ID)
    left outer join Supervisor as c on (a.Requested_Login=c.Emp_Login)
    left outer join req_doc as d on (a.Req_ID=d.req_id)
    join Req_Part e on (a.Req_ID=e.Req_ID)
  where
    ((@datesearch is null or @datesearch=0) or (a.Req_Date between @startdate and @enddate))
    and ((@status_id is null or @status_id=0) or (a.Status_ID=@status_id))
    and ((@Vendor_Name is null or LEN(RTrim(@Vendor_Name))=0) or (a.Vendor_Name like '%'+@Vendor_Name+'%'))
    and ((@PO_Number is null or LEN(RTrim(@PO_Number))=0) or (a.PO_Number like @PO_Number+'%'))
    and ((@Req_ID is null or @Req_ID=0) or (a.Req_ID=@Req_ID))
    and ((@Part_ID is null or LEN(RTrim(@Part_ID))=0) or (e.Part_ID like '%'+@Part_ID+'%'))
END

停止返回重复行的最佳方法是什么

而且，仅供参考：我没有设计这个数据库！我知道列名Requested_Buy的拼写应该正确为Requested_By，但数据库已经在生产中了。错字真让我恼火！

Answer 1

如果我理解正确，您可以像这样更新查询。

  select
    a.Req_ID, a.Vendor_ID, a.Vendor_Name, a.Req_Date, a.PO_Number, a.Requested_Buy, a.approved,
    b.status,
    c.supervisor_login,
    case when d.req_id is not null then 1 else 0 end as HasDocs
  from Req as a
    join Status as b on (a.Status_ID=b.Status_ID)
    left outer join Supervisor as c on (a.Requested_Login=c.Emp_Login)
    left outer join req_doc as d on (a.Req_ID=d.req_id)
  where
    ((@datesearch is null or @datesearch=0) or (a.Req_Date between @startdate and @enddate))
    and ((@status_id is null or @status_id=0) or (a.Status_ID=@status_id))
    and ((@Vendor_Name is null or LEN(RTrim(@Vendor_Name))=0) or (a.Vendor_Name like '%'+@Vendor_Name+'%'))
    and ((@PO_Number is null or LEN(RTrim(@PO_Number))=0) or (a.PO_Number like @PO_Number+'%'))
    and ((@Req_ID is null or @Req_ID=0) or (a.Req_ID=@Req_ID))
    and ( ISNULL(@Part_ID,0) = 0 
            OR 
          EXISTS(SELECT NULL FROM Req_Part e WHERE a.Req_ID=e.Req_ID AND e.Part_ID like '%'+@Part_ID+'%')
        )