主页 / dba / 问题 / 214740
Accepted
heye
Asked: 2018-08-13 20:16:49 +0800 CST

仅当条件满足整个列时才返回值

  • 772

任何人都可以帮助我如何实现以下结果

输入数据

ORDER_NUMBER    LINE_ITEM   FLAG
LP-13288    1   TRUE
LP-13288    2   FALSE
LP-13288    3   (null)
LP-13288    4   FALSE
LP-13289    1   FALSE
LP-13289    2   FALSE
LP-13290    1   TRUE
LP-13290    2   TRUE
LP-13290    3   TRUE
LP-13292    1   FALSE
LP-13293    2   TRUE

但我期待如下结果：

ORDER_NUMBER    LINE_ITEM   FLAG    RESULT
LP-13288    1   TRUE    0
LP-13288    2   FALSE   0
LP-13288    3   (null)  0
LP-13288    4   FALSE   0
LP-13289    1   FALSE   0
LP-13289    2   FALSE   0
LP-13290    1   TRUE    1
LP-13290    2   TRUE    1
LP-13290    3   TRUE    1
LP-13292    1   FALSE   0
LP-13293    2   TRUE    1

需要实现的场景

  1. 对于一组 order_number，如果所有订单项都为“true”，则结果字段需要为“1”

  2. 对于一组 order_number，如果任何订单项包含混合标志（true、false、null），则结果字段需要填充 '0'

任何帮助，将不胜感激。

oracle group-by

3 个回答

  1. Best Answer

    您可以使用窗口聚合（带有OVER子句的聚合函数）将条件应用于一列值，同时计算详细信息行的结果。以下是在您的情况下如何使用窗口聚合：

    SELECT
  order_number,
  line_item,
  flag,
  CASE COUNT(CASE flag WHEN 'true' THEN 1 END)
       OVER (PARTITION BY order_number)
  WHEN COUNT(*)
       OVER (PARTITION BY order_number)
  THEN 1
  ELSE 0
  END AS result
FROM
  orders
;

    该COUNT(CASE flag WHEN 'true' THEN 1 END) OVER (PARTITION BY order_number)表达式计算每个真值的数量，order_number并为具有相同 的行返回相同的值order_number。因此，您也可以在行组内生成相同的值。

    上述的变体（使用反向逻辑和一次计数而不是两次）：

    SELECT
  order_number,
  line_item,
  flag,
  CASE WHEN 
       COUNT(CASE flag WHEN 'true' THEN NULL ELSE 1 END)
       OVER (PARTITION BY order_number)
       = 0
  THEN 1
  ELSE 0
  END AS result
FROM
  orders
;
    • 4

  2. 使用 CTE 轻松完成。

    with cte as ( 
SELECT order_number, 
 SUM(CASE WHEN FLAG='true' THEN 1 ELSE 0 END) as flagsums, COUNT(*) as flagcounts 
    FROM orders
GROUP BY order_number
)
SELECT orders.*, CASE WHEN flagsums=flagcounts THEN '1' ELSE '0' END as RESULT 
FROM cte JOIN orders
     ON cte.order_number = orders.order_number ;

    基本上，我正在汇总数据并获取每个订单的标志总和。

    那么满足这个条件：

    因此，对于 ORDER_NUMBER，如果 FLAG 为真且其余标志为假或空，则需要将 RESULT 填充为“1”，如果 ORDER_NUMBER 的所有 FLAG 均为真，则 RESULT 应为“0”

    ...通过简单地检查每个订单的标志总和是否与该订单的行数相同。

    • 1

  3. 稍作修改即可获得所需的输出 

     WITH true AS
 (SELECT COUNT(*) cnt,order_number FROM orders
 WHERE flag='true' GROUP BY order_number),

   total AS
 (
 SELECT COUNT(*) tot,order_number FROM orders 
 GROUP BY order_number
 )

SELECT order_number,line_item,flag,'0' RESULT FROM orders
WHERE order_number NOT IN (SELECT total.order_number 
 FROM true,total WHERE true.cnt=total.tot)
 UNION
SELECT order_number,line_item,flag,'1' RESULT FROM orders
WHERE order_number IN (SELECT total.order_number 
 FROM true,total WHERE true.cnt=total.tot)
 ORDER BY order_number,line_item;
    • 1

相关问题