时间戳

首页 > 解决方案 > 选择列中的唯一值

sql - 选择列中的唯一值

问题描述

我创建了一个查询来过滤数据。我只需要在最终语句中选择不同的 inv_mast_uid。如果相同的 inv_mast_uid 重复，我只需要选择 1 行。请建议

with s1
     as (select count(supplier_part_no)as count_of_duplicates,
                supplier_part_no,
                supplier_id,
                inv.item_desc,
                extended_desc
         from   p21_view_inventory_supplier supp
                join p21_view_inv_mast inv
                  on supp.item_id = inv.item_id
         where  product_type = 'R'
                and supp.delete_flag = 'N'
                and inv.delete_flag = 'N'
                and other_charge_item = 'N'
         group  by supplier_part_no,
                   supplier_id,
                   item_desc,
                   extended_desc
         having count(supplier_part_no) > 1),
     s2 as (select supp.inv_mast_uid,
                inv.item_id,
                supplier_id,
                supplier_part_no,
                item_desc,
                extended_desc
         from   p21_view_inventory_supplier supp
                left join p21_view_inv_mast inv
                       on supp.item_id = inv.item_id)
select s2.inv_mast_uid,
       s2.item_id,
       count_of_duplicates,
       s1.supplier_part_no,
       s1.supplier_id,
       s1.extended_desc,
       s1.item_desc
from   s2
       join s1
         on s1.supplier_part_no = s2.supplier_part_no
where  s1.supplier_id = s2.supplier_id
       and s1.item_desc = s2.item_desc
       and ( s1.extended_desc = s2.extended_desc
              or s1.extended_desc is null
                 and s2.extended_desc is null )

结果是

查询输出

预期结果：

希望只有第 1 行和第 2 行。不影响其他分组功能。

添加了代码和相关输出。

标签： sqlsql-servertsqlsql-server-2012

解决方案

只需使用不同的。它将根据您选择的列返回不同的行。也就是说，重复的行将被删除。

select distinct s2.inv_mast_uid as inv_mast_uid,
   s2.item_id,
   count_of_duplicates,
   s1.supplier_part_no,
   s1.supplier_id as supplier_id,
   s1.extended_desc,
   s1.item_desc
from   s2
   join s1
     on s1.supplier_part_no = s2.supplier_part_no
where  s1.supplier_id = s2.supplier_id
   and s1.item_desc = s2.item_desc
   and ( s1.extended_desc = s2.extended_desc
          or (s1.extended_desc is null
             and s2.extended_desc is null) )

我猜你在最后一行放了错误的括号，所以在or条件中添加了那个。

但是，如果您正在寻找仅列的不同值inv_mast_uid并且不关心其他列上的值，您可以这样做：

把最后一个select写成s3，然后把select写成如下：

select p.* 
    from s3 as p
inner join (select inv_mast_uid, min(supplier_id) from
s3 group by s2.inv_mast_uid) as q
on p.inv_mast_uid= q.inv_mast_uid
    and p.supplier_id = q.supplier_id

推荐阅读