从"where"条件自动生成 MYSQL 列

Question

我有以下查询来在选择查询中生成列，我手动键入，但我希望通过迭代循环生成列，该循环获取开始日期和结束日期，然后输出开始日期和终止日期之间的相应年份

SELECT 
SUM(IF(DATE_FORMAT(a.`Date_First_Seen`,"%Y")="2011",1,0))"2011"
,SUM(IF(DATE_FORMAT(a.`Date_First_Seen`,"%Y")="2012",1,0))"2012"
,SUM(IF(DATE_FORMAT(a.`Date_First_Seen`,"%Y")="2013",1,0))"2013"
,SUM(IF(DATE_FORMAT(a.`Date_First_Seen`,"%Y")="2014",1,0))"2014"
,SUM(IF(DATE_FORMAT(a.`Date_First_Seen`,"%Y")="2015",1,0))"2015"
,SUM(IF(DATE_FORMAT(a.`Date_First_Seen`,"%Y")="2016",1,0))"2016"
,SUM(IF(DATE_FORMAT(a.`Date_First_Seen`,"%Y")="2017",1,0))"2017"
,SUM(IF(DATE_FORMAT(a.`Date_First_Seen`,"%Y")="2018",1,0))"2018"
FROM  year_table a 
WHERE DATE_FORMAT(Date_First_Seen,"%Y") BETWEEN "2011" AND "2018"

Answer 1

此查询可以显著减少并消除冗余。使用year可以轻松获得约会年份。然后按年份分组，并计算每组中的日期。

select
year(date_first_seen) as year_first_seen,
count(date_first_seen)
from year_table
where year_first_seen between 2011 and 2018
group by year_first_seen

如果要将其作为单列，请使用数据透视表。以下是关于如何在MySQL中实现这一点的答案。