SQLiteSpy环境下跨表取数联立输出，并且如何高效

新开一贴，之前的交代的不够明确。

SQL学的效果一直不是很理想，对于联立、合并等基本操作还是一头雾水。
上面的图片给定的是，最左侧是主数据库，其中过滤出来的ID，作为在后续表中查找的关键，后续更多的表中的ID是不全的，对后续的表格逐个进行查找、联立，如果在规定的条件下，无数据的话，就补零，有数据的话，就补数据，形成最终的输出效果。
目前是四张表的查找和联立，假如表格超过10张以上，如何做到代码简洁、运行高效？
谢谢！

这种表之间结合的数据，只能关联去查询，如果有多张表的话，就只能铜鼓哦多次关联获取结果了


select t1.ID, t1.FL as '分类', t1.`总计`, t2.`领导`, t3.`税务`, t3.`杂项`, t4.`外卖+堂食`, t4.`其他` 
from(
    -- 关联子查询X1
    select FL, ID, IFNULL(sum(DATA1), 0) as '总计' 
    from X1 where KJ = '总计' and FL in ('合肥', '芜湖') group by FL,ID
)t1
left join(
    -- 关联子查询X2
    select ID, IFNULL(sum(DATA1), 0) as '领导' 
    from X2 where KJ = '领导' group by ID
)t2 on t1.ID = t2.ID
left join(
    -- 关联子查询X3
    select tt3.ID, IFNULL(tt3.`税务`, 0) as '税务', IFNULL(tt3.`杂项`, 0) as '杂项' from(
        select ID, case KJ when '税务' then sum(DATA1) end '税务', case KJ when '杂项' then sum(DATA1) end '杂项' 
        from X3 where KJ in ('税务', '杂项') group by ID, KJ
    )tt3
)t3 on t1.ID = t3.ID
left join(
    -- 关联子查询X4
    select tt4.ID, IFNULL(tt4.`外卖+堂食`, 0) as '外卖+堂食', IFNULL(tt4.`其他`, 0) as '其他' from(
        select ID, case KJ when '堂食' or '外卖' then sum(DATA1) end '外卖+堂食', case KJ when '其他' then sum(DATA1) end '外卖'
        from X4 where KJ in ('堂食', '外卖', '其他') group by ID, KJ
    )tt4
)t4 on t1.ID = t4.ID

联立查询语句

SELECT
    X1.FL AS '分类',
    X1.ID,
    X1.DATA1 AS '总计',
    IFNULL(X2.DATA1, 0) AS '领导',
    IFNULL(X3_1.DATA1, 0) AS '税务',
    IFNULL(X3_2.DATA1, 0) AS '杂项',
    SUM(IFNULL(X4_1.DATA1, 0)) AS '外卖+堂食',
    IFNULL(X4_2.DATA1, 0) AS '杂项'
FROM X1
LEFT JOIN X2 ON (X2.ID = X1.ID AND X2.KJ = '领导')
LEFT JOIN X3 AS X3_1 ON (X3_1.ID = X1.ID AND X3_1.KJ = '税务')
LEFT JOIN X3 AS X3_2 ON (X3_2.ID = X1.ID AND X3_2.KJ = '杂项')
LEFT JOIN X4 AS X4_1 ON (X4_1.ID = X1.ID AND (X4_1.KJ = '外卖' OR X4_1.KJ = '堂食'))
LEFT JOIN X4 AS X4_2 ON (X4_2.ID = X1.ID AND X4_2.KJ = '杂项')
WHERE (X1.FL = '合肥' OR X1.FL = '芜湖') AND X1.KJ = '总计'
GROUP BY X1.ID

在SQLite上测试数据

CREATE TABLE X1 (FL, ID, KJ, DATA1);
CREATE TABLE X2 (FL, ID, KJ, DATA1);
CREATE TABLE X3 (FL, ID, KJ, DATA1);
CREATE TABLE X4 (FL, ID, KJ, DATA1);

CREATE INDEX IDX_ID ON X1(ID);

INSERT INTO X1 VALUES
('合肥', 1001, '总计', 12),
('合肥', 1001, '分项A', 2),
('合肥', 1002, '总计', 343),
('芜湖', 1004, '总计', 1456),
('芜湖', 1004, '分项B', 23),
('芜湖', 1006, '总计', 567),
('阜阳', 1003, '总计', 112),
('宿州', 1005, '总计', 22);

INSERT INTO X2 VALUES
('芜湖', 1004, '领导', 1),
('芜湖', 1007, '员工', 4),
('芜湖', 1006, '领导', 2),
('阜阳', 1003, '员工', 4),
('宿州', 1005, '员工', 6),
('芜湖', 1004, '员工', 3),
('芜湖', 1006, '员工', 6),
('合肥', 1009, '领导', 90);

INSERT INTO X3 VALUES
('芜湖', 1004, '税务', 1),
('芜湖', 1004, '杂项', 4),
('芜湖', 1006, '杂项', 2),
('合肥', 1001, '人工', 1),
('合肥', 1001, '其他', 44),
('阜阳', 1010, '税务', NULL),
('合肥', 1001, '税务', 33);

INSERT INTO x4 VALUES
('芜湖', 1004, '外卖', 1),
('芜湖', 1004, '堂食', 4),
('芜湖', 1004, '杂项', 2),
('合肥', 1001, '杂项', 1),
('合肥', 1001, '其他', 44),
('阜阳', 1010, '税务', NULL),
('合肥', 1090, '外卖', 33);

SELECT t.FL AS '分类', t.ID,
   SUM(case when t.KJ='总计' then ISNULL(t.DATA1, 0) else 0 end) AS '总计',
    SUM(case when t.KJ='领导' then ISNULL(t.DATA1, 0) else 0 end) AS '领导',
    SUM(case when t.KJ='税务' then ISNULL(t.DATA1, 0) else 0 end) AS '税务',
    SUM(case when t.KJ='杂项3' then ISNULL(t.DATA1, 0) else 0 end) AS '杂项',
    SUM(case when t.KJ='外卖' then ISNULL(t.DATA1, 0) when t.KJ='堂食' then ISNULL(t.DATA1, 0) else 0 end) AS '外卖+堂食',
    SUM(case when t.KJ='杂项' then ISNULL(t.DATA1, 0) else 0 end) AS '杂项'
FROM (
select * from X1 where KJ='总计'
UNION all 
select * from X2 where KJ = '领导' 
UNION all 
select FL,ID,(case when KJ='杂项' then '杂项3' else KJ end) AS KJ,DATA1 from X3 where KJ = '税务' or KJ = '杂项'  
UNION all 
select * from X4 where KJ = '外卖' OR KJ = '堂食' or KJ = '杂项') t  where t.FL = '合肥' OR t.FL = '芜湖' GROUP BY  t.FL,t.ID

结果如下：

使用一些优化方案，优化查询语句

您可以参考一下这篇文章：

https://www.5axxw.com/questions/content/5k7cx7