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.
我已经找到了一些如何做到这一点的示例,但它们都分别在每个列上选择了distinct。
6 个解决方案
#1
12
Assuming that the first column is unique, you can do this:
假设第一列是唯一的,你可以这样做:
SELECT id, col2, col3, col4
FROM yourtable
WHERE id IN
(
SELECT MIN(id)
FROM yourtable
GROUP BY col2, col3
)
See it working online: sqlfiddle
可以看到它在线工作:sqlfiddle
#2
25
Update 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
#3
1
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
#4
0
This query makes sure that the combination of column1 and column2 is unique, while selecting the minimum value of column three
这个查询确保column1和column2的组合是唯一的,同时选择列3的最小值
SELECT col1, col2, MIN(col3)
FROM yourTable
GROUP BY col1, col2
#5
0
THe simplest query for this is
最简单的查询是
SELECT col1, col2, MIN(col3)
FROM myTable
GROUP BY col1, col2
#6
0
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相同的no记录(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
)
#1
12
Assuming that the first column is unique, you can do this:
假设第一列是唯一的,你可以这样做:
SELECT id, col2, col3, col4
FROM yourtable
WHERE id IN
(
SELECT MIN(id)
FROM yourtable
GROUP BY col2, col3
)
See it working online: sqlfiddle
可以看到它在线工作:sqlfiddle
#2
25
Update 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
#3
1
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
#4
0
This query makes sure that the combination of column1 and column2 is unique, while selecting the minimum value of column three
这个查询确保column1和column2的组合是唯一的,同时选择列3的最小值
SELECT col1, col2, MIN(col3)
FROM yourTable
GROUP BY col1, col2
#5
0
THe simplest query for this is
最简单的查询是
SELECT col1, col2, MIN(col3)
FROM myTable
GROUP BY col1, col2
#6
0
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相同的no记录(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
)