mysql - 比较两年内的日期

Question

我有一个人口表，我想比较两年内的人口。

我的表结构:
id(自动递增)，类型(男人，女人， child )，人口(1到10000)，日期

我想在查询下运行两个并显示到一个表结果中:

查询1:

SELECT type,count(population) as count_of_year1
FROM population
where date between '2013-01-01' and '2013-01-24'
GROUP BY type

查询2:

SELECT type, count(population) as count_of_year2
FROM population
where date between '2014-01-01' and '2014-01-24'
GROUP BY type

我需要这个结果:

|类型 | 2013年人口| 2014年人口

如何做到这一点？

Answer 1

使用case表达式进行条件计数:

SELECT type,
       count(case when date between '2013-01-01' and '2013-01-24' then population end) as count_of_year1,
       count(case when date between '2014-01-01' and '2014-01-24' then population end) as count_of_year2
FROM population
GROUP BY type

添加此 where 子句以加快速度(如果需要):

where date between '2013-01-01' and '2013-01-24'
   or date between '2014-01-01' and '2014-01-24'