mysql - 将两个结果查询合并为一个

Question

我有这样的查询(我在两个查询中都有带有 join 和 union 的复杂查询)

SELECT 
            id, 
            COUNT(id) ct_id, 
            SUM(kl) sum_kl, 
            SUM(CASE WHEN tgl_kondisi="2017-09-13" THEN 1 ELSE 0 END) as tot, 
            SUM(CASE WHEN tgl_kondisi <= "2017-09-14" THEN kl ELSE 0 end ) as sum_kl2 
        FROM (
            SELECT 
                id_kondisi as id, 
                tgl_kondisi,
                nilai_potensi_kerugian AS kl 
            FROM laporan_kondisi 
            UNION  
            SELECT 
                id_sub_kondisi, 
                tgl_kondisi, 
                nilai_potensi_kerugian 
            FROM laporan_kondisi 
        ) merged_table  GROUP BY id ORDER BY id;

结果

+------+-------+----------+------+----------+
| id   | ct_id | sum_kl   | tot  | sum_kl2  |
+------+-------+----------+------+----------+
| 01   |     3 | 20000000 |    3 | 20000000 |
| 0101 |     2 |  9000000 |    2 |  9000000 |
| 0102 |     2 | 11000000 |    2 | 11000000 |
| 02   |     1 |        0 |    0 |        0 |
| 0201 |     1 |        0 |    0 |        0 |
| 0202 |     1 |        0 |    0 |        0 |
| 0203 |     1 |        0 |    0 |        0 |
| 03   |     2 |  4000000 |    2 |  4000000 |
| 0301 |     1 |        0 |    1 |        0 |
| 0302 |     2 |  4000000 |    2 |  4000000 |
| 0303 |     1 |        0 |    1 |        0 |
+------+-------+----------+------+----------+

还有另一个查询

SELECT  
        id,
        SUM(CASE WHEN tgl_tindak_lanjut="2017-09-14" THEN 1 ELSE 0 end ) as count_all_09,
        SUM( CASE WHEN tgl_tindak_lanjut="2017-09-13" THEN 1 ELSE 0 END) as count_09_13 
    FROM ( 
        SELECT 
            a.id_kondisi as id, 
            d.tgl_tindak_lanjut as tgl_tindak_lanjut 
        FROM
            laporan_kondisi a 
        LEFT OUTER JOIN  
            laporan_sebab b 
        ON 
            a.id = b.id_laporan_kondisi 
        LEFT OUTER JOIN  
            laporan_rekomendasi c 
        ON 
            b.id = c.id_laporan_sebab 
        LEFT OUTER JOIN 
            laporan_tindak_lanjut d 
        ON 
            c.id = d.id_laporan_rekomendasi  
        UNION  
        SELECT 
            a2.id_sub_kondisi,
            d2.tgl_tindak_lanjut as tgl_tindak_lanjut 
        FROM
            laporan_kondisi a2 
        LEFT OUTER JOIN 
            laporan_sebab b2 
        ON  
            a2.id = b2.id_laporan_kondisi 
        LEFT OUTER JOIN 
            laporan_rekomendasi c2 
        ON 
            b2.id = c2.id_laporan_sebab 
        LEFT OUTER join  
            laporan_tindak_lanjut d2 
        ON 
            c2.id = d2.id_laporan_rekomendasi  
    ) merged_table GROUP BY id

结果

+------+--------------+-------------+
| id   | count_all_09 | count_09_13 |
+------+--------------+-------------+
| 01   |            0 |           1 |
| 0101 |            0 |           1 |
| 0102 |            0 |           0 |
| 02   |            0 |           0 |
| 0201 |            0 |           0 |
| 0202 |            0 |           0 |
| 0203 |            0 |           0 |
| 03   |            0 |           0 |
| 0301 |            0 |           0 |
| 0302 |            0 |           0 |
| 0303 |            0 |           0 |
+------+--------------+-------------+

可以像这样组合表格吗？

+------+-------+----------+------+----------+--------------+-------------+
| id   | ct_id | sum_kl   | tot  | sum_kl2  | count_all_09 | count_09_13 |
+------+-------+----------+------+----------+--------------+-------------+
| 01   |     3 | 20000000 |    3 | 20000000 |     0        |       1     |
| 0101 |     2 |  9000000 |    2 |  9000000 |     0        |       1     |
| 0102 |     2 | 11000000 |    2 | 11000000 |     0        |       0     |
| 02   |     1 |        0 |    0 |        0 |     0        |       0     |
| 0201 |     1 |        0 |    0 |        0 |     0        |       0     |
| 0202 |     1 |        0 |    0 |        0 |     0        |       0     |
| 0203 |     1 |        0 |    0 |        0 |     0        |       0     |
| 03   |     2 |  4000000 |    2 |  4000000 |     0        |       0     |
| 0301 |     1 |        0 |    1 |        0 |     0        |       0     |
| 0302 |     2 |  4000000 |    2 |  4000000 |     0        |       0     |
| 0303 |     1 |        0 |    1 |        0 |     0        |       0     |
+------+-------+----------+------+----------+--------------+-------------+

我尝试过JOIN和许多其他方法，但它只给出一个结果。这样的结果可以吗？或者这个查询只是没有给我们任何结果？

Answer 1

很难说添加所有第二个查询逻辑是否会影响结果，因为您没有提供有关此查询的作用或实际数据的任何信息。您可以做的，应该始终有效(如果您计划重用此查询，则不建议这样做，并且如果性能是一个问题，也不建议这样做)，只需加入整个查询即可。

它看起来像这样:

SELECT t.*,s.count_all_09,s.count_09_13
FROM (FIRST QUERY HERE) t
JOIN (SECOND QUERY HERE) s
 ON(t.id = s.id)