mysql - 请让我知道 mysql 查询 : alias in where clause

Question

这个问题已经有答案了:

How to use alias in where clause in mysql (4 个回答)

已关闭 8 年前。

1. SELECT 
2.  (SELECT SUM(count) FROM _ERP_재고 as jego WHERE jego.goodsno=link.goodsno) as tt, 
3.  (SELECT catnm FROM gd_category WHERE category=LEFT(link.category, 3)) as cate1, 
4.  (SELECT catnm FROM gd_category WHERE category=LEFT(link.category, 6)) as cate2 
5. FROM gd_goods as goods, gd_goods_link as link, _ERP_재고 as jego 
6. WHERE link.goodsno=goods.goodsno 
7. AND jego.goodsno=link.goodsno 
8. AND (SELECT SUM(count) FROM _ERP_재고 as jego WHERE jego.goodsno=link.goodsno) > 0 
9. AND link.category LIKE '009%' GROUP BY goods.goodsno

我做了一个不恰当的查询，因为第二行与第 8 行相同。
如果我将第 8 行中的子选择替换为 tt (第 2 行的别名)，发生错误。
请让我知道如何摆脱再次调用子查询。

Answer 1

不能在 WHERE 子句中使用别名。相反，您必须使用HAVING。

1. SELECT 
2.  (SELECT SUM(count) FROM _ERP_재고 as jego WHERE jego.goodsno=link.goodsno) as tt, 
3.  (SELECT catnm FROM gd_category WHERE category=LEFT(link.category, 3)) as cate1, 
4.  (SELECT catnm FROM gd_category WHERE category=LEFT(link.category, 6)) as cate2 
5. FROM gd_goods as goods, gd_goods_link as link, _ERP_재고 as jego 
6. WHERE link.goodsno=goods.goodsno 
7. AND jego.goodsno=link.goodsno 
8. AND link.category LIKE '009%' GROUP BY goods.goodsno
9. HAVING tt > 0