php - 组连接结果不准确

Question

我正在使用 GROUP_CONCAT() 来显示多个类别，但我没有得到确切的结果。对于表连接，我使用 IN() 子句，请帮助我。

The query only displays a single category. in database category id store like 2,5,8

$sql = "SELECT u.*, GROUP_CONCAT(c.name SEPARATOR ',') as cname, s.name as sname
        FROM course u        
        INNER JOIN category c ON c.id IN (u.category_id) 
        INNER JOIN status s ON s.id = u.status_id
        WHERE u.is_deleted = 0 AND u.id = $id $app
        LIMIT 1";

我想要类别表中的多个类别。

Answer 1

group_concat 是一个聚合函数，当您还需要未聚合的列时，为了正确使用这些函数，需要 GROUP BY 子句和用于聚合级别的列名称

    SELECT  GROUP_CONCAT(c.name SEPARATOR ',') as cname, s.name as sname
    FROM course u        
    INNER JOIN category c ON c.id IN (u.category_id) 
    INNER JOIN status s ON s.id = u.status_id
    WHERE u.is_deleted = 0 AND u.id = $id $app
    GROUP BY s.name
    ORDER BY s.name 
    LIMIT 1;