Shulou(Shulou.com)06/01 Report--

Creation of group by activity Lab sheet

Field explanation:

Xh: student number

Xh: first name

Nl: age

Create table student (xh number,xm varchar2 (4), nl int); insert into student values (1); insert into student values (2); insert into student values (3); insert into student values (4); insert into student values (5); insert into student values (6); insert into student values (7)

View tabl

SQL > select * from student; XH XM NL- 1 A 21 2 B 21 3 A 21 4 A 21 5 A 21 6 C 217 B 217 rows selected.SQL > questions and answers

Problem: the query has duplicate names.

Idea: use the count function to do statistics. If count > 1, there are duplicates.

SQL > select xm,count (*) from student group by xm having (count (*) > 1); XM COUNT (*)-A 4B 2SQL >

Question: query all the information of students with duplicate names

* idea: select from student can view the information of all students, how to check the repetition? We already know which names are duplicated above, so we just need to judge which names are in the duplicate names * *

SQL > select * from student where xm in (select xm from student group by xm having (count (*) > 1)) XH XM NL- 1 A 21 2 B 21 3 A 21 4 A 21 5 A 21 7 B 216 rows selected.SQL >

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.