与 MySQL 的 INNER JOIN DISTINCT
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3854714/
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
INNER JOIN DISTINCT with MySQL
提问by Martin
I have a mysql problem. I have two tables like this that I need to join together.
我有一个 mysql 问题。我有两个这样的表需要连接在一起。
table:
桌子：
id otherid2
1 | 1
2 | 1
3 | 2
4 | 2
idotherid2
1 | 1
2 | 1
3 | 2
4 | 2
table2:
表2：
otherid otherid2
1 | 1
2 | 1
3 | 2
4 | 2
otherid otherid2
1 | 1
2 | 1
3 | 2
4 | 2
I'm using:
我正在使用：
SELECT id,otherid FROM table INNER JOIN table2 ON table.otherid2=table2.otherid2
This gives me:
这给了我：
id otherid
1 | 1
1 | 2
2 | 1
2 | 2
3 | 3
3 | 4
4 | 3
4 | 4
id 其他
1 | 1
1 | 2
2 | 1
2 | 2
3 | 3
3 | 4
4 | 3
4 | 4
As you can see I get duplicates of id as there is otherid2s that is not unique in table2. What I need is to INNER JOIN DISTINCT in some way, I only want the result to be as below. Not duplicates.
正如您所看到的，我得到了重复的 id，因为 table2 中存在非唯一的 otherid2。我需要的是以某种方式 INNER JOIN DISTINCT，我只希望结果如下。不重复。
This is what I want:
这就是我要的：
id otherid
1 | 1
2 | 1
3 | 3
4 | 3
id 其他
1 | 1
2 | 1
3 | 3
4 | 3
Can I do this in an easy way?
我可以用简单的方式做到这一点吗？
回答by Jagmag
If you want the row with the lowest id in table2, this should probably do it
如果您想要 table2 中具有最低 id 的行，这可能应该这样做
SELECT id, min(otherid)
FROM table
INNER JOIN table2
ON table.otherid2=table2.otherid2
GROUP BY id
回答by myme
In your comment you wanted the lowest, then I'd suggest a group by and a min aggregator
在你的评论中你想要最低的，然后我建议一个 group by 和一个 min aggregator
SELECT id, MIN(otherid) AS otherid ... GROUP BY id
