我想获取一个仓单中的每个商品的:预期数量、实际数量、剩余数量,并且剩余数量大于零使用以下语句,报错:[Microsoft][ODBC SQL Server Driver][SQL Server]列名 'sumQty1' 无效。
SELECT CmdyID, SUM(QtyExpect) AS sumQty1, SUM(QtyActual) AS sumQty2, (sumQty1 - sumQty2) AS sumQty3
FROM OWsT_Order_Item
WHERE OrderID = 'P101201001' AND sumQty3 > 0
GROUP BY CmdyID改成以下语句才可以,但我又不会判断剩余数量大于零
SELECT CmdyID, SUM(QtyExpect) AS sumQty1, SUM(QtyActual) AS sumQty2, (SUM(QtyExpect) - SUM(QtyActual)) AS sumQty3
FROM OWsT_Order_Item
GROUP BY CmdyID并且,最重要的是,以上只是简单的示例,实际使用时:预期数量 和 实际数量,也是通过大量SQL语句从不同的表计算出来的,因此就算使用第二种修改后方法,也会SQL语句太长、太乱、效率低下。请问有没有简洁的方法,存储过程也可以,谢谢!
SELECT CmdyID, SUM(QtyExpect) AS sumQty1, SUM(QtyActual) AS sumQty2, (sumQty1 - sumQty2) AS sumQty3
FROM OWsT_Order_Item
WHERE OrderID = 'P101201001' AND sumQty3 > 0
GROUP BY CmdyID改成以下语句才可以,但我又不会判断剩余数量大于零
SELECT CmdyID, SUM(QtyExpect) AS sumQty1, SUM(QtyActual) AS sumQty2, (SUM(QtyExpect) - SUM(QtyActual)) AS sumQty3
FROM OWsT_Order_Item
GROUP BY CmdyID并且,最重要的是,以上只是简单的示例,实际使用时:预期数量 和 实际数量,也是通过大量SQL语句从不同的表计算出来的,因此就算使用第二种修改后方法,也会SQL语句太长、太乱、效率低下。请问有没有简洁的方法,存储过程也可以,谢谢!
解决方案 »
- WIN 2003 R2(X64)环境下的SQL 2005 安装过程中的小问题
- SQL语句问题,这个问题困扰了两天了!
- 引碧血剑的异构数据库之间用SQL语句转换 SQL Server 转 Excel加入条件....where 更深一点。详...
- 怎么修改标识ID的值
- 那位能给我讲讲bcp的详细用法!最好能举个实例!谢谢!
- 如何在2005的sys.databases视图建立这样的触发器呢
- 请教一句SQL(还是昨天那个问题)
- 怎样复制sql server的结构而不是数据
- 1500分帖子结贴,请pengdali(大力)接分!!!
- 导入文本文件中数据的时候发生错误
- AND 与or的应用问题
- 一个多表查询问题?谢谢!
SELECT
CmdyID, SUM(QtyExpect) AS sumQty1, SUM(QtyActual) AS sumQty2,
(sumQty1 - sumQty2) AS sumQty3
FROM OWsT_Order_Item
WHERE OrderID = 'P101201001' AND sumQty3 > 0
GROUP BY CmdyID--这个报错是因为sumQty1,sumQty2是你在当前层次的别名,嵌套后方可使用。
FROM OWsT_Order_Item
WHERE OrderID = 'P101201001' AND sumQty1 - sumQty2 > 0
GROUP BY CmdyID
不想嵌套,就这样:SELECT
CmdyID, SUM(QtyExpect) AS sumQty1, SUM(QtyActual) AS sumQty2,
SUM(QtyExpect) - SUM(QtyActual) AS sumQty3
FROM OWsT_Order_Item
WHERE
OrderID = 'P101201001' AND
SUM(QtyExpect) - SUM(QtyActual) > 0
GROUP BY CmdyID
FROM OWsT_Order_Item
GROUP BY CmdyID
having sum(QtyExpect - QtyActual)>0
FROM OWsT_Order_Item
GROUP BY CmdyID
having sum(QtyExpect - QtyActual)>0语句已经和存储差不多了
from
(
SELECT CmdyID, SUM(QtyExpect) AS sumQty1, SUM(QtyActual) AS sumQty2, (sumQty1 - sumQty2) AS sumQty3
FROM OWsT_Order_Item
WHERE OrderID = 'P101201001'
GROUP BY CmdyID) t
where t.sumQty3>0