MySQL wybierz jedną kolumnę DISTINCT, z odpowiadającymi innymi kolumnami

ID   FirstName   LastName
1      John        Doe
2      Bugs        Bunny
3      John        Johnson

Chcę wybrać DISTINCTwyniki z FirstNamekolumny, ale potrzebuję odpowiedniego IDi LastName.

Zestaw wyników musi pokazywać tylko jeden John, ale z ID1 i a LastNameDoe.

spróbuj tego zapytania

 SELECT ID, FirstName, LastName FROM table GROUP BY(FirstName)

Słowo DISTINCTkluczowe tak naprawdę nie działa w oczekiwany sposób. Podczas używania SELECT DISTINCT col1, col2, col3faktycznie wybierasz wszystkie unikalne krotki {col1, col2, col3}.

Aby uniknąć potencjalnie nieoczekiwanych wyników podczas używania GROUP BYbez funkcji agregującej, co jest używane w zaakceptowanej odpowiedzi , ponieważ MySQL może dowolnie pobierać DOWOLNĄ wartość w zbiorze danych, który jest pogrupowany, gdy nie używa funkcji agregującej [sic] i ma problemy ONLY_FULL_GROUP_BY. Proszę rozważyć skorzystanie z wykluczenia.

Wyłączenie przyłączenia - jednoznaczne podmioty

Zakładając, że imię i nazwisko są jednoznacznie indeksowane (jednoznaczne) , alternatywą GROUP BYjest sortowanie za pomocą a LEFT JOINdo filtrowania zestawu wyników, zwanego inaczej JOIN wykluczenia.

Zobacz demonstrację

Porządek rosnący (AZ)

Aby pobrać odrębne imię uporządkowane według nazwiska z AZ

Pytanie

SELECT t1.*
FROM table_name AS t1
LEFT JOIN table_name AS t2
ON t1.firstname = t2.firstname
AND t1.lastname > t2.lastname
WHERE t2.id IS NULL;

Wynik

| id | firstname | lastname |
|----|-----------|----------|
|  2 |      Bugs |    Bunny |
|  1 |      John |      Doe |

Malejąco (ZA)

Aby pobrać odrębne imię uporządkowane według nazwiska z ZA

Pytanie

SELECT t1.*
FROM table_name AS t1
LEFT JOIN table_name AS t2
ON t1.firstname = t2.firstname
AND t1.lastname < t2.lastname
WHERE t2.id IS NULL;

Wynik

| id | firstname | lastname |
|----|-----------|----------|
|  2 |      Bugs |    Bunny |
|  3 |      John |  Johnson |

Następnie możesz uporządkować wynikowe dane według potrzeb.

Wyłączenie dołączenia - niejednoznaczne byty

Jeśli kombinacja imienia i nazwiska nie jest unikalna (niejednoznaczna) i masz wiele wierszy o tych samych wartościach, możesz filtrować zestaw wyników, dołączając warunek OR do kryteriów JOIN, aby również filtrować według identyfikatora.

Zobacz demonstrację

dane nazwa_tabeli

(1, 'John', 'Doe'),
(2, 'Bugs', 'Bunny'),
(3, 'John', 'Johnson'),
(4, 'John', 'Doe'),
(5, 'John', 'Johnson')

Pytanie

SELECT t1.*
FROM table_name AS t1
LEFT JOIN table_name AS t2
ON t1.firstname = t2.firstname
AND (t1.lastname > t2.lastname
OR (t1.firstname = t1.firstname AND t1.lastname = t2.lastname AND t1.id > t2.id))
WHERE t2.id IS NULL;

Wynik

| id | firstname | lastname |
|----|-----------|----------|
|  1 |      John |      Doe |
|  2 |      Bugs |    Bunny |

Zamówione podzapytanie

EDYTOWAĆ

Moja oryginalna odpowiedź przy użyciu uporządkowanego podzapytania została napisana przed MySQL 5.7.5 , co nie ma już zastosowania z powodu zmian w ONLY_FULL_GROUP_BY. Zamiast tego skorzystaj z powyższych przykładów dołączania wykluczeń.

Należy również zauważyć; gdy ONLY_FULL_GROUP_BYjest wyłączony (oryginalne zachowanie przed MySQL 5.7.5) , użycie GROUP BYfunkcji agregującej może dawać nieoczekiwane wyniki, ponieważ MySQL może dowolnie wybierać DOWOLNĄ wartość w zbiorze danych, który jest pogrupowany [sic] .

Oznacza to, że można pobrać wartość IDlub, która nie jest powiązana z pobranym wierszem.lastnamefirstname

OSTRZEŻENIE

Z MySQL GROUP BYmoże nie przynieść oczekiwanych rezultatów, gdy jest używany zORDER BY

Zobacz przykład przypadku testowego

Najlepszą metodą implementacji, aby zapewnić oczekiwane wyniki, jest filtrowanie zakresu zestawu wyników przy użyciu uporządkowanego podzapytania.

dane nazwa_tabeli

(1, 'John', 'Doe'),
(2, 'Bugs', 'Bunny'),
(3, 'John', 'Johnson')

Pytanie

SELECT * FROM (
    SELECT * FROM table_name ORDER BY ID DESC
) AS t1
GROUP BY FirstName

Wynik

| ID | first |    last |
|----|-------|---------|
|  2 |  Bugs |   Bunny |
|  3 |  John | Johnson |

Porównanie

Aby zademonstrować nieoczekiwane wyniki podczas używania GROUP BYw połączeniu zORDER BY

Pytanie

SELECT * FROM table_name GROUP BY FirstName ORDER BY ID DESC

Wynik

| ID | first |  last |
|----|-------|-------|
|  2 |  Bugs | Bunny |
|  1 |  John |   Doe |

SELECT ID,LastName 
From TABLE_NAME 
GROUP BY FirstName 
HAVING COUNT(*) >=1

SELECT firstName, ID, LastName from tableName GROUP BY firstName

Co powiesz na

`SELECT 
    my_distinct_column,
    max(col1),
    max(col2),
    max(col3)
    ...
 FROM
    my_table 
 GROUP BY 
    my_distinct_column`

Nie jestem pewien, czy możesz to zrobić za pomocą MySQL, ale możesz użyć CTE w T-SQL

; WITH tmpPeople AS (
 SELECT 
   DISTINCT(FirstName),
   MIN(Id)      
 FROM People
)
SELECT
 tP.Id,
 tP.FirstName,
 P.LastName
FROM tmpPeople tP
JOIN People P ON tP.Id = P.Id

W przeciwnym razie może być konieczne użycie tabeli tymczasowej.

Jak zauważył fyrye , zaakceptowana odpowiedź dotyczy starszych wersji MySQL, w których ONLY_FULL_GROUP_BYjeszcze nie została wprowadzona. Z MySQL 8.0.17 (użytym w tym przykładzie), chyba że wyłączysz ONLY_FULL_GROUP_BY, pojawi się następujący komunikat o błędzie:

mysql> SELECT id, firstName, lastName FROM table_name GROUP BY firstName;

BŁĄD 1055 (42000): Wyrażenie nr 1 listy SELECT nie znajduje się w klauzuli GROUP BY i zawiera niezagregowaną kolumnę „mydatabase.table_name.id”, która nie jest funkcjonalnie zależna od kolumn w klauzuli GROUP BY; jest to niezgodne z trybem_sql = only_full_group_by

Jednym ze sposobów obejścia tego, o którym nie wspomina Fyrye , ale opisano w https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html , jest zastosowanie ANY_VALUE()funkcji do kolumn, które są nie w GROUP BYklauzuli ( idiw lastNametym przykładzie):

mysql> SELECT ANY_VALUE(id) as id, firstName, ANY_VALUE(lastName) as lastName FROM table_name GROUP BY firstName;
+----+-----------+----------+
| id | firstName | lastName |
+----+-----------+----------+
|  1 | John      | Doe      |
|  2 | Bugs      | Bunny    |
+----+-----------+----------+
2 rows in set (0.01 sec)

Jak napisano w wyżej wymienionych dokumentach,

W takim przypadku MySQL ignoruje niedeterminizm wartości adresowych w każdej grupie nazw i akceptuje zapytanie. Może to być przydatne, jeśli po prostu nie obchodzi Cię, która wartość nieagregowanej kolumny jest wybrana dla każdej grupy. ANY_VALUE()nie jest funkcją agregującą, w przeciwieństwie do funkcji takich jak SUM()lub COUNT(). Po prostu tłumi test na niedeterminizm.

Pamiętaj o tym, gdy używasz grupy według i porządkuj według tego, że MySQL jest TYLKO bazą danych, która pozwala na używanie kolumn w grupie według i / lub sortowanie według elementów, które nie są częścią instrukcji select.

Na przykład: wybierz kolumnę 1 z grupy tabel według kolumny 2 uporządkuj według kolumny 3

To nie będzie latać w innych bazach danych, takich jak Postgres, Oracle, MSSQL itp. W tych bazach danych musisz wykonać następujące czynności

wybierz kolumnę 1, kolumnę 2, kolumnę 3 z grupy tabel według kolumny 2 uporządkuj według kolumny 3

Tylko niektóre informacje na wypadek, gdybyś kiedykolwiek migrował swój obecny kod do innej bazy danych lub zaczął pracować w innej bazie danych i próbował ponownie użyć kodu.

Możesz użyć grupy by do wyświetlania różnych wartości, a także odpowiadających im pól.

select * from tabel_name group by FirstName

Teraz masz wyjście takie jak to:

ID    FirstName     LastName
2     Bugs          Bunny
1     John          Doe

Jeśli chcesz odpowiedzieć jak

ID    FirstName     LastName
1     John          Doe
2     Bugs          Bunny

następnie użyj tego zapytania,

select * from table_name group by FirstName order by ID

SELECT DISTINCT(firstName), ID, LastName from tableName GROUP BY firstName

Byłby najlepszy zakład IMO

SELECT DISTINCT (column1), column2
FROM table1
GROUP BY column1