php - 我如何以提供相同输出的方式重写它

Question

我有一个像这样的 mysql 查询:

SELECT 
    bp.id, 
    COUNT(*) AS total
FROM
    blog_posts bp
JOIN
    tagged tg ON
        tg.taggable_id = bp.id
        AND tg.taggable_type = 'App\Storage\BlogPost'
JOIN
    tags t ON
        t.id = tg.tag_id
WHERE
    bp.user_id = 1
GROUP BY
    t.id
ORDER BY
    total DESC,
    t.count DESC
LIMIT
    3

我得到一个错误:

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'example.bp.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

我如何以提供相同输出的方式重写它？

我在 MySQL 5.7.17 和 Homestead Laravel 中工作

Answer 1

每当您执行 GROUP BY 时，您添加到 SELECT 和 ORDER BY 子句中的所有内容都需要在 中GROUP BY 子句，除非您使用聚合函数，例如 MIN()、MAX()、SUM()、AVG() 等。

在这种情况下，您的 order by 子句中有 t.count DESC，像这样编写查询将为您提供所需的结果。

SELECT
    id,
    total
FROM
    (
        SELECT 
            bp.id, 
            COUNT(*) AS total,
            MAX(t.count) count
        FROM
            blog_posts bp
        JOIN
            tagged tg ON
                tg.taggable_id = bp.id
                AND tg.taggable_type = 'App\Storage\BlogPost'
        JOIN
            tags t ON
                t.id = tg.tag_id
        WHERE
            bp.user_id = 1
        GROUP BY
            bp.id,
            t.id
        LIMIT
            3
    ) a
ORDER BY
    total DESC,
    `count` DESC

如果你不关心最终结果中是否包含计数，那么你可以这样做

SELECT 
    bp.id, 
    COUNT(*) AS total,
    MAX(t.count) count
FROM
    blog_posts bp
JOIN
    tagged tg ON
        tg.taggable_id = bp.id
        AND tg.taggable_type = 'App\Storage\BlogPost'
JOIN
    tags t ON
        t.id = tg.tag_id
WHERE
    bp.user_id = 1
GROUP BY
    bp.id,
    t.id
ORDER BY
    total DESC,
    t.count DESC
LIMIT
    3