如何修正oracle中计算和两列时的错误值

Question

我在oracle中使用CTE来计算两个别名为2的列的值。然后我求和2个别名。这是我的桌子：

DELVRY_NO        ITM_NAME       TOTAL_QUANTITY      PACKING_STYLE    QUANTITY_IN_FULL_CTN
DVR20101900006   CHP-671R           61                   30                  60

这是我的问题：

WITH BOXCOUNT AS (                                                         
SELECT ROUND(SUM(TOTAL_QUANTITY/PACKING_STYLE)) AS FULLBOX
, ROUND(SUM(TOTAL_QUANTITY - QUANTITY_IN_FULL_CTN)) AS SPAREBOX 
FROM LOG0055D)
SELECT
L55.DELVRY_NO
, L55.ITM_NAME
, L55.TOTAL_QUANTITY
, L55.PACKING_STYLE
, L55.QUANTITY_IN_FULL_CTN
, BC.FULLBOX
, BC.SPAREBOX
,SUM(BC.FULLBOX + BC.SPAREBOX) AS TOTALBOX
FROM LOG0055D L55, BOXCOUNT BC
WHERE DELVRY_NO  = 'DVR20101900006'
GROUP BY 
L55.DELVRY_NO
, L55.ITM_NAME
, L55.TOTAL_QUANTITY
, L55.PACKING_STYLE
, L55.QUANTITY_IN_FULL_CTN
, BC.FULLBOX
, BC.SPAREBOX

当我运行我的查询时，结果如下：

DELVRY_NO       ITM_NAME  TOTAL_QUANTITY  PACKING_STYLE QUANTITY_IN_FULL_CTN  FULLBOX    SPAREBOX   TOTALBOX
DVR20101900006  CHP-671R      61               30            60               956          3077    4033

"FULLBOX"SPAREBOX"one_answers"TOTALBOX"似乎是错误的值，不幸的是：

FULLBOX   SPAREBOX   TOTALBOX
2          1           3

如何解决他们的问题？非常感谢

Answer 1

对我来说，CTE似乎错过了delvry_no列(和一个group by子句(，而主查询不应该使用cross join，而应该由delvry_no使用inner join。类似这样的东西：

WITH
boxcount
AS
(  SELECT delvry_no,
ROUND (SUM (total_quantity / packing_style)) AS fullbox,
ROUND (SUM (total_quantity - quantity_in_full_ctn)) AS sparebox
FROM log0055d
GROUP BY delvry_no)
SELECT l55.delvry_no,
l55.itm_name,
l55.total_quantity,
l55.packing_style,
l55.quantity_in_full_ctn,
bc.fullbox,
bc.sparebox,
SUM (bc.fullbox + bc.sparebox) AS totalbox
FROM log0055d l55 JOIN boxcount bc ON bc.delvry_no = l55.delvry_no
WHERE l555.delvry_no = 'DVR20101900006'
GROUP BY l55.delvry_no,
l55.itm_name,
l55.total_quantity,
l55.packing_style,
l55.quantity_in_full_ctn,
bc.fullbox,
bc.sparebox