MySQL多条件查询：按状态区分日期条件关联表数据

需求说明 #

需要查询两类记录：

• 当status_riwayat = 'keluar'时，必须满足pj_riwayat.tanggal为2022年11月
• 当status_riwayat = 'masuk'时，无日期限制

涉及两张表：

供应商详情表 pj_detailsupplier #

CREATE TABLE pj_detailsupplier 
(
    id_detailsupplier int,
    id_barang int,
    id_supplier int,
    no_batch varchar(255),
    stok int,
    tanggal date,
    tgl_expired date,
    status_detailsupplier int 
);

INSERT INTO `pj_detailsupplier`(`id_detailsupplier`, `id_barang`, `id_supplier`, `no_batch`, `stok`, `tanggal`, `tgl_expired`, `status_detailsupplier`) 
VALUES ('13208','205','25','1','12','2022-01-01','2022-01-01','0')

历史记录表 pj_riwayat #

CREATE TABLE pj_riwayat 
(
    id_riwayat int,
    id_detailsupplier int,
    jumlah int,
    keluar int,
    sisa int,
    tanggal date,
    status_riwayat varchar(255) 
);

INSERT INTO `pj_riwayat`(`id_riwayat`, `id_detailsupplier`, `jumlah`, `keluar`, `sisa`, `tanggal`, `status_riwayat`) 
VALUES ('','13208','12','0','12','2022-09-02','masuk'), 
       ('','13208','0','2','10','2022-11-14','keluar')

原查询问题分析 #

你写的SQL只返回1条数据，原因是：

1. LEFT JOIN中加入了日期条件，导致status_riwayat = 'masuk'的记录（日期为2022-09-02）无法关联到pj_detailsupplier表
2. WHERE子句的pj_detailsupplier.id_barang = 205过滤掉了所有关联失败（即pj_detailsupplier字段为NULL）的记录，最终只保留了符合日期条件的keluar记录

修正后的查询语句 #

方案1：WHERE子句中按状态分支判断（性能更优） #

SELECT * 
FROM pj_riwayat
INNER JOIN pj_detailsupplier 
    ON pj_riwayat.id_detailsupplier = pj_detailsupplier.id_detailsupplier
WHERE pj_detailsupplier.id_barang = 205
AND (
    -- 状态为masuk时，无日期限制
    pj_riwayat.status_riwayat = 'masuk'
    OR
    -- 状态为keluar时，限制日期为2022年11月
    (
        pj_riwayat.status_riwayat = 'keluar'
        AND pj_riwayat.tanggal BETWEEN '2022-11-01' AND '2022-11-30'
    )
)

方案2：使用YEAR()和MONTH()函数（适合不明确日期范围的场景） #

SELECT * 
FROM pj_riwayat
INNER JOIN pj_detailsupplier 
    ON pj_riwayat.id_detailsupplier = pj_detailsupplier.id_detailsupplier
WHERE pj_detailsupplier.id_barang = 205
AND (
    pj_riwayat.status_riwayat = 'masuk'
    OR
    (
        pj_riwayat.status_riwayat = 'keluar'
        AND YEAR(pj_riwayat.tanggal) = 2022
        AND MONTH(pj_riwayat.tanggal) = 11
    )
)

说明 #

• 改用INNER JOIN即可，因为所有pj_riwayat记录都有对应的pj_detailsupplier数据
• 把日期条件移到WHERE子句中，通过逻辑分支针对不同状态做判断，确保两种符合条件的记录都能被查询到
• 方案1的日期范围写法性能更优，若tanggal字段有索引可以直接利用

内容的提问来源于stack exchange，提问作者Sukmo Abadii