oracle之查询重复项

发表于 | 分类于 ， | 评论数： | 阅读次数：

背景

数据库系统：Oracle

表结构：

IP_ADDRESSIP_TYPEIP_STATUSIP_USERNAMEIP_DEVICEIP_DESCRIPTION

现需要找出有用多个IP地址的用户，并列出他的姓名（IP_USERNAME）、IP地址（IP_ADDRESS）和IP类型（IP_TYPE）

思路

  • 首先需要根据用户姓名进行分组排序，那么就得用到group by。

    1
    select il.ip_username, count(il.ip_username) from ip_list il where il.ip_status = '已分配' group by il.ip_username;

    注意：group by在使用中需要注意的是，前面select的内容，除聚合函数（如count()）外，必须出现在group by后面，否则会有系统报错。如：

    1
    select * from ip_list il where il.ip_status = '已分配' group by il.ip_username;

    就会出现ORA-00979的报错：

    ora-00979

  • 我们需要查找一人有多IP的清单，那么可以直接使用having关键来筛选：

    1
    select il.ip_username from ip_list il where il.ip_status = '已分配' group by il.ip_username having count(1) > 1;

  • 由于我们需要查询出姓名、IP、类型三个信息，如将这三个字段都列在group by后，那么将无法获取针对姓名的count()计算，如：

    1
    select il.ip_username, count(il.ip_username) from ip_list il where il.ip_status = '已分配' group by il.ip_username having count(il.ip_username) > 1;

    该查询语句不会报错，但会查询出空结果。

    所以只能采用子查询的方式，先查出来所有需要输出的人员清单，用in来再次查询详细信息：

    1
    2
    3
    select il1.ip_username, il1.ip_address, il1.ip_type from ip_list il1 
    where il1.ip_username in 
    (select il.ip_username from ip_list il where il.ip_status = '已分配' group by il.ip_username having count(il.ip_username) > 1)

拓展

这个查询用到了group by 、having、in三种，其中group by中查询列不能多于group by后的列，需要注意，更多信息可查看：

group by:https://www.runoob.com/sql/sql-groupby.html

having:https://www.runoob.com/sql/sql-having.html