如何在 UNION ALL 中设置 UNION？

Iseca 发表于 Dev

伊塞卡

我正在尝试执行此代码：

SELECT ItName, ItCode, Qty, Qt2, Qt3
FROM
(
SELECT A.ItName, A.ItCode, COUNT(B.Qty) AS 'Quantity 1', COUNT(B.Qty2) AS 'Quantity 2', 
COUNT(B.Qty3) AS 'Quantity 3' 
FROM tblitem A
INNER JOIN tblstocksummary B ON A.ItCode = B.ItCode
WHERE A.Itcode='IL001-000151'
GROUP BY A.ItCode
HAVING COUNT(B.Qty) !='0'AND COUNT(B.Qty2) !='0' AND COUNT(B.Qty3) !='0'

UNION

SELECT A2.ItName, A2.ItCode, COUNT(B2.Qty), COUNT(B2.Qty2), COUNT(B2.Qty3) FROM tblitem A2
INNER JOIN tblstocksummary B2 ON A2.ItCode = B2.ItCode
WHERE A2.Itcode='IL001-000373' 
GROUP BY A2.ItCode
HAVING COUNT(B2.Qty) !='0'AND COUNT(B2.Qty2) !='0' AND COUNT(B2.Qty3) !='0'
) t
UNION ALL

SELECT A3.ItName, A3.ItCode, COUNT(B3.Qty), COUNT(B3.Qty2), COUNT(B3.Qty3) FROM tblitem A3
INNER JOIN tblstocksummary B3 ON A2.ItCode = B3.ItCode
WHERE A3.Itcode='IL001-000166' 
GROUP BY A3.ItCode
HAVING COUNT(B3.Qty) !='0'AND COUNT(B3.Qty2) !='0' AND COUNT(B3.Qty3) !='0';

但我有错误：

SQL 错误 (1054)：字段列表中的未知列“数量”

所以，我想要么我使用了错误的语法，要么我正在尝试做一些无法完成的事情。也许最有可能有经验的人会马上看出哪里出了问题。

有人能帮助我吗？谢谢

卡兹·穆罕默德·阿里·努尔

对于三个不同的，A.ItCode您不需要三个不同的选择查询来与 union all 结合，而是可以使用in子句通过单个选择查询获得三个不同的产品。

SELECT A.ItCode, A.ItName, COUNT(B.Qty) 'Quantity', COUNT(B.Qty2) 'Quantity2', COUNT(B.Qty3) 'Quantity3'
FROM tblitem A
INNER JOIN tblstocksummary B ON A.ItCode = B.ItCode AND A.ItCode in ('IL001-000151','IL001-000373', 'IL001-000166')
GROUP BY A.ItCode 
HAVING 'Quantity' !='0' AND 'Quantity2' !='0' AND 'Quantity3' !='0'

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。