mysql - GROUP_CONCAT 表中的多个值

Question

我刚刚添加了一个表，其中包含每个项目的 url 和描述。下面的查询工作正常......但它只返回 url，我还需要 desc。我不确定如何进行这项工作。

$query_str = "SELECT      a.userid, a.cat, a.id, a.name, a.image, 
                          a.desc, a.country, b.user_id, c.username, 
                          c.fbook, d.cat_id,
                          (
                               SELECT GROUP_CONCAT(url) 
                               FROM one_add o 
                               WHERE a.id=o.one_id

                          ) AS url,
                          (
                               SELECT COUNT(id) 
                               FROM one_msg m 
                               WHERE m.guest_id = ? AND 
                                     m.one_id=a.id 
                          ) AS count
               FROM place a
                            LEFT OUTER JOIN wait b 
                                 ON a.id=b.post_id AND 
                                    b.user_id= ?
                            JOIN users c
                                 ON a.userid=c.id
                            LEFT JOIN ( 
                                       SELECT userid, user_id, 
                                              GROUP_CONCAT( cat_id ) AS cat_id 
                                       FROM user_cat 
                                       WHERE userid='$user_id' 
                                       GROUP BY user_id 
                                      ) AS d ON d.userid='$user_id' AND 
                                                d.user_id=a.userid
              WHERE a.cat != ? ORDER BY a.date desc";

这就是我想要完成的:<a href="url">desc</a>

Answer 1

您可以通过在 GROUP_CONCAT 函数中使用 CONCAT 来实现这一点:

(SELECT GROUP_CONCAT(CONCAT('<a href=\"',url,'\">',desc,'</a>')) 
 FROM one_add o 
 WHERE a.id=o.one_id) AS url

完整查询:

(SELECT COUNT(id) FROM one_msg m WHERE m.guest_id = ? AND m.one_id=a.id ) AS count
FROM place a
LEFT OUTER JOIN wait b ON a.id=b.post_id AND b.user_id= ?
JOIN users c ON a.userid=c.id
LEFT JOIN ( SELECT userid, user_id, GROUP_CONCAT( cat_id ) AS cat_id FROM user_cat WHERE userid='$user_id' GROUP BY user_id ) AS d ON d.userid='$user_id' AND d.user_id=a.userid
WHERE a.cat != ? ORDER BY a.date desc";