CREATE TABLE TEST(account VARCHAR(32),ip VARCHAR(32),type NUMBER,address VARCHAR(32));insert into aa(account, ip, type, address) values('wang','192.168.12.1',1,'beijing');
insert into aa(account, ip, type, address) values('wang','192.168.12.2',1,'hangzhou');
insert into aa(account, ip, type, address) values('wang','192.168.12.3',1,'shanghai');
insert into aa(account, ip, type, address) values('wang','192.168.12.4',1,'shanghai');
insert into aa(account, ip, type, address) values('wang','192.168.12.1',1,'shanghai');
insert into aa(account, ip, type, address) values('wang','192.168.12.1',1,'beijing');
insert into aa(account, ip, type, address) values('wang','192.168.12.1',1,'beijing');
insert into aa(account, ip, type, address) values('wang','192.168.12.1',1,'beijing');
insert into aa(account, ip, type, address) values('fei','192.168.12.1',1,'beijing');
insert into aa(account, ip, type, address) values('fei','192.168.12.1',1,'beijing');
insert into aa(account, ip, type, address) values('fei','192.168.12.1',1,'beijing');
insert into aa(account, ip, type, address) values('fei','192.168.12.2',1,'beijing');
insert into aa(account, ip, type, address) values('zhao','192.168.12.6',1,'beijing');
insert into aa(account, ip, type, address) values('zhao','192.168.12.6',1,'beijing');
insert into aa(account, ip, type, address) values('zhao','192.168.12.6',1,'beijing');
insert into aa(account, ip, type, address) values('zhao','192.168.12.6',1,'beijing');
insert into aa(account, ip, type, address) values('zhao','192.168.12.6',1,'beijing');
insert into aa(account, ip, type, address) values('zhao','192.168.12.6',1,'beijing');我想达到这样的查询效果:
对于每一个account,统计出他所对应的不同ip的数量,最后查询出数量大于等于2的记录。
能通过一条sql实现吗?
谢谢
insert into aa(account, ip, type, address) values('wang','192.168.12.2',1,'hangzhou');
insert into aa(account, ip, type, address) values('wang','192.168.12.3',1,'shanghai');
insert into aa(account, ip, type, address) values('wang','192.168.12.4',1,'shanghai');
insert into aa(account, ip, type, address) values('wang','192.168.12.1',1,'shanghai');
insert into aa(account, ip, type, address) values('wang','192.168.12.1',1,'beijing');
insert into aa(account, ip, type, address) values('wang','192.168.12.1',1,'beijing');
insert into aa(account, ip, type, address) values('wang','192.168.12.1',1,'beijing');
insert into aa(account, ip, type, address) values('fei','192.168.12.1',1,'beijing');
insert into aa(account, ip, type, address) values('fei','192.168.12.1',1,'beijing');
insert into aa(account, ip, type, address) values('fei','192.168.12.1',1,'beijing');
insert into aa(account, ip, type, address) values('fei','192.168.12.2',1,'beijing');
insert into aa(account, ip, type, address) values('zhao','192.168.12.6',1,'beijing');
insert into aa(account, ip, type, address) values('zhao','192.168.12.6',1,'beijing');
insert into aa(account, ip, type, address) values('zhao','192.168.12.6',1,'beijing');
insert into aa(account, ip, type, address) values('zhao','192.168.12.6',1,'beijing');
insert into aa(account, ip, type, address) values('zhao','192.168.12.6',1,'beijing');
insert into aa(account, ip, type, address) values('zhao','192.168.12.6',1,'beijing');我想达到这样的查询效果:
对于每一个account,统计出他所对应的不同ip的数量,最后查询出数量大于等于2的记录。
能通过一条sql实现吗?
谢谢
解决方案 »
- oracle链接sqlserver问题
- 请问,表里有ip字符串字段,如何查询某段的IP地址呢
- Oracle中查询昨天00:00到今天00:00的sql怎么写?
- Oracle10g建表权限管理服务一系列问题
- 我想把SqA返回給某個變量,要怎么實現啊
- dmp文件能够进行分析么??
- 出现“数据库连接失败,DBMS 073 ORACLE V7.3 is not supported in your current installation”,请问大家是什么原因?
- 请教oracle的jdbc驱动程序的类路径如何设置?
- 在线等待!如何知道update影响的行数,要求在客户端的sql里实现,不能调用服务器端存贮过程
- Oracle怎么实现分组后每组单独一列显示?
- 跪求 社交网站数据库设计(类型人人网,FACEBOOK)
- pl/sql怎么能够一次性替换DataType
FROM test t
GROUP BY t.account
HAVING COUNT(DISTINCT t.ip) >= 2;