MySQL SQL 查询中没有重复项
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1543435/
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
No duplicates in SQL query
提问by Martin
I'm doing a select in MySQL with an inner join:
我正在使用内部连接在 MySQL 中进行选择：
SELECT DISTINCT tblcaritem.caritemid, tblcar.icarid
FROM tblcaritem
INNER JOIN tblprivatecar ON tblcaritem.partid = tblprivatecar.partid
INNER JOIN tblcar ON tblcaritem.carid = tblcar.carid
WHERE tblcaritem.userid=72;
Sometimes I get duplicates of tblcaritem.caritemid in the result. I want to make sure to never get duplicates of tblcaritem.caritemid, but how can I do that? I tried to use DISTINCT but it just checked if the whole row is a duplicate, I want to check for only tblcaritem.caritemid, is there a way?
有时我会在结果中得到 tblcaritem.caritemid 的重复项。我想确保永远不会得到 tblcaritem.caritemid 的重复项，但我该怎么做？我尝试使用 DISTINCT 但它只是检查整行是否重复，我只想检查 tblcaritem.caritemid，有没有办法？
Sorry if I didn't explain it very well, I'm not the best of SQL queries.
对不起，如果我没有很好地解释它，我不是最好的 SQL 查询。
回答by chaos
GROUP BY tblcaritem.caritemid
GROUP BY tblcaritem.caritemid
回答by Juliet
The problem here is just as you describe: you're checking for the uniqueness of the whole row, your dataset ends up looking like this:
这里的问题正如您所描述的：您正在检查整行的唯一性，您的数据集最终看起来像这样：
CarItemId CarId
--------- -----
1 1
1 2
1 3
2 1
2 2
3 3
You want unique CarItemIds with no duplicates, meaning that you also want unique CarIds ---- alright, you have 3 CarIds, which one should SQL Server pick?
您需要没有重复的唯一 CarItemId，这意味着您还需要唯一的 CarId ---- 好吧，您有 3 个 CarId，SQL Server 应该选择哪一个？
You don't have much choice here except to aggregate those extra CarIds away:
除了将这些额外的 CarId 聚合起来之外，您在这里没有太多选择：
SELECT tblcaritem.caritemid, max(tblcar.icarid)
FROM tblcaritem
INNER JOIN tblprivatecar ON tblcaritem.partid = tblprivatecar.partid
INNER JOIN tblcar ON tblcaritem.carid = tblcar.carid
WHERE tblcaritem.userid=72
GROUP BY tblcaritem.caritemid
回答by jle
You could do an aggregate function on the other row... something like max or min
你可以在另一行做一个聚合函数......像max或min这样的东西
SELECT tblcaritem.caritemid, max(tblcar.icarid)
FROM tblcaritem
INNER JOIN tblprivatecar ON tblcaritem.partid = tblprivatecar.partid
INNER JOIN tblcar ON tblcaritem.carid = tblcar.carid
WHERE tblcaritem.userid=72
group by tblcaritem.caritemid;
回答by Bork Blatt
If you put 2 fields in a SELECT DISTINCT query, it will return rows where the combinationof the 2 fields is unique, not just where each field is unique.
如果在 SELECT DISTINCT 查询中放置 2 个字段，它将返回2 个字段组合唯一的行，而不仅仅是每个字段唯一的行。
You are going to have to run 2 separate queries if you want only unique results for each column.
如果您只需要每列的唯一结果，您将不得不运行 2 个单独的查询。
