代码之家  ›  专栏  ›  技术社区  ›  Nicky

写一个查询来显示部门的Id、名称和编号?

  •  0
  • Nicky  · 技术社区  · 7 年前

    ID      Name     Department
    --      ----     ----------
    1       Sam      HR
    1       Sam      FINANCE
    2       Ron      PAYROLL
    3       Kia      HR
    3       Kia      IT
    

    结果:

    ID      Name     Department
    --      ----     ----------
    1       Sam               2
    3       Kia               2
    

    我试过用 group by id 以及使用 count(*) ,但查询给出错误。

    我该怎么做?

    3 回复  |  直到 7 年前
        1
  •  2
  •   Littlefoot    7 年前

    没有看到您的查询,盲目猜测是您错误地编写了 GROUP BY 子句(如果您使用它)并忘记包含 HAVING 条款。

    SQL> with test (id, name, department) as
      2    (select 1, 'sam', 'hr'      from dual union
      3     select 1, 'sam', 'finance' from dual union
      4     select 2, 'ron', 'payroll' from dual union
      5     select 3, 'kia', 'hr'      from dual union
      6     select 3, 'kia', 'it'      from dual
      7    )
      8  select id, name, count(*)
      9  from test
     10  group by id, name
     11  having count(*) > 1
     12  order by id;
    
            ID NAM   COUNT(*)
    ---------- --- ----------
             1 sam          2
             3 kia          2
    
    SQL>
    
        2
  •  1
  •   Kamil Gosciminski    7 年前

    你说得对 count() . 不过,您需要按其他列进行分组,只计算唯一的部门,然后在having子句中根据数字进行筛选。

    select id, name, count(distinct department) as no_of_department
    from table
    group by id, name
    having count(distinct department) > 1
    

    select *
    from (
      select id, name, count(distinct department) over (partition by id, name) as no_of_department
      from table
    ) t
    where no_of_department > 1
    
        3
  •  1
  •   Yogesh Sharma    7 年前

    你可以用 窗口 函数 subquery

    select distinct id, name, Noofdepartment
    from (select t.*, count(*) over (partition by id,name) Noofdepartment
          from table t
         ) t
    where Noofdepartment > 1;
    

    但是,也可以使用 group by

    select id, name, count(*) as Noofdepartment
    from table t
    group by id, name
    having count(*) > 1;