先上两副图

这里有两张表:score表和year_as表,要求统计出score表按年份分组的个数,且查询出来的内容需要包括year_as表中的year_as字段.

使用正常连表并分组统计count得出的SQL和对应结果如下:

SELECT
    b.`year`,
    b.year_as,
    COUNT(a.`id`) AS num
FROM
    `score` AS a
LEFT JOIN `year_as` AS b
ON
    a.`year` = b.`year`
GROUP BY
    a.`year`
ORDER BY
    num
DESC

这数据明显是不对的,因为score表中的2008只有3条,怎么查询出来是15条呢? 

 于是可以推导:count函数是在连表后才统计的(score表中2008的数据 * year_as表中2008的数据 = 15),统计完后再分组

解决方案是:先分组统计出score表中的数据,然后再连表分组查询得出正确结果

SELECT
    a.year,
		a.year_as,
    b.num
FROM
    `year_as` AS a
LEFT JOIN(
    SELECT
        year,
        COUNT(`id`) AS num
    FROM
        `score`
    GROUP BY
        `year`
) AS b
ON
    a.year = b.year
GROUP BY
    b.`year`
ORDER BY
    num
DESC