背景
数据库系统:Oracle
表结构:
IP_ADDRESS | IP_TYPE | IP_STATUS | IP_USERNAME | IP_DEVICE | IP_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
的报错:我们需要查找一人有多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
3select 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