【SQL】查询部门有2个人或以上员工的部门号

查询部门有2个人或以上员工的部门号

id      payno
1       lisi
1       zhangsan
2       wangmeng
3       zhaoliu
3       wuxia
3       mengfang
4       guodaxia
5       guofurong

结果如下：

id
1
3

解决：
方法一：

SELECT DISTINCT id
  FROM(
       SELECT id
             ,payno
             ,row_number () over (PARTITION BY id ORDER BY payno) AS rn
         FROM gorder
       ) a
 WHERE  rn = 2
;

 

方法二：

select id
  from (
        select id
              ,count(payno) as c
          from gorder
         group by id
        )a
 where c >= 2
;

备注：建表和数据
create table gorder(id int,payno varchar(10));
insert into gorder values(1,'lisi');
insert into gorder values(1,'zhangsan');
insert into gorder values(2,'wangmeng');
insert into gorder values(3,'zhaoliu');
insert into gorder values(3,'wuxia');
insert into gorder values(3,'mengfang');
insert into gorder values(4,'guodaxia');
insert into gorder values(5,'guofurong');