您的位置: 首页  >  技术问答  >  如何在Oracle中的条件下使用Case计算多列的总和

如何在Oracle中的条件下使用Case计算多列的总和

分类: 技术问答 • 2022-04-24 18:53:52
问题描述：

我正在使用 oracle SQL 计算我的数据库中某些列的值的总和，并在我的条件下使用案例

I'm using oracle SQL to calculate sum of value some columns in my database with case when in my condition

这是我的带有值的 LOG0104M 表:

this is my LOG0104M table with values:

PRODUCT_CODE   PRODUCT_NAME   PACKING_STYLE   TOTAL_QUANTITY   QUANTITY_IN_FULL_CTN   SPAREBOX
 3300099        AP-1516D       30                  50                1                  NULL
 3330000        NEOSENSE       60                  20                1                  NULL

使用上面的列值，我想计算 'SPAREBOX' 的输出值是 0 或 1.要输出 'SPAREBOX' 的值是 0 或 1，它将基于 Spare_quantity 的结果与此配方:

with value of columns above, i want to calculate output value of 'SPAREBOX' is 0 OR 1. To output value of 'SPAREBOX' is 0 OR 1, it will base on result of Spare_quantity with this recipe:

1. FullBox =  (Total_Quantity / Packing_Style)
2. Spare_quantity = Total_Quantity - FullBox * Packing_Style.
    If Spare_quantity  = 0 => SpareBox column = 0
    If Spare_quantity  > 0 => SpareBox column  = 1.

示例:

1.  50 / 30 = FullBox (1,7)
2.   50 - 1.7 * 30 = Spare_quantity( 1,5)
     => Spare_quantity  > 0 and then SpareBox column = 1

预期结果:

PRODUCT_CODE   PRODUCT_NAME   PACKING_STYLE   TOTAL_QUANTITY   QUANTITY_IN_FULL_CTN   SPAREBOX
 3300099        AP-1516D       30                  50                   1                1

这是我的查询:

WITH BOXCOUNT AS (                                                         
SELECT ROUND(SUM(TOTAL_QUANTITY/PACKING_STYLE)) AS FULLBOX FROM LOG0104M)
SELECT
   L55.PRODUCT_CODE 
   , L55.PRODUCT_NAME 
   , L55.TOTAL_QUANTITY
   , L55.PACKING_STYLE
   , L55.QUANTITY_IN_FULL_CTN
   , L55.SPAREBOX
   , BC.FULLBOX

   ,CASE 
      WHEN SUM(L55.TOTAL_QUANTITY - BC.FULLBOX * L55.PACKING_STYLE) = 0  THEN L55.SPAREBOX = 0
      WHEN SUM(L55.TOTAL_QUANTITY - BC.FULLBOX * L55.PACKING_STYLE) != 0 THEN L55.SPAREBOX = 1
   END AS SPARE_QUANTITY

  FROM LOG0104M L55, BOXCOUNT BC
  GROUP BY 
    L55.PRODUCT_CODE
    , L55.PRODUCT_NAME
    , L55.TOTAL_QUANTITY
    , L55.PACKING_STYLE
    , L55.QUANTITY_IN_FULL_CTN
    , BC.FULLBOX
    , L55.SPAREBOX

它似乎以错误的结果运行，而不是预期的结果.

it seem run with wrong result and not as expected result.

如何解决问题?非常感谢

How to fix the problem ? many thank

答

你还没有提供太多的例子.如果您想要一个严格的答案，您需要包含足够的示例数据以包含您想要的所有行为.

You still haven't provided much is the way of example. If you want a rigorous answer you need to include enough example data to include all the behaviour you desire.

例如，为什么您的示例输入有两行，而您想要的结果只有一行?

For example, why does your example input have two rows, but your desired results have one row?

拼命地试图在两行之间阅读我认为您想要的只是每行多出一列，说明该行是否完全填满了它的框?

Desperately trying to read between the lines I think that all you want is an extra column on each row, saying if that row has perfectly filled its boxes or not?

这只是一个使用 MOD()...

That would simply be a CASE expression using MOD()...

SELECT
     L55.PRODUCT_CODE 
   , L55.PRODUCT_NAME 
   , L55.TOTAL_QUANTITY
   , L55.PACKING_STYLE
   , L55.QUANTITY_IN_FULL_CTN
   , L55.SPAREBOX
   , CASE WHEN MOD(L55.TOTAL_QUANTITY, L55.PACKING_STYLE) = 0 THEN 0 ELSE 1 END   AS SPARE_QUANTITY
FROM
  LOG0104M L55

如果这不是您想要的，请添加一组完整输入数据和一组完整与该输入对应的结果，展示正是您需要的.

If that's not what you want, please add a full set of input data and a full set of results that correspond to that input, demonstrating exactly what you need.

如果您不确定我的意思，请参考我之前给您的链接.

If you're uncertain about what I mean, please refer to the links I gave you previously.

相关推荐