mysql 从2个复杂查询中生成一个查询

qojgxg4l 于 2023-06-28 发布在 Mysql

关注(0)|答案(1)|浏览(119)

我对将2个查询变成1个查询感到困惑，因为这些查询对我来说太复杂了。这是我的第一个疑问。

SELECT
    COALESCE(s1.kode_barang, s2.kode_barang) AS kode_barang,
    COALESCE(s1.nama, s2.nama) AS nama,
    COALESCE(s1.sisa, 0) AS sisa_JUNI,
    COALESCE(s2.total_jumlah, 0) AS masuk_JULI,
    COALESCE(s1.sisa, 0) + COALESCE(s2.total_jumlah, 0) AS total
FROM
    (
        SELECT
            s2.max_tanggal,
            s2.kode_barang,
            s1.nama,
            s2.sisa
        FROM
            ws_fh.simas_barang s1
            LEFT JOIN (
                SELECT
                    sm1.kode_barang,
                    sm1.tanggal AS max_tanggal,
                    sm1.created_at,
                    sm1.sisa
                FROM
                    ws_fh.simas_mutasi sm1
                    JOIN (
                        SELECT
                            kode_barang,
                            MAX(tanggal) AS max_tanggal,
                            created_at
                        FROM
                            ws_fh.simas_mutasi
                        WHERE
                            tanggal BETWEEN '2022-06-01' AND '2022-06-30'
                        GROUP BY
                            kode_barang
                    ) sm2 ON sm2.kode_barang = sm1.kode_barang
                    AND sm2.max_tanggal = sm1.tanggal
                GROUP BY
                    sm1.kode_barang,
                    sm1.created_at,
                    sm1.sisa
                ORDER BY
                    sm1.kode_barang,
                    sm1.created_at DESC
            ) s2 ON s2.kode_barang = s1.id
        WHERE
            s1.jenis = 'ATK DAN SEJENISNYA'
        GROUP BY
            s2.kode_barang
    ) s1
LEFT JOIN (
    SELECT
        sm.tanggal,
        sm.kode_barang,
        sb.nama,
        SUM(sm.jumlah) AS total_jumlah
    FROM
        ws_fh.simas_mutasi sm
        JOIN ws_fh.simas_barang sb ON sb.id = sm.kode_barang
    WHERE
        sb.jenis = 'ATK DAN SEJENISNYA'
        AND sm.kegiatan != 'mengeluarkan stok'
        AND sm.kegiatan != 'mengubah barang'
        AND sm.tanggal BETWEEN '2022-07-01' AND '2022-07-31'
    GROUP BY
        sb.nama
) s2 ON s1.nama = s2.nama

UNION

SELECT
    COALESCE(s1.kode_barang, s2.kode_barang) AS kode_barang,
    COALESCE(s1.nama, s2.nama) AS nama,
    COALESCE(s1.sisa, 0) AS sisa,
    COALESCE(s2.total_jumlah, 0) AS total_jumlah,
    COALESCE(s1.sisa, 0) + COALESCE(s2.total_jumlah, 0) AS total
FROM
    (
        SELECT
            s2.max_tanggal,
            s2.kode_barang,
            s1.nama,
            s2.sisa
        FROM
            ws_fh.simas_barang s1
            RIGHT JOIN (
                SELECT
                    sm1.kode_barang,
                    sm1.tanggal AS max_tanggal,
                    sm1.created_at,
                    sm1.sisa
                FROM
                    ws_fh.simas_mutasi sm1
                    JOIN (
                        SELECT
                            kode_barang,
                            MAX(tanggal) AS max_tanggal,
                            created_at
                        FROM
                            ws_fh.simas_mutasi
                        WHERE
                            tanggal BETWEEN '2022-06-01' AND '2022-06-30'
                        GROUP BY
                            kode_barang
                    ) sm2 ON sm2.kode_barang = sm1.kode_barang
                    AND sm2.max_tanggal = sm1.tanggal
                GROUP BY
                    sm1.kode_barang,
                    sm1.created_at,
                    sm1.sisa
                ORDER BY
                    sm1.kode_barang,
                    sm1.created_at DESC
            ) s2 ON s2.kode_barang = s1.id
        WHERE
            s1.jenis = 'ATK DAN SEJENISNYA'
        GROUP BY
            s2.kode_barang
    ) s1
RIGHT JOIN (
    SELECT
        sm.tanggal,
        sm.kode_barang,
        sb.nama,
        SUM(sm.jumlah) AS total_jumlah,
        sm.kegiatan
    FROM
        ws_fh.simas_mutasi sm
        JOIN ws_fh.simas_barang sb ON sb.id = sm.kode_barang
    WHERE
        sb.jenis = 'ATK DAN SEJENISNYA'
        AND sm.kegiatan != 'mengeluarkan stok'
        AND sm.kegiatan != 'mengubah barang'
        AND sm.tanggal BETWEEN '2022-07-01' AND '2022-07-31'
    GROUP BY
        sb.nama
) s2 ON s1.nama = s2.nama
WHERE
    s1.nama IS NULL
ORDER BY
    nama;

这是我的第二个疑问。

SELECT COALESCE(sm.kode_barang, s1.id) AS kode_barang, 
COALESCE(sb.nama, s1.nama) AS nama, 
SUM(sm.jumlah) AS keluar_JULI, 
s2.sisa AS sisa_JULI
FROM ws_fh.simas_mutasi sm
LEFT JOIN ws_fh.simas_barang sb ON sb.id = sm.kode_barang
LEFT JOIN (
    SELECT sm1.kode_barang, sm1.tanggal AS max_tanggal, sm1.created_at, sm1.sisa
    FROM ws_fh.simas_mutasi sm1
    JOIN (
        SELECT kode_barang, MAX(tanggal) AS max_tanggal, created_at
        FROM ws_fh.simas_mutasi
        WHERE tanggal BETWEEN '2022-07-01' AND '2022-07-31'
        GROUP BY kode_barang
    ) sm2 ON sm2.kode_barang = sm1.kode_barang AND sm2.max_tanggal = sm1.tanggal
    GROUP BY sm1.kode_barang, sm1.created_at, sm1.sisa
    ORDER BY sm1.kode_barang, sm1.created_at DESC
) s2 ON s2.kode_barang = sb.id
RIGHT JOIN ws_fh.simas_barang s1 ON s1.id = s2.kode_barang
WHERE COALESCE(sb.jenis, s1.jenis) = 'ATK DAN SEJENISNYA'
GROUP BY COALESCE(sb.nama, s1.nama)
HAVING keluar_JULI IS NOT NULL AND sisa_JULI IS NOT NULL
ORDER BY COALESCE(sb.nama, s1.nama);

我想把所有这些都变成一个查询。对“nama”使用group by，这样第一个查询和第二个查询中的相同“nama”将位于同一行中。如果第一个查询中的“nama”与第二个查询中的“nama”不匹配，则将其显示在表中。

mysql

来源：https://stackoverflow.com/questions/76554759/make-one-query-from-2-complex-queries

1条答案

按热度按时间

jv4diomz1#

为此，您需要一个完整的外部连接，MySQL不支持，MySQL 8至少在某种程度上更具可读性

WITH CTE1 AS (SELECT
    COALESCE(s1.kode_barang, s2.kode_barang) AS kode_barang,
    COALESCE(s1.nama, s2.nama) AS nama,
    COALESCE(s1.sisa, 0) AS sisa_JUNI,
    COALESCE(s2.total_jumlah, 0) AS masuk_JULI,
    COALESCE(s1.sisa, 0) + COALESCE(s2.total_jumlah, 0) AS total
FROM
    (
        SELECT
            s2.max_tanggal,
            s2.kode_barang,
            s1.nama,
            s2.sisa
        FROM
            ws_fh.simas_barang s1
            LEFT JOIN (
                SELECT
                    sm1.kode_barang,
                    sm1.tanggal AS max_tanggal,
                    sm1.created_at,
                    sm1.sisa
                FROM
                    ws_fh.simas_mutasi sm1
                    JOIN (
                        SELECT
                            kode_barang,
                            MAX(tanggal) AS max_tanggal,
                            created_at
                        FROM
                            ws_fh.simas_mutasi
                        WHERE
                            tanggal BETWEEN '2022-06-01' AND '2022-06-30'
                        GROUP BY
                            kode_barang
                    ) sm2 ON sm2.kode_barang = sm1.kode_barang
                    AND sm2.max_tanggal = sm1.tanggal
                GROUP BY
                    sm1.kode_barang,
                    sm1.created_at,
                    sm1.sisa
                ORDER BY
                    sm1.kode_barang,
                    sm1.created_at DESC
            ) s2 ON s2.kode_barang = s1.id
        WHERE
            s1.jenis = 'ATK DAN SEJENISNYA'
        GROUP BY
            s2.kode_barang
    ) s1
LEFT JOIN (
    SELECT
        sm.tanggal,
        sm.kode_barang,
        sb.nama,
        SUM(sm.jumlah) AS total_jumlah
    FROM
        ws_fh.simas_mutasi sm
        JOIN ws_fh.simas_barang sb ON sb.id = sm.kode_barang
    WHERE
        sb.jenis = 'ATK DAN SEJENISNYA'
        AND sm.kegiatan != 'mengeluarkan stok'
        AND sm.kegiatan != 'mengubah barang'
        AND sm.tanggal BETWEEN '2022-07-01' AND '2022-07-31'
    GROUP BY
        sb.nama
) s2 ON s1.nama = s2.nama

UNION

SELECT
    COALESCE(s1.kode_barang, s2.kode_barang) AS kode_barang,
    COALESCE(s1.nama, s2.nama) AS nama,
    COALESCE(s1.sisa, 0) AS sisa,
    COALESCE(s2.total_jumlah, 0) AS total_jumlah,
    COALESCE(s1.sisa, 0) + COALESCE(s2.total_jumlah, 0) AS total
FROM
    (
        SELECT
            s2.max_tanggal,
            s2.kode_barang,
            s1.nama,
            s2.sisa
        FROM
            ws_fh.simas_barang s1
            RIGHT JOIN (
                SELECT
                    sm1.kode_barang,
                    sm1.tanggal AS max_tanggal,
                    sm1.created_at,
                    sm1.sisa
                FROM
                    ws_fh.simas_mutasi sm1
                    JOIN (
                        SELECT
                            kode_barang,
                            MAX(tanggal) AS max_tanggal,
                            created_at
                        FROM
                            ws_fh.simas_mutasi
                        WHERE
                            tanggal BETWEEN '2022-06-01' AND '2022-06-30'
                        GROUP BY
                            kode_barang
                    ) sm2 ON sm2.kode_barang = sm1.kode_barang
                    AND sm2.max_tanggal = sm1.tanggal
                GROUP BY
                    sm1.kode_barang,
                    sm1.created_at,
                    sm1.sisa
                ORDER BY
                    sm1.kode_barang,
                    sm1.created_at DESC
            ) s2 ON s2.kode_barang = s1.id
        WHERE
            s1.jenis = 'ATK DAN SEJENISNYA'
        GROUP BY
            s2.kode_barang
    ) s1
RIGHT JOIN (
    SELECT
        sm.tanggal,
        sm.kode_barang,
        sb.nama,
        SUM(sm.jumlah) AS total_jumlah,
        sm.kegiatan
    FROM
        ws_fh.simas_mutasi sm
        JOIN ws_fh.simas_barang sb ON sb.id = sm.kode_barang
    WHERE
        sb.jenis = 'ATK DAN SEJENISNYA'
        AND sm.kegiatan != 'mengeluarkan stok'
        AND sm.kegiatan != 'mengubah barang'
        AND sm.tanggal BETWEEN '2022-07-01' AND '2022-07-31'
    GROUP BY
        sb.nama
) s2 ON s1.nama = s2.nama
WHERE
    s1.nama IS NULL
ORDER BY
    nama),
    CTE2 AS(
    SELECT COALESCE(sm.kode_barang, s1.id) AS kode_barang, 
COALESCE(sb.nama, s1.nama) AS nama, 
SUM(sm.jumlah) AS keluar_JULI, 
s2.sisa AS sisa_JULI
FROM ws_fh.simas_mutasi sm
LEFT JOIN ws_fh.simas_barang sb ON sb.id = sm.kode_barang
LEFT JOIN (
    SELECT sm1.kode_barang, sm1.tanggal AS max_tanggal, sm1.created_at, sm1.sisa
    FROM ws_fh.simas_mutasi sm1
    JOIN (
        SELECT kode_barang, MAX(tanggal) AS max_tanggal, created_at
        FROM ws_fh.simas_mutasi
        WHERE tanggal BETWEEN '2022-07-01' AND '2022-07-31'
        GROUP BY kode_barang
    ) sm2 ON sm2.kode_barang = sm1.kode_barang AND sm2.max_tanggal = sm1.tanggal
    GROUP BY sm1.kode_barang, sm1.created_at, sm1.sisa
    ORDER BY sm1.kode_barang, sm1.created_at DESC
) s2 ON s2.kode_barang = sb.id
RIGHT JOIN ws_fh.simas_barang s1 ON s1.id = s2.kode_barang
WHERE COALESCE(sb.jenis, s1.jenis) = 'ATK DAN SEJENISNYA'
GROUP BY COALESCE(sb.nama, s1.nama)
HAVING keluar_JULI IS NOT NULL AND sisa_JULI IS NOT NULL
ORDER BY COALESCE(sb.nama, s1.nama))
    SELECT COALESCE(CTE1.kode_barang,CTE1.kode_barang) kode_barang,COALESCE(CTE1.nama,CTE1.nama) nama,
    sisa_JUNI,masuk_JULI, keluar_JULI,sisa_JULI,total
    FROM CTE1 LEFT JOIN CTE2 ON CTE1.kode_barang = CTE2.kode_barang
    UNION
SELECT COALESCE(CTE1.kode_barang,CTE1.kode_barang),COALESCE(CTE1.nama,CTE1.nama),
    sisa_JUNI,masuk_JULI, keluar_JULI,sisa_JULI,total
    FROM CTE2 LEFT JOIN CTE1 ON CTE1.kode_barang = CTE2.kode_barang
ORDER BY 2

赞(0）回复(0）举报 2023-06-28

我来回答

mysql 从2个复杂查询中生成一个查询

1条答案

相关问题

热门标签

最新问答