通过特定列从表中查找不同值的方法

Question

下面是一个由如下数据组成的表：

StandardName|Username    |RType
------------|------------|--------
Department  |Department  |  Position
Division    |Division    |  Entity
Division    |Division    |  Position
Plant       |Plant       |  Entity
Section     |Section     |  Position
SubDivision |Sub-Division|  Entity
SubDivision |Subdivision |  Position
SubSection  |Subsection  |  Position
Unit        |Unit        |  Entity

我希望所有的StandardName和UserName后面跟着Rtype = 'Entity‘，如果是Rtype =’RType‘，它只会得到与RType 'Entity’无关的StandardName

为此，我执行了如下的查询

SELECT DISTINCT u.StandardName, u.UserName ,ISNULL(e.RType,'position') AS RType
from (
     SELECT  DISTINCT StandardName,UserName
     from Table1 AS ee where   RType = 'Entity'  
     UNION
     SELECT  DISTINCT StandardName, UserName
     from Table1 AS pp where  RType = 'position' 
) u
LEFT OUTER JOIN (
     select UserName,StandardName,RType 
     from Table1 WHERE RType='Entity' 
) e on e.StandardName = u.StandardName
 ORDER BY Rtype

那么输出是

StandardName|Username    |RType
------------|------------|--------
Division    |Division    |  Entity
Plant       |Plant       |  Entity      
SubDivision |Sub-Division|  Entity
SubDivision |Subdivision |  Entity       
Unit        |Unit        |  Entity
Department  |Department  |  Position
Section     |Section     |  Position
SubSection  |Subsection  |  Position

在这里，在“SubDivision”的情况下，它会出现两次，而它需要在“实体”RType中出现一次。

预期产出应是--

StandardName|Username    |RType
------------|------------|--------
Division    |Division    |  Entity
Plant       |Plant       |  Entity      
SubDivision |Sub-Division|  Entity            
Unit        |Unit        |  Entity
Department  |Department  |  Position
Section     |Section     |  Position
SubSection  |Subsection  |  Position

SQLFIDDLE 链接

Answer 1

这是一个从您的需求转换而来的sql语句：

 -- gimme all things that are entity
 select StandardName, UserName, RType 
   from Table1
   where RType = 'Entity'  
 union   
 -- and all things that have no entity-entry
   select StandardName, UserName, RType from Table1 as k
   where rtype = 'Position' and not exists (
       select 1 
       from table1 as t 
       where t.standardname = k.standardname 
         and t.rtype = 'Entity'  
   )

没有你的“区别”，但你得到的结果是：

StandardName    UserName        RType
----------------------------------------
Division        Division        Entity
Plant           Plant           Entity
SubDivision     Sub-Division    Entity
Unit            Unit            Entity
Department      Department      Position
Section         Section         Position
SubSection      Subsection      Position

您没有任何重复的'Entity'-entries，所以没有不同的要求。

Answer 2

这是一个优先级查询。Patrick的方法是一个很好的方法，特别是在只有两种类型的情况下。

另一种方法使用row_number()

select e.StandardName, e.Username, e.RType
from (select e.*,
             row_number() over (partition by StandardName
                                order by (case RType when 'Entity' then 1 when 'Position' then 2 else 3 end)
                               ) as seqnum
      from employees e
     ) e
where seqnum = 1;

Answer 3

对于每个StandardName，您都希望行带有“Entity”，但如果不存在，则行中包含“exist”。对于ROW_NUMBER来说，这是一个简单的任务：

with cte as 
 ( 
   select
      StandardName
     ,Username
     ,RType
     ,row_number() 
      over (partition by StandardName  -- for each StandardName
            order by RType) as rn      -- sort to get Entity first
   from Table1
 )
select
   StandardName
  ,Username
  ,RType
from cte
where rn = 1
order by
   RType
  ,Username
;

请参阅小提琴