不要在 MySQL 的表上重复 (DISTINCT) 一列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3975803/
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
Do Not Repeat (DISTINCT) One Column On A Table With MySQL
提问by mrtakdnz
When I use this:
当我使用这个时：
SELECT DISTINCT id FROM table
this works! but... when I want to filter only one column I try to do this:
这有效！但是......当我只想过滤一列时，我尝试这样做：
SELECT DISTINCT prod_id, id, prod_picture FROM products
this gives me all table... I just need 1 picture for each product, like:
这给了我所有的表格......每个产品我只需要一张图片，例如：
1 | 1 | asd.jpg
2 | 3 | weq.jph
1 | 1 | asd.jpg
2 | 3 | 文件
not
不是
1 | 1 | asd.jpg
1 | 2 | qwe.jpg
2 | 3 | weq.jpg
1 | 1 | asd.jpg
1 | 2 | qwe.jpg
2 | 3 | 微信.jpg
actually I try to use this:
实际上我尝试使用这个：
SELECT DISTINCT
prod_list.id,
prod_list.prodname,
prod_pict.pict_file
FROM
prod_list
INNER JOIN urun_pict ON (prod_list.id = prod_pict_prod_id)
I have to filter just "prod_list.id"...
我只需要过滤“prod_list.id”...
回答by Dan Grossman
You should GROUP BYthe product id to collapse all rows for each id into one. All columns which are not part of your GROUP BYclause should be aggregate columns. You need to tell MySQL whichof the possibly multiple values for the other columns you want. If you don't care, use MINor MAX?
您应该GROUP BY使用产品 id 将每个 id 的所有行折叠为一个。所有不属于您的GROUP BY子句的列都应该是聚合列。你需要告诉MySQL它为你想要的其他列的可能是多个值。如果您不在乎，请使用MIN或MAX?
SELECT
prod_list.id,
prod_list.prodname,
MAX(prod_pict.pict_file) AS `pict_file`,
FROM
prod_list
INNER JOIN
prod_pict
ON
prod_list.id = prod_pict.prod_id
GROUP BY
prod_list.id,
prod_list.prodname
回答by Ignacio Vazquez-Abrams
SELECT prod_id, id, prod_picture
FROM products
GROUP BY prod_id
回答by Haim Evgi
use group by prod_id,
使用group by prod_id，
SELECT prod_id, id, prod_picture FROM products group by prod_id
work only if in run not with this sql_mode: ONLY_FULL_GROUP_BY, The default value is empty (no modes set).
仅在运行时不使用此sql_mode 才工作：ONLY_FULL_GROUP_BY，默认值为空（未设置模式）。
回答by MatTheCat
SELECT
prod_list.id,
prod_list.prodname,
prod_pict.pict_file
FROM
prod_list
INNER JOIN urun_pict ON (prod_list.id = prod_pict_prod_id)
GROUP BY prod_list.id
This should work.
这应该有效。
