MySql：通过增加额外字段，将不相关的结果集关联起来

需求

需要用一条SQL查询出北京四个区域的订单数和客户人数。数据输出格式为如下：

区域	订单数	客户人数
东区	230	355
西区	168	200
南区	270	373
北区	262	311

限定条件：订单数和客户数无法直接获取，需要从不同的表中组合得出。四个区域的数据也需要单独统计得出。
思路：首先，分别统计四个区域里每个区域的订单数、四个区域里每个区域的客户数。

 select count(*) from order where ...;
 select count(*) from order where ...;
 select count(*) from order where ...;
 select count(*) from order where ...;

 select count(*) from cust where ...;
 select count(*) from cust where ...;
 select count(*) from cust where ...;
 select count(*) from cust where ...;

然后，在每个区域里增加一个额外字段和对应值。

 select count(*), 1 as cl from order where ...;
 select count(*), 2 as cl from order where ...;
 select count(*), 3 as cl from order where ...;
 select count(*), 4 as cl from order where ...;

 select count(*), 1 as cl from cust where ...;
 select count(*), 2 as cl from cust where ...;
 select count(*), 3 as cl from cust where ...;
 select count(*), 4 as cl from cust where ...;

然后，将四个区域的订单数使用UNION ALL联合在一起，看做一个整体，同理将四个区域的客户数使用UNION ALL联合在一起，也看做一个整体。

 select count(*), 1 as cl from order where ...
 UNION ALL
 select count(*), 2 as cl from order where ...
 UNION ALL
 select count(*), 3 as cl from order where ...
 UNION ALL
 select count(*), 4 as cl from order where ...;

 select count(*), 1 as cl from cust where ...
 UNION ALL
 select count(*), 2 as cl from cust where ...
 UNION ALL
 select count(*), 3 as cl from cust where ...
 UNION ALL
 select count(*), 4 as cl from cust where ...;

然后，将两个整体看做两张表，通过join链接起来。

 select * from (
     select count(*), 1 as cl from order where ...
     UNION ALL
     select count(*), 2 as cl from order where ...
     UNION ALL
     select count(*), 3 as cl from order where ...
     UNION ALL
     select count(*), 4 as cl from order where ...
 ) bt1 left join (
     select count(*), 1 as cl from cust where ...
     UNION ALL
     select count(*), 2 as cl from cust where ...
     UNION ALL
     select count(*), 3 as cl from cust where ...
     UNION ALL
     select count(*), 4 as cl from cust where ...
 ) bt2 on bt1.cl = bt2.cl

最后加工下表头，输出即可。

 select 
     sit as '区域', ordC as '订单数', cusC as '客户数'
 from (
     select case bt1.cl
                when 1 then '西区'
                when 2 then '东区'
                when 3 then '南区'
                when 4 then '北区'
            end,
            bt1.c,
            bt2.c
    from (
         select count(*) as c, 1 as cl from order where ...
         UNION ALL
         select count(*) as c, 2 as cl from order where ...
         UNION ALL
         select count(*) as c, 3 as cl from order where ...
         UNION ALL
         select count(*) as c, 4 as cl from order where ...
     ) bt1 left join (
         select count(*) as c, 1 as cl from cust where ...
         UNION ALL
         select count(*) as c, 2 as cl from cust where ...
         UNION ALL
         select count(*) as c, 3 as cl from cust where ...
         UNION ALL
         select count(*) as c, 4 as cl from cust where ...
     ) bt2 on bt1.cl = bt2.cl
 )

通过这种增加额外字段的方式，将原本不容易关联起来的数据联系起来，达到了我们的目的。通过这种思路，在遇到这类型问题时，也能多一种解决方案。