“where 子句"中的未知列“差异" [英] Unknown column 'difference' in 'where clause

问题描述

这是我的代码:

select a.id_brg, a.nm_brg, a.jen_sat, a.nm_kat,a.stok,a.laku, (a.stok-a.laku) as difference from (SELECT barang.id_brg, barang.nm_brg, jen_sat, nm_kat, SUM( IFNULL(  stok_brg.stok, 0 ) ) AS stok, IFNULL( laku, 0 ) AS laku
        FROM barang
        JOIN satuan ON barang.id_sat = satuan.id_sat
        JOIN kategori ON barang.id_kat = kategori.id_kat
        LEFT JOIN stok_brg ON barang.id_brg = stok_brg.id_brg
        LEFT JOIN (
        SELECT barang.id_brg, SUM( IFNULL( brg_laku.dibeli, 0 ) ) AS laku
        FROM barang, brg_laku
        WHERE barang.id_brg = brg_laku.id_brg
        GROUP BY barang.id_brg ASC
        ) AS brg_laku ON barang.id_brg = brg_laku.id_brg
        GROUP BY barang.id_brg ASC) as a;

如何在代码中添加where?

where difference <= 3

每次我写where时，我都会收到错误信息:

Every time I write where, I get error message:

where 子句中的未知列差异"

Unknown column 'difference' in where clause

推荐答案

SELECT a.id_brg, 
       a.nm_brg, 
       a.jen_sat,
       a.nm_kat,
       a.stok,
       a.laku,
      (a.stok-a.laku) as difference 
FROM (
      SELECT barang.id_brg,
             barang.nm_brg,
             jen_sat, 
             nm_kat, 
             SUM(IFNULL(stok_brg.stok, 0)) AS stok,
             IFNULL(laku, 0) AS laku
      FROM barang
      JOIN satuan ON barang.id_sat = satuan.id_sat
      JOIN kategori ON barang.id_kat = kategori.id_kat
      LEFT JOIN stok_brg ON barang.id_brg = stok_brg.id_brg
      LEFT JOIN (
                 SELECT barang.id_brg,
                        SUM(IFNULL( brg_laku.dibeli, 0 )) AS laku
                 FROM barang 
                 INNER JOIN brg_laku
                 ON barang.id_brg = brg_laku.id_brg
                 GROUP BY barang.id_brg ASC
                ) AS brg_laku 
     ON barang.id_brg = brg_laku.id_brg
     GROUP BY barang.id_brg ASC
    ) as a
WHERE (a.stok-a.laku) <= 3;

现在，你能在 MySQL 的 WHERE 子句中使用别名吗?

来自 MySQL 手册:

From the MySQL Manual:

不允许在 WHERE 子句中引用列别名因为在 WHERE 时可能尚未确定列值条款被执行.请参阅第 B.1.5.4 节，列问题别名".

It is not allowable to refer to a column alias in a WHERE clause because the column value might not yet be determined when the WHERE clause is executed. See Section B.1.5.4, "Problems with Column Aliases".

这篇关于“where 子句"中的未知列“差异"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！