How to remodel a correlated nested subquery to non-correlated?

0 like 0 dislike
6 views
There is a table with the athletes,it is the id field of sportsman unique not null and type of sport, you need to bring those who engaged in more than one sport. how to do this without a correlated subquery?(in other words take a repeating table row as many times as they are repeated)
Is it possible to more efficiently perform this query?
My code
select pair_sports.fio ,pair_sports.sport from fitness.pair_sports where fio=any (SELECT pair_sports.fio FROM fitness.pair_sports group by (fio) having count(*)>1) order by fio
by | 6 views

1 Answer

0 like 0 dislike
In order that it would be possible to do the analysis, create a circuit with the desired DBMS on the website sqlfiddle.com and left a link to his scheme, then we'll see. In a preliminary analysis, see that the question of categories in MYSQL since I did not work with this DBMS, that will try to write a sample simple SQL and something like MYSQL.

1) Simple and readable option:
select t.* from fitness.pair_sports t where exists ( select t2.fio from fitness.pair_sports t2 where t2.fio = t.fio group by t2.fio having count(*) > 1 );


2) What is muddy may work
select * from ( select @rownum:= case when t.fio <> @name then 0 else 1 end as rn, @name:=t.fio as name, t.* fitness.pair_sports t order by t.fio ) tt where tt.rn = 1;
by

Related questions

0 like 0 dislike
1 answer
0 like 0 dislike
2 answers
0 like 0 dislike
2 answers
0 like 0 dislike
4 answers
asked Mar 21, 2019 by Tiradoir
110,608 questions
257,186 answers
0 comments
32,718 users