在我的应用程序中,有发布者和类别.一个发布者可以属于几个类别.当我进行mysql事务时,它将为所属的每个类别返回相同的发布者记录.这是查询：

SELECT
    grdirect_publisher.name,
    grdirect_publisher.short_description,
    grdirect_publisher.thumb_image,
    grdirect_publisher.url,
    grdirect_category.name AS catname
FROM
    grdirect_publisher
JOIN
    grdirect_publisher_categories
    ON
    grdirect_publisher.id = grdirect_publisher_categories.publisher_id
JOIN
    grdirect_category
    ON
    grdirect_publisher_categories.category_id = grdirect_category.id

返回：

name    short_description   thumb_image url catname
------------------------------------------------------------
Foo Lorem Ipsum...      images/pic.png  d.com   Video Games
Foo Lorem Ipsum...      images/pic.png  d.com   Music
Bar Blah Blah...        images/tic.png  e.com   Music

本质上,Foo应该只在结果中显示一次.

解决方法:

您可以使用DISTINCT,但是如果结果集中的任何列具有不同的值,则它将强制复制该行.如果要将每个名称的列表减少到一行,则必须使用DISTINCT,并且必须省略catname列：

SELECT DISTINCT
    grdirect_publisher.name,
    grdirect_publisher.short_description,
    grdirect_publisher.thumb_image,
    grdirect_publisher.url
FROM
. . .

代替DISTINCT的另一种解决方案是MySQL的聚合函数GROUP_CONCAT(),它允许您在组中采用多个值并生成以逗号分隔的列表：

SELECT
    grdirect_publisher.name,
    grdirect_publisher.short_description,
    grdirect_publisher.thumb_image,
    grdirect_publisher.url,
    GROUP_CONCAT(grdirect_category.name) AS catname
. . .
GROUP BY grdirect_publisher.id;

因此,您必须确定希望结果集看起来如何以获得正确的解决方案.