MySQL 在mysql中选择不同的2列组合
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11277251/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Selecting distinct 2 columns combination in mysql
提问by James Harzs
I have a mysql table that looks like this:
我有一个 mysql 表，看起来像这样：
1 value1 value2 3534
2 value1 value1 8456
3 value1 value2 3566
4 value1 value3 7345
5 value2 value3 6734
I need a query to select all the rows with distinct column 2 and 3, for example the output I want for this example will look like this:
我需要一个查询来选择具有不同第 2 列和第 3 列的所有行，例如我想要的这个示例的输出将如下所示：
1 value1 value2 3534
2 value1 value1 8456
4 value1 value3 7345
5 value2 value3 6734
i've found a few samples on how to do it but they all select distinct on each column individually.
我找到了一些关于如何做到这一点的示例，但它们都分别在每一列上选择了不同的。
采纳答案by Mark Byers
回答by Fahim Parkar
Update 1
更新 1
Better you use this against above.
最好在上面使用它。
SELECT id, col2, col3, col4
FROM yourtable
GROUP BY col2, col3;
Demo
演示
The reason I am saying is because using CONCAT, I am not getting desired result in this case. First query is returning me 5 rows however CONCAT is returning me 4 rows which is INCORRECT.
我说的原因是因为使用 CONCAT，在这种情况下我没有得到想要的结果。第一个查询返回 5 行，但 CONCAT 返回 4 行，这是错误的。
Hope you got my point.
希望你明白我的意思。
Assumed the columns in the table are (id, col2, col3, col4).
假设表中的列是 (id, col2, col3, col4)。
SELECT DISTINCT(CONCAT(col2, col3)) as "dummy column", id, col2, col3, col4
FROM yourtable
GROUP BY CONCAT(col2, col3);
OR
或者
SELECT id, col2, col3, MIN(col4)
FROM yourtable
GROUP BY col2, col3;
live working example
现场工作示例
回答by Andomar
Assuming the columns in the table are (id, col1, col2, col3), you could:
假设表中的列是(id, col1, col2, col3)，您可以：
SELECT *
FROM YourTable yt
JOIN (
SELECT MIN(id) as minid
FROM YourTable
GROUP BY
col1, col2
) filter
ON filter.minid = yt.id
回答by schadr
This query makes sure that the combination of column1 and column2 is unique, while selecting the minimum value of column three
此查询确保 column1 和 column2 的组合是唯一的，同时选择第三列的最小值
SELECT col1, col2, MIN(col3)
FROM yourTable
GROUP BY col1, col2
回答by Sashi Kant
THe simplest query for this is
对此最简单的查询是
SELECT col1, col2, MIN(col3)
FROM myTable
GROUP BY col1, col2
回答by Derya
Using the group by method is returning me extra rows, where as explicitly checking each field although longer returns the same no of records as count(Distinct ..)
使用 group by 方法返回我额外的行，其中显式检查每个字段虽然更长返回与 count(Distinct ..) 相同的记录数
SELECT id, col2, col3, col4
FROM yourtable yt
WHERE id =
(
SELECT MIN(id)
FROM yourtable yt1
WHERE yt.col2 = yt1.col2
AND yt.col3 = yt1.col3
)
