MySQL은 다른 열과 함께 하나의 열 DISTINCT를 선택합니다.

MySQL은 다른 열과 함께 하나의 열 DISTINCT를 선택합니다.

---

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

내가 선택하려는 DISTINCT로부터 결과 FirstName열을,하지만 난 상응하는 필요 ID와 LastName.

결과 집합 은 1 개만 표시하고 1 개와 Doe John는 표시해야합니다 .IDLastName

---

이 쿼리를 사용해보십시오

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

---

DISTINCT키워드는 정말 당신이 그것을 기대하고있는 방식으로 작동하지 않습니다. 사용 SELECT DISTINCT col1, col2, col3하면 실제로 모든 고유 {col1, col2, col3} 튜플을 선택하는 것입니다.

---

편집하다

최초의 답변은 MySQL 5.7.5 이전에 작성 되었으며, 기본 변경 사항으로 인해 더 이상 적용되지 않습니다 ONLY_FULL_GROUP_BY. 이 때, 또한 중요하다 ONLY_FULL_GROUP_BY비활성화의 사용 GROUP BYMySQL을 자유롭게 선택할 수로 집계 기능이없는이 예기치 않은 결과를 얻을 것입니다 어떤 그룹화 된 데이터 세트 내에서 값 [원문]를 .

이름과 성이 고유하게 색인화되었다고 가정하면,를 GROUP BY사용하여 정렬하여 LEFT JOIN결과 세트를 필터링 할 수 있습니다. 데모보기

내림차순으로 성으로 정렬 된 고유 한 이름을 검색하려면 (ZA)

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;

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

성으로 정렬 된 고유 한 이름을 오름차순으로 검색하려면 (AZ)

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;

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

그런 다음 원하는대로 결과 데이터를 주문할 수 있습니다.

이름과 성 조합이 고유하지 않고 동일한 값의 여러 행이있는 경우 조인에 OR 조건을 포함시켜 특정 ID를 선택하여 결과 집합을 필터링 할 수 있습니다. 데모를 참조하십시오 .

table_name 데이터 :

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

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;

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

---

경고

MySQL GROUP BY을 사용하면 ORDER BYSee : Test Case Example 과 함께 사용할 때 항상 예상되는 결과를 얻지는 않습니다 .

예상 결과를 보장하는 가장 좋은 구현 방법은 하위 쿼리를 사용하여 결과 집합 범위를 필터링하는 것입니다.

table_name 데이터 :

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

질문

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

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

대

SELECT * FROM table_name GROUP BY FirstName ORDER BY ID DESC

#Results
| 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

---

어때요?

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

---

MySQL로이 작업을 수행 할 수 있는지 확실하지 않지만 T-SQL에서 CTE를 사용할 수 있습니다

; 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

그렇지 않으면 임시 테이블을 사용해야 할 수도 있습니다.

---

Keep in mind when using the group by and order by that MySQL is the ONLY database that allows for columns to be used in the group by and/or order by piece that are not part of the select statement.

So for example: select column1 from table group by column2 order by column3

That will not fly in other databases like Postgres, Oracle, MSSQL, etc. You would have to do the following in those databases

select column1, column2, column3 from table group by column2 order by column3

Just some info in case you ever migrate your current code to another database or start working in another database and try to reuse code.

---

As pointed out by fyrye, the accepted answer pertains to older versions of MySQL in which ONLY_FULL_GROUP_BY had not yet been introduced. With MySQL 8.0.17 (used in this example), unless you disable ONLY_FULL_GROUP_BY you would get the following error message:

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

ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mydatabase.table_name.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

One way to work around this not mentioned by fyrye, but described in https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html, is to apply the ANY_VALUE() function to the columns which are not in the GROUP BY clause (id and lastName in this example):

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)

As written in the aforementioned docs,

In this case, MySQL ignores the nondeterminism of address values within each name group and accepts the query. This may be useful if you simply do not care which value of a nonaggregated column is chosen for each group. ANY_VALUE() is not an aggregate function, unlike functions such as SUM() or COUNT(). It simply acts to suppress the test for nondeterminism.

---

You can use group by for display distinct values and also corresponding fields.

select * from tabel_name group by FirstName

Now you got output like this:

ID    FirstName     LastName
2     Bugs          Bunny
1     John          Doe

If you want to answer like

ID    FirstName     LastName
1     John          Doe
2     Bugs          Bunny

then use this query,

select * from table_name group by FirstName order by ID

---

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

Would be the best bet IMO

---

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

참고URL : https://stackoverflow.com/questions/5967130/mysql-select-one-column-distinct-with-corresponding-other-columns