mysql - #1054 - 'b.ID_TRANSAKSIJUAL' 中的未知列 'field list'

Question

这是我在 MySQL 中的查询。不知道为什么该字段是unknown

SELECT `a`.`NAMA_BULAN` AS `Bulan`,
    CASE WHEN `b`.`ID_TRANSAKSIJUAL` IS NULL
    THEN 0
    ELSE `b`.`ID_TRANSAKSIJUAL`
    END AS `Total_Pendapatan`
    FROM `tbl_bulan` AS `a`
        LEFT JOIN (
        SELECT MONTH(`tbl_transaksi_jual`.`TGL_TRANSAKSI`) AS `TGL_TRANSAKSI` , COUNT(`tbl_transaksi_jual`.`ID_TRANSAKSIJUAL`) AS `TOTAL_STLH_DISC`
        FROM `tbl_transaksi_jual`
        WHERE `tbl_transaksi_jual`.`ID_CABANG`=1 AND 
            YEAR(`tbl_transaksi_jual`.`TGL_TRANSAKSI`)= 2016
        GROUP BY MONTH(`tbl_transaksi_jual`.`TGL_TRANSAKSI`) 
        ) AS `b` ON `a`.ID_BULAN = `b`.`TGL_TRANSAKSI`
    ORDER BY `a`.ID_BULAN ASC

Answer 1

子查询 b 不会返回名为 ID_TRANSAKSIJUAL 的列。它返回 TOTAL_STLH_DISC 中该列的计数，因此您应该在主查询中测试它。

您还可以使用 IFNULL 或 COALESCE 代替 CASE 表达式。

SELECT `a`.`NAMA_BULAN` AS `Bulan`,
       IFNULL(b.TOTAL_STLH_DISC, 0) AS Total_Pendapatan
FROM `tbl_bulan` AS `a`
LEFT JOIN (
    SELECT MONTH(`tbl_transaksi_jual`.`TGL_TRANSAKSI`) AS `TGL_TRANSAKSI`,
           COUNT(`tbl_transaksi_jual`.`ID_TRANSAKSIJUAL`) AS `TOTAL_STLH_DISC`
    FROM `tbl_transaksi_jual`
    WHERE `tbl_transaksi_jual`.`ID_CABANG`=1 AND 
        YEAR(`tbl_transaksi_jual`.`TGL_TRANSAKSI`)= 2016
    GROUP BY MONTH(`tbl_transaksi_jual`.`TGL_TRANSAKSI`) 
    ) AS `b` ON `a`.ID_BULAN = `b`.`TGL_TRANSAKSI`
ORDER BY `a`.ID_BULAN ASC