SQL: wybierz wiersze z wartością kolumny, która występuje co najmniej N razy?
Załóżmy, że mam tabelę SQL "Celebrities" z dwoma kolumnami: "fname " i"lname":
fname | lname
---------+-------
Bill | Clinton
Bill | Gates
George | Bush
George | Clinton
Barack | Obama
Chciałbym napisać zapytanie, które zwraca imię i nazwisko każdej osoby w tabeli, której nazwisko pojawia się co najmniej dwa razy w kolumnie "lname". Jak napisać to zapytanie SQL?
22
Author: Daniel Allen Langdon, 2011-04-23
3 answers
SELECT fname, lname FROM Celebrities
WHERE lname IN
(SELECT lname FROM Celebrities
GROUP BY lname HAVING COUNT (lname) >1)
52
Author: Ocaso Protal,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-04-22 21:46:02
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-04-22 21:46:02
Using a JOIN:
SELECT a.*
FROM CELEBRITIES a
JOIN (SELECT c.lname
FROM CELEBRITIES c
GROUP BY c.lname
HAVING COUNT(*) >= 2) b ON b.lname = a.lname
Użycie EXISTS:
SELECT a.*
FROM CELEBRITIES a
WHERE EXISTS (SELECT NULL
FROM CELEBRITIES c
WHERE c.lname = a.lname
GROUP BY c.lname
HAVING COUNT(*) >= 2)
9
Author: OMG Ponies,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-04-22 21:46:17
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-04-22 21:46:17
select fname, lname
from
(
select fname, lname, count(*) over(partition by lname) as lcount
from Celebrities
) as S
where lcount > 1
Testowane w SQL Server 2008. Może działać w innych DBMS, które obsługują count(*) over(...)
2
Author: Mikael Eriksson,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-04-22 21:55:59
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-04-22 21:55:59