MySql COUNT(),SUM()组合用法

• 需求：统计工作室表的数量，按创建时间分组
• 问题：按创建时间分组，如何将计数后的结果求和
• 解决：用COUNT(DISTINCT col_name)讲每行计数为1，再求和分组

SELECT
    DATE_FORMAT(create_time, '%Y%m%d') createTime,
    SUM(
        (
            SELECT
                FORMAT(
                    COUNT(DISTINCT up.id) / (
                        SELECT
                            COUNT(*)
                        FROM
                            user_panorama
                    ),
                    0
                )
            FROM
                user_panorama up,
                ct_user u
            WHERE
                up.user_id = u.id
        )
    ) worksCount
FROM
    user_panorama
GROUP BY
    createTime

 

• 结果