只做下截取和统计,没有格式化SQL。暂时使用子查询,如果有更好的查询语句,请指正。
//截取年份
select SUBSTR(IDCARD,7,4) as year from temp_ktbao_cert order by year desc
//统计同年份的count 数
select year,count(year) from (select SUBSTR(IDCARD,7,4) as year from temp_ktbao_cert order by year desc )a group by a.year order by year desc