我有这个查询，它应该做的：
Select
WB.BinNumber, WB.ID,
(select count(*)
from WMS_LocationBalance
where BinID = WB.ID) AS BinCount
from
WMS_WarehouseBin WB
ORDER BY
BinCount
但是添加WHERE BinCount = 0查询时不会运行，因为这BinCount是无效的列名。
为什么可以，order by BinCount但我不能在其中使用WHERE？
另外我该如何解决呢？
Select
WB.BinNumber,
WB.ID,
(select count(*)
from Location
where BinID = WB.ID) AS BinCount
from
WarehouseBin WB
WHERE
BinCount = 0
ORDER BY
BinCount
试试这个。您不能使用Alias namein，Where clause因为它WHERE clause是在之前处理的SELECT clause(*)，因此别名不可derived table用，请在where子句中使用a然后使用别名
SELECT *
FROM (SELECT WB.BinNumber,
WB.ID,
(SELECT Count(*)
FROM Location
WHERE BinID = WB.ID) AS BinCount
FROM WarehouseBin WB) A
WHERE A.BinCount = 0
ORDER BY a.BinCount
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句