sql - MySQL 查询 : Optimal Querying

Question

我有这个代码

SELECT DISTINCT idx_campus_bookinfo,c.userid as Buyer,bookname,book_explain,writedate
FROM campus_bookinfo cb 
LEFT JOIN user_books ub ON idx_campus_bookinfo = id_product
LEFT JOIN customer c ON ub.id_customer = c.id_customer
where cb.idx_campus = 1 and cb.idxuser = 29 ORDER BY writedate DESC

输出为

Click to View output

我的问题是如何让买家栏以逗号分隔，其中有同一本书。

Answer 1

使用 GROUP_CONCAT功能，但这意味着必须用 GROUP BY 替换 DISTINCT:

    SELECT idx_campus_bookinfo,
           GROUP_CONCAT(c.userid SEPARATOR ',') as Buyer,
           bookname,
           book_explain,
           writedate
      FROM campus_bookinfo cb 
LEFT JOIN user_books ub ON idx_campus_bookinfo = id_product
LEFT JOIN customer c ON ub.id_customer = c.id_customer
    WHERE cb.idx_campus = 1 
      AND cb.idxuser = 29 
 GROUP BY idx_campus_bookinfo, bookname, book_explain, writedate
 ORDER BY writedate DESC

a followup question, can i sort the userid?

是的，你可以:

    SELECT idx_campus_bookinfo,
           GROUP_CONCAT(c.userid ORDER BY c.userid ASC SEPARATOR ',') as Buyer,
           bookname,
           book_explain,
           writedate
      FROM campus_bookinfo cb 
LEFT JOIN user_books ub ON idx_campus_bookinfo = id_product
LEFT JOIN customer c ON ub.id_customer = c.id_customer
    WHERE cb.idx_campus = 1 
      AND cb.idxuser = 29 
 GROUP BY idx_campus_bookinfo, bookname, book_explain, writedate
 ORDER BY writedate DESC