需求:
按照项目ID,日期 将以下两张表数据合并
表1 表2
目标表:
解决思路:
- 将不同的表转化为统一结构的表, 不存在的字段默认设置为NULL; # NULL AS total_cases
- 去重合并两张表格 UNION;
- 将字段值为null的设置为0 , 按指定字段分组并求和; # IFNULL(num, 0)
SQL语句:
SELECT SUM(t4.interface_num) AS interface_num, SUM(t4.total_cases) AS total_cases, t4.date, t4.project_id FROM(
SELECT IFNULL(t3.interface_num,0) AS interface_num, IFNULL(t3.total_cases,0) total_cases, date, t3.project_id FROM
(SELECT t1.interface_num, NULL AS total_cases, t1.interface_date AS date, t1.project_id FROM tb1_name t1
UNION
SELECT NULL AS interface_num, t2.total_cases, t2.case_date AS date, t2.project_id FROM tb2_name t2) t3
) t4
GROUP BY t2.date, t2.project_id
ORDER BY date;