mysql - mysql 查询上的 LEFT JOIN 没有得到我想要的结果

Question

我有2个表，awards和awards_user，并且在mysql查询上有LEFT JOIN问题，没有得到我想要的结果

奖项:

awardid  | name   | link  
1        | award1 | photo link    
2        | award2 | photo link   
3        | award3 | photo link   
4        | award4 | photo link 

奖项用户:

awardid  | username
2        | mark      
2        | jon
3        | mark    
1        | jon

我尝试得到这样的结果:

award name  |  award photo  |  Users
award1      |      link     |  jon
award2      |      link     |  mark,jon
award3      |      link     |  mark
award4      |      link     |  -------

这是我的尝试:

 SELECT au.username 
      , au.userid 
      , a.link 
      , a.name 
      , a.awardid 
   FROM awards a
   LEFT 
   JOIN awards_user au
     ON au.awardid = a.awardid
  WHERE a.forumid = 22
  GROUP 
     BY a.awardid 
      , au.username
  ORDER 
     BY a.awardid DESC 

解决问题:

$all_awards = $db->query_read(" SELECT awards.name as name, awards.link as link, 
       GROUP_CONCAT(DISTINCT awards_user.username) AS username
   FROM awards LEFT JOIN awards_user ON (awards_user.awardid = awards.awardid)
   where awards.forumid = '".$_REQUEST['forumid']."'
   GROUP BY awards.awardid, awards.name, awards.link
  ");

Answer 1

GROUP_CONCAT 正是您所需要的:

SELECT
       a.name AS name
     , a.link AS photo
     , GROUP_CONCAT(DISTINCT au.username) AS users
FROM awards AS a
     LEFT JOIN awards_user AS au ON (au.awardid = a.awardid)
GROUP BY
       a.awardid
     , a.name
     , a.link
ORDER BY
     a.awardid DESC

有关 MySQL GROUP_CONCAT 的更多信息:http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

顺便说一句，如果您需要在 group_concat 中，您甚至可以对用户名进行排序或选择不同的分隔符。只需阅读 MySQL 手册和 google“mysql group_concat 示例”:)