20分求一条跨表查询的语句，我这里老是出错！

[frank_77]

有两个表：BAT_REC1,PRDT1

BAT_REC1 批号结存表，结构为：
PRD_NO(品号)，QTY_IN(入库数量)，QTY_OUT(出库数量)，QTY_ON_PRC(在制品数量)

PRDT1 货品结存库，结构为：
PRD_NO(品号)，QTY_ON_ODR（受订数量）

需要查出的结果为：
品号 库出数量（入库-出库） 在制品数量 受订数量 可用数量（入库-出库-受订量）


我写的SQL语句是：
SELECT BAT_REC1.PRD_NO, SUM(BAT_REC1.QTY_IN) - SUM(BAT_REC1.QTY_OUT)
AS 库存数量, SUM(PRDT1.QTY_ON_ODR) AS 受订数量,
SUM(BAT_REC1.QTY_ON_PRC) AS 在制品数量, SUM(BAT_REC1.QTY_IN)
- SUM(BAT_REC1.QTY_OUT) - SUM(BAT_REC1.QTY_ON_ODR) AS 可用库存
FROM PRDT1 INNER JOIN
BAT_REC1 ON PRDT1.PRD_NO = BAT_REC1.PRD_NO
WHERE (BAT_REC1.PRD_NO < 'B')
GROUP BY BAT_REC1.PRD_NO

其它数据都是对的，只有受订数量有问题，数量应该是6，而查询出来的数据是30，请高手指教，问题出在那里

[frank_77]

那么，你看这句话应该如何去写呢

[davorsuker39]

这样呢？
SELECT BAT_REC1.PRD_NO, SUM(BAT_REC1.QTY_IN) - SUM(BAT_REC1.QTY_OUT)
AS 库存数量, SUM(PRDT1.QTY_ON_ODR) AS 受订数量,
SUM(BAT_REC1.QTY_ON_PRC) AS 在制品数量, SUM(BAT_REC1.QTY_IN)
- SUM(BAT_REC1.QTY_OUT) - SUM(BAT_REC1.QTY_ON_ODR) AS 可用库存
FROM PRDT1 INNER JOIN
(SELECT DISTINCT PRD_NO,SUM(QTY_ON_ODR) AS QTY_ON_ODR FROM BAT_REC1 GROUP BY PRD_NO ) ON PRDT1.PRD_NO = BAT_REC1.PRD_NO
WHERE (BAT_REC1.PRD_NO < 'B')
GROUP BY BAT_REC1.PRD_NO

[frank_77]

不对阿，还是有问题

[davorsuker39]

什么问题？？怎么报错的？

[frank_77]

报错：
the column prefix 'bat_rec1' does not match with a table name or alias name used the query

[davorsuker39]

不好意思！写错了！
SELECT BAT_REC1.PRD_NO, SUM(BAT_REC1.QTY_IN) - SUM(BAT_REC1.QTY_OUT)
AS 库存数量, PRDT1.QTY_ON_ODR AS 受订数量,
SUM(BAT_REC1.QTY_ON_PRC) AS 在制品数量, SUM(BAT_REC1.QTY_IN)
- SUM(BAT_REC1.QTY_OUT) - SUM(BAT_REC1.QTY_ON_ODR) AS 可用库存
FROM (SELECT DISTINCT PRD_NO,SUM(QTY_ON_ODR) AS QTY_ON_ODR FROM BAT_REC1 ) AS PRDT1 INNER JOIN BAT_REC1 ON PRDT1.PRD_NO = BAT_REC1.PRD_NO
WHERE (BAT_REC1.PRD_NO < 'B')
GROUP BY BAT_REC1.PRD_NO

[frank_77]

column 'prdt1.qty_on_odr' is invalid in the select list because it is not contained in either an aggregate function or the group by clause

[frank_77]

这句话不加sum就会报错

[honestface]

表PRDT1 上没有(BAT_REC1.PRD_NO < 'B')条件限制,加上

[honestface]

表PRDT1 上没有(PRD_NO < 'B')条件限制,加上

[davorsuker39]

SELECT BAT_REC1.PRD_NO, SUM(BAT_REC1.QTY_IN) - SUM(BAT_REC1.QTY_OUT)
AS 库存数量, PRDT1.QTY_ON_ODR AS 受订数量,
SUM(BAT_REC1.QTY_ON_PRC) AS 在制品数量, SUM(BAT_REC1.QTY_IN)
- SUM(BAT_REC1.QTY_OUT) - SUM(BAT_REC1.QTY_ON_ODR) AS 可用库存
FROM (SELECT DISTINCT PRD_NO,SUM(QTY_ON_ODR) AS QTY_ON_ODR FROM BAT_REC1 GROUP BY PRD_NO) AS PRDT1 INNER JOIN BAT_REC1 ON PRDT1.PRD_NO = BAT_REC1.PRD_NO
WHERE (BAT_REC1.PRD_NO < 'B')
GROUP BY BAT_REC1.PRD_NO

[davorsuker39]

SELECT BAT_REC1.PRD_NO, SUM(BAT_REC1.QTY_IN) - SUM(BAT_REC1.QTY_OUT)
AS 库存数量, PRDT1.QTY_ON_ODR AS 受订数量,
SUM(BAT_REC1.QTY_ON_PRC) AS 在制品数量, SUM(BAT_REC1.QTY_IN)
- SUM(BAT_REC1.QTY_OUT) - SUM(BAT_REC1.QTY_ON_ODR) AS 可用库存
FROM (SELECT DISTINCT PRD_NO,SUM(QTY_ON_ODR) AS QTY_ON_ODR FROM PRDT1 GROUP BY PRD_NO) AS PRDT1 INNER JOIN BAT_REC1 ON PRDT1.PRD_NO = BAT_REC1.PRD_NO
WHERE (BAT_REC1.PRD_NO < 'B')
GROUP BY BAT_REC1.PRD_NO

[davorsuker39]

现在测试通过了吗？

[yanglimei]

在两个表里PRD_NO(品号)都不能有重复

[frank_77]

测试还是不通过？

[frank_77]

测试还是不通过

[cainiao000]

帮你顶

[lsxaa]

列出数据来... 看看

[davorsuker39]

什么错？

[davorsuker39]

是不是有NULL的缘故？