代码之家 › 专栏 › 技术社区 › marcspector1972

我是否可以在Select或Where中使用一个函数来防止相互抵消的行被拉入提取?

oracle sql

marcspector1972 · 技术社区 · 8 月前

我有一个收回服务费的脚本。有时服务会收费,然后取消,如下图所示。我想知道是否有一个函数可以在我的Select或Where中使用,以防止那些相互抵消的行被拉入提取?:

代码:

SELECT
 c.service_dt_tm "Service Date",
 c.item_price,
 c.charge_type_cd

FROM
 charge c,

 customer e

WHERE
 c.bill_item_id = 363803 AND
 c.charge_description = 'Oil Change' AND
 c.customer_id = e.customer_id AND

 e.active_ind = 1 AND
 e.customer_type_cd IN (9489, 9480, 9488) AND
 e.garage_loc_cd = 8473

我尝试过但运气不佳的事情:

Where条款中的案例

匹配_识别

非常感谢您的任何帮助、指导、建议、指导和推荐。

谢谢你,罗兰

1 回复 | 直到 8 月前

MT0 8 月前

统计每种类型的行数,并过滤出同时存在这两种类型的行都:

SELECT service_dt_tm AS "Service Date",
       item_price,
       charge_type_cd
FROM   (
  SELECT c.service_dt_tm,
         c.item_price,
         c.charge_type_cd,
         ROW_NUMBER() OVER (
           PARTITION BY c.customer_id,
                        c.service_dt_tm,
                        ABS(c.item_price),
                        c.charge_type_cd
           ORDER BY     NULL
         ) AS rn,
         LEAST(
           COUNT(CASE c.charge_type_cd WHEN 1577 THEN 1 END) OVER (
             PARTITION BY c.customer_id, c.service_dt_tm, ABS(c.item_price)
             ORDER BY NULL
           ),
           COUNT(CASE c.charge_type_cd WHEN 1576 THEN 1 END) OVER (
             PARTITION BY c.customer_id, c.service_dt_tm, ABS(c.item_price)
             ORDER BY NULL
           )
         ) AS num_matched
  FROM   charge c
         INNER JOIN customer e
         ON c.customer_id = e.customer_id
  WHERE  c.bill_item_id = 363803
  AND    c.charge_description = 'Oil Change'
  AND    e.active_ind = 1
  AND    e.customer_type_cd IN (9489, 9480, 9488)
  AND    e.garage_loc_cd = 8473
)
WHERE  rn > num_matched;

对于样本数据:

CREATE TABLE charge (customer_id, service_dt_tm, item_price, charge_type_cd, bill_item_id, charge_description) AS
  SELECT 1, TO_DATE('1970-01-01 12:34:56', 'YYYY-MM-DD HH24:MI:SS'), 2.15, 1577, 363803, 'Oil Change' FROM DUAL UNION ALL
  SELECT 1, TO_DATE('1970-01-01 12:34:56', 'YYYY-MM-DD HH24:MI:SS'), -2.15, 1576, 363803, 'Oil Change' FROM DUAL UNION ALL
  SELECT 1, TO_DATE('1970-01-01 12:34:56', 'YYYY-MM-DD HH24:MI:SS'), 2.15, 1577, 363803, 'Oil Change' FROM DUAL UNION ALL
  SELECT 1, TO_DATE('1970-01-01 12:34:56', 'YYYY-MM-DD HH24:MI:SS'), 2.15, 1577, 363803, 'Oil Change' FROM DUAL;

CREATE TABLE customer (customer_id, active_ind, customer_type_cd, garage_loc_cd) AS
  SELECT 1, 1, 9489, 8473 FROM DUAL;

输出:

服务日期	商品价格	CHARGE_TYPE_CD
1970-01-01 12:34:56	2.15	1577
1970-01-01 12:34:56	2.15	1577

fiddle