英文:
More than one occurrence from table
问题
create table customer (cif number, name varchar(20), mobile number);
insert into table customer values(121, 'ANT', 789);
insert into table customer values(122, 'ANT', 789);
insert into table customer values(123, 'ENT', 789);
insert into customer values(124, 'ENT', 789);
insert into customer values(125, 'BEE', 123);
insert into customer values(126, 'BEE', 123);
insert into customer values(127, 'BRO', 789);
insert into customer values(128, 'FIO', 789);
commit;
我想根据名称和手机号从客户表中检索多次出现的数据。
有人能帮我吗？
结果如下图所示。
英文:
create table customer (cif number, name varchar(20),mobile number);
insert into table customer values(121,'ANT',789);
insert into table customer values(122,'ANT',789);
insert into table customer values(123,'ENT',789);
insert into customer values(124,'ENT',789);
insert into customer values(125,'BEE',123);
insert into customer values(126,'BEE',123);
insert into customer values(127,'BRO',789);
insert into customer values(128,'FIO',789);
commit;
I want retrieve data from customer table based on name and mobile more than one occurrences.
Can anyone help me out
Result like
答案1
得分: 2
你可以使用COUNT()聚合函数作为分析函数，同时通过使用PARTITION BY子句对这些列进行分组，如下所示：
SELECT cif, name, mobile
FROM (SELECT c.*,
COUNT(*) OVER (PARTITION BY name, mobile) AS cnt
FROM customer c)
WHERE cnt > 1
英文:
You can use COUNT() aggregation as Analytic function along with grouping by those columns through use of PARTITION BY clause as
SELECT cif, name, mobile
FROM (SELECT c.*,
COUNT(*) OVER (PARTITION BY name, mobile) AS cnt
FROM customer c )
WHERE cnt > 1
<kbd>Demo</kbd>
答案2
得分: 1
SQL> select * From customer;
CIF NAME MOBILE
121 ANT 789
122 ANT 789
123 ENT 789
124 ENT 789
125 BEE 123
126 BEE 123
127 BRO 789
128 FIO 789
8 rows selected.
根据您发布的结果，您正在寻找不是在name和mobile方面唯一的行。如果是这样，这里有另一个选项：
SQL> select *
2 from customer a
3 where exists (select null
4 from customer b
5 where b.name = a.name
6 and b.mobile = a.mobile
7 group by b.name, b.mobile
8 having count(*) > 1
9 );
CIF NAME MOBILE
121 ANT 789
122 ANT 789
123 ENT 789
124 ENT 789
125 BEE 123
126 BEE 123
6 rows selected.
SQL>
英文:
Source:
SQL> select * From customer;
CIF NAME MOBILE
---------- -------------------- ----------
121 ANT 789
122 ANT 789
123 ENT 789
124 ENT 789
125 BEE 123
126 BEE 123
127 BRO 789
128 FIO 789
8 rows selected.
Based on result you posted, you're looking for rows that aren't unique per name and mobile. If that's so, here's another option:
SQL> select *
2 from customer a
3 where exists (select null
4 from customer b
5 where b.name = a.name
6 and b.mobile = a.mobile
7 group by b.name, b.mobile
8 having count(*) > 1
9 );
CIF NAME MOBILE
---------- -------------------- ----------
121 ANT 789
122 ANT 789
123 ENT 789
124 ENT 789
125 BEE 123
126 BEE 123
6 rows selected.
SQL>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论