MySQL 查询两个结果集合并,并排显示,相同则求和显示,不相同则显示为0

第一个sql

SELECT
	DATE_FORMAT(表年月日字段, '%Y') AS year,
	COUNT(主键ID) AS rkzzl 
FROM
	表名 
GROUP BY year;

第二个sql
		 
SELECT
	DATE_FORMAT(表年月日字段, '%Y') AS year,
	COUNT(主键ID) AS rkswl 
FROM
	表名  
GROUP BY year;

结果:

sql1

sql2

 

两个结果合并,年相同则求和, 不相同则0补位

select d.year, sum(d.rkzzl) as rkzzl, sum(d.rkswl) as rkswl  
FROM
(
	select  DATE_FORMAT( t.表年月日字段, '%Y' ) AS year,COUNT( t.主键ID ) AS rkzzl,0 as rkswl 
	from 表名 t
	group by year
	 
	union

	select DATE_FORMAT( t2.表年月日字段, '%Y' ) AS year,0 as rkzzl, COUNT( t2.主键ID) AS rkswl
	from 表名 t2
	group by year
) d 
GROUP BY d.year 
ORDER BY d.year

结果显示