时间戳

首页 > 解决方案 > 如何在 Oracle 中得到正确的总和结果

sql - 如何在 Oracle 中得到正确的总和结果

问题描述

我正在使用子查询来计算别名的总和，但它似乎返回错误的结果

我的桌子：

itm_code          itm_name            PACKING_STYLE       TOTAL_QUANTITY

3211483       DEODORIZATION              1                      32
3211484       DEODORIZATION FILTER       1                      20
3211485       FILTER                     30                     30
3211486       FILTER-RO 8INCH(S)         30                     30

这是我的食谱：

1. Full box = Integer of(total qty/ packing style)

2. Spare_box: via spare_quantity
   spare_quantity = total_quantity - full_box * packing_style.
   If spare_quantity = 0 => spare_box = 0
   If spare_quantity  > 0 => spare_box = 1

这是我的查询：

    SELECT 
         l48.itm_code itmcd
         , l48.itm_name itmname
         , l48.PACKING_STYLE
         , l48.TOTAL_QUANTITY             
         , FULLBOX
         , SPARE_BOX
                    
         , SUM(FULLBOX + SPARE_BOX) AS TOTALBOX 
      from 
         log0048d l48
         , ( SELECT 
                
                ROUND(SUM(l48.TOTAL_QUANTITY / l48.PACKING_STYLE)) AS FULLBOX
                , CASE WHEN ROUND(SUM(l48.TOTAL_QUANTITY - (l48.TOTAL_QUANTITY/l48.PACKING_STYLE) * l48.PACKING_STYLE)) =  0 THEN  0
                       WHEN ROUND(SUM(l48.TOTAL_QUANTITY - (l48.TOTAL_QUANTITY/l48.PACKING_STYLE) * l48.PACKING_STYLE)) >  0 THEN  1
                   END AS SPARE_BOX
            FROM log0048d l48 )
         
     where 1 = 1
       AND l48.reqst_no  = 'SMO21071900398'         
      GROUP BY 
    
        l48.itm_code
        , l48.itm_name
        , l48.reqst_no_itm   
        , l48.PACKING_STYLE
        , l48.TOTAL_QUANTITY             
        , FULLBOX
        , SPARE_BOX

然后我收到错误的是：

itm_code        itm_name      PACKING_STYLE  TOTAL_QUANTITY    FULLBOX    SPAREBOX    TOTALBOX

3211483     DEODORIZATION           1             32             58          0           58
3211484     DEODORIZATION FILTER    1             20             58          0           58
3211485     FILTER                  30            30             58          0           58
3211486     FILTER-RO 8INCH(S)      30            30             58          0           58

我期望的正确结果如下：

itm_code        itm_name      PACKING_STYLE  TOTAL_QUANTITY    FULLBOX    SPAREBOX    TOTALBOX

3211483     DEODORIZATION           1             32             32          0           32
3211484     DEODORIZATION FILTER    1             20             20          0           20
3211485     FILTER                  30            30             1           0           1
3211486     FILTER-RO 8INCH(S)      30            30             1           0           1

我怎样才能得到我预期的正确结果？

标签： sqloracleoracle11g

解决方案

您的示例数据不完整，或者我没有得到您的要求，但是

select 
  code, 
  name, 
  packing_style, 
  total_quantity, 
  floor(total_quantity / packing_style) as fullbox, 
  sign(total_quantity - floor(total_quantity / packing_style) * packing_style) as sparebox,
  floor(total_quantity / packing_style) + sign(total_quantity - floor(total_quantity / packing_style) * packing_style) as totalbox 
from thetable

应该做的工作。不需要分组（尤其不是几乎每一列结果，这样的分组有什么意义）

当然，您可以使其更具可读性和更好的可维护性，例如如下

with 

fbe(code, fullbox) as (
  select 
    code, 
    floor(total_quantity / packing_style) as fullbox
  from thetable
),

sbe(code, sparebox) as (
  select
    t.code,
    sign(t.total_quantity - fbe.fullbox * t.packing_style) as sparebox
  from thetable t inner join fbe on t.code = fbe.code
)

select 
  t.code,
  t.name,
  t.packing_style,
  t.total_quantity,
  fbe.fullbox,
  sbe.sparebox,
  fbe.fullbox + sbe.sparebox as totalbox
from thetable t 
  inner join fbe on t.code = fbe.code 
  inner join sbe on t.code = sbe.code

您也可以sparebox通过取模操作获得 的值，即如果其余部分total_quantity/packing_style与0您需要的备用箱不同，否则您不需要。即您也可以按如下方式编写查询：

select 
  code, 
  name, 
  packing_style, 
  total_quantity, 
  floor(total_quantity / packing_style) as fullbox, 
  sign(mod(total_quantity / packing_style)) as sparebox,
  floor(total_quantity / packing_style) + sign(mod(total_quantity/ packing_style)) as totalbox 
from thetable

此外，totalbox将始终是fullbox（如果总数量是包装样式的倍数）或fullbox + 1（否则），因此您可以进一步简化如下：

select 
  code, 
  name, 
  packing_style, 
  total_quantity, 
  floor(total_quantity / packing_style) as fullbox, 
  sign(mod(total_quantity / packing_style)) as sparebox,
  ceil(total_quantity / packing_style) as totalbox 
from thetable

请参阅floor、ceil、sign和mod的文档

推荐阅读