先上两副图
这里有两张表: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