MySQL 合并查询，以map或对象的形式返回

转载 CSDN博主「小林子林子」 -> https://blog.csdn.net/qq_26106607/article/details/84961254

原始SQL-> 目的-> 合并查询

-- 初检数量

SELECT COUNT(1)

FROM installation_order o

WHERE

o.`init_check_person` = 'wulin' AND o.`init_dispatch_time` >= '2018-01-01 01:00:00' AND o.`init_check_time` <= '2018-12-11 19:06:30' AND o.`init_dispatch_time` IS NOT NULL AND o.`init_check_time` IS NOT NULL;

-- 初装数量

SELECT COUNT(1)

FROM installation_order o

WHERE

o.`init_costume_person` = 'wulin' AND o.`init_costume_dispatch_time` >= '2018-01-01 01:00:00' AND o.`init_costume_time` <= '2018-12-11 23:06:30' AND o.`init_costume_dispatch_time` IS NOT NULL AND o.`init_costume_time` IS NOT NULL;

-- 节能改造单

SELECT COUNT(1)

FROM installation_order o

WHERE

o.`remould_person` = 'wulin' AND o.`transform_dispatch_order_time` >= '2018-01-01 01:00:00' AND o.`remould_time` <= '2018-12-11 23:06:30' AND o.`transform_dispatch_order_time` IS NOT NULL AND o.`remould_time` IS NOT NULL;

初次转换：

SELECT COUNT(1) init_check_count

FROM installation_order o

WHERE

o.`init_check_person` = 'wulin' AND o.`init_dispatch_time` >= '2018-01-01 01:00:00' AND o.`init_check_time` <= '2018-12-11 19:06:30' AND o.`init_dispatch_time` IS NOT NULL AND o.`init_check_time` IS NOT NULL

UNION ALL

SELECT COUNT(1) init_costume_count

FROM installation_order o

WHERE o.`init_costume_person` = 'wulin' AND o.`init_costume_dispatch_time` >= '2018-01-01 01:00:00' AND o.`init_costume_time` <= '2018-12-11 23:06:30' AND o.`init_costume_dispatch_time` IS NOT NULL AND o.`init_costume_time` IS NOT NULL

UNION ALL

SELECT COUNT(1) sn_remould_count

FROM installation_order o

WHERE o.`remould_person` = 'wulin' AND o.`transform_dispatch_order_time` >= '2018-01-01 01:00:00' AND o.`remould_time` <= '2018-12-11 23:06:30' AND o.`transform_dispatch_order_time` IS NOT NULL AND o.`remould_time` IS NOT NULL

结果： ---> 不符合预期 : 以MAP或者对象的形式返回 ----> 再次进行转换

SELECT COUNT(1) a, 0 b, 0 c

FROM installation_order o

WHERE o.`init_check_person` = 'wulin' AND o.`init_dispatch_time` >= '2018-01-01 01:00:00' AND o.`init_check_time` <= '2018-12-11 19:06:30' AND o.`init_dispatch_time` IS NOT NULL AND o.`init_check_time` IS NOT NULL

UNION ALL

SELECT 0 a, COUNT(1) b, 0 c

FROM installation_order o

WHERE o.`init_costume_person` = 'wulin' AND o.`init_costume_dispatch_time` >= '2018-01-01 01:00:00' AND o.`init_costume_time` <= '2018-12-11 23:06:30' AND o.`init_costume_dispatch_time` IS NOT NULL AND o.`init_costume_time` IS NOT NULL

UNION ALL

SELECT 0 a, 0 b, COUNT(1) c

FROM installation_order o

WHERE o.`remould_person` = 'wulin' AND o.`transform_dispatch_order_time` >= '2018-01-01 01:00:00' AND o.`remould_time` <= '2018-12-11 23:06:30' AND o.`transform_dispatch_order_time` IS NOT NULL AND o.`remould_time` IS NOT NULL

----->与预期有点差距，最后进行整合：

SELECT SUM(a) init_check_count, SUM(b) init_costume_count, SUM(c) sn_remould_count

FROM (

    SELECT COUNT(1) a, 0 b, 0 c

    FROM installation_order o

    WHERE o.`init_check_person` = 'wulin' AND o.`init_dispatch_time` >= '2018-01-01 01:00:00' AND o.`init_check_time` <= '2018-12-11 19:06:30' AND o.`init_dispatch_time` IS NOT NULL AND o.`init_check_time` IS NOT NULL

    UNION ALL

    SELECT 0 a, COUNT(1) b, 0 c

    FROM installation_order o

    WHERE o.`init_costume_person` = 'wulin' AND o.`init_costume_dispatch_time` >= '2018-01-01 01:00:00' AND o.`init_costume_time` <= '2018-12-11 23:06:30' AND o.`init_costume_dispatch_time` IS NOT NULL AND o.`init_costume_time` IS NOT NULL

    UNION ALL

    SELECT 0 a, 0 b, COUNT(1) c

    FROM installation_order o

    WHERE o.`remould_person` = 'wulin' AND o.`transform_dispatch_order_time` >= '2018-01-01 01:00:00' AND o.`remould_time` <= '2018-12-11 23:06:30' AND o.`transform_dispatch_order_time` IS NOT NULL AND o.`remould_time` IS NOT NULL

	) AS aaaa

最终效果：

找了许久，感谢大佬~~