选择特定行但基于另一个列角色

5 回答

• 0

  UNION ALL将取代这个地方

  select * from table 
  where role <> 'All'
  union all
  select * from table t
  where not exists (
      select 1 from table 
      where id = t.id and [comp.] = t.[comp.] and role <> 'All'
  )
  order by 1
  

  结果：

  ID  Comp.   Role
  1   abc    Sales
  2   def    All
  3   zeh    IT
  

  回复于 2024-05-06T00:28:37+08:00
• 0

  嗯 . . . 我认为这样做你想要的：

  select t.*
  from t
  where t.role = 'Sales'
  union all
  select t.*
  from t
  where t.role <> 'Sales' and
        not exists (select 1 from t t2 where t2.id = t.id);
  

  回复于 2024-05-06T00:28:37+08:00
• 0

  如果您只有2个Role值（'Sales'和'All'），这将有效

  create table #tmp(ID INT,Comp VARCHAR(10),Role VARCHAR(10))
  
      insert into #tmp
      SELECT 1,'abc','All'
      union ALL
      SELECT 1,'abc','Sales'
      union ALL
      SELECT 2,'def','All'
  
  
      select ID,Comp,MAX(Role) As Role from #tmp
      Group by Id,comp
  
      drop table #tmp
  

  回复于 2024-05-06T00:28:37+08:00
• 0

  如果你有其他功能，你应该添加到 CASE WHEN

  SELECT
      ID
      , Comp
      , CASE WHEN role_id = 0 THEN 'All'
             WHEN role_id = 1 THEN 'Sales'
             WHEN role_id = 2 THEN 'TI' 
             ELSE NULL END AS Role
  FROM
      (
      SELECT
          ID
          , Comp
          , MAX(CASE WHEN Role = 'All' THEN 0
                     WHEN Role = 'Sales' THEN 1
                     WHEN Role = 'TI' THEN 2 END 
                     ELSE -1 END) AS role_id
      FROM 
          t 
      GROUP BY
          ID
          , Comp
      )tmp
  

  回复于 2024-05-06T00:28:37+08:00
• 0

  根据我的理解

  SELECT ID,Comp,Role
  FROM table1
  WHERE ID IN(SELECT ID FROM table1 GROUP BY ID) AND Role != 'All'
  
  UNION
  SELECT ID,Comp,Role
  FROM table1
  WHERE ID IN(SELECT ID FROM table1 GROUP BY ID HAVING COUNT(ID)=1)
  

  这工作正常检查demo

  回复于 2024-05-06T00:28:37+08:00