Home>

mysql - sql get names with multiple types

Students learning languages ​​with Progate.
I'm solving a problem as part of language learning, but I'm stuck with the problem of "extracting only data with multiple types of name identification with a single name identification number".

[Image]
[Original data]

Name-matching name Name identification number
Ah 1
Yes 2
Yes 2
U 3
Eh 3
Oh 4
Oh 4
or 4
ki 5
ki 5
< 5
< 5
K 6
This 6
Sa 6

[Data to be retrieved/Extracted data]

Name-matching name Name identification number
U 3
Eh 3
Oh 4
Oh 4
or 4
ki 5
ki 5
< 5
< 5
K 6
This 6
Sa 6

I want to output the above result.

[Unnecessary data/Non-extracted data]

Name-matching name Name identification number
Ah 1
Yes 2
Yes 2

This is data that does not need to be displayed, but it is included here just in case.

I thought about SQL statements that display duplicate data and SQL statements that use exists. Cannot output.
In this case, what steps can be taken?
Please give me a professor.

Supplemental information (FW/tool version etc.)

MySQL

mysql
  • Answer # 1 

    create table tbl (name varchar (10), num int);
insert into tbl values
('A', 1),
('I', 2),
('I', 2),
('U', 3),
('E', 3),
('O', 4),
('O', 4),
('Ka', 4),
('Ki', 5),
('Ki', 5),
('Ku', 5),
('Ku', 5),
('Ke', 6),
('Ko', 6),
('Sa', 6);
select * from tbl as t1
where exists (
select 1 from tbl
group by num
having count (distinct name)>1
and num = t1.num);
    Validation

    First grouping process

    select num, count (distinct name) as cnt from tbl
group by num;
    num cnt
    1 1
    2 1
    3 2
    4 2
    5 2
    6 3

    Filter this by multiple cnts (>1)

    select num, count (distinct name) as cnt from tbl
group by num
having count (distinct name)>1
    num cnt
    3 2
    4 2
    5 2
    6 3

    End of parenthesis
    The num extracted here and the outer table num
    Use where exists to narrow down

Related articles