sql - oracle sql如何获取不匹配的正记录

Question

大家好，我需要一些帮助来编写一个 sql 语句，该语句基本上会根据 my_id、report_id 指出没有相应负匹配数的行。

这是为了更好地解释的表声明。

   CREATE TABLE ."TEST"  
   ( "REPORT_ID" VARCHAR2(100 BYTE),  
"AMOUNT" NUMBER(17,2),  
"MY_ID" VARCHAR2(30 BYTE),  
"FUND" VARCHAR2(20 BYTE),  
"ORG" VARCHAR2(20 BYTE)  
   )  

这是一些示例数据

Insert into TEST (REPORT_ID,AMOUNT,MY_ID,FUND,ORG) values ('1',50,'910','100000','67120');

Insert into TEST (REPORT_ID,AMOUNT,MY_ID,FUND,ORG) values ('1',-50,'910','100000','67130');

Insert into TEST (REPORT_ID,AMOUNT,MY_ID,FUND,ORG) values ('1',100,'910','100000','67150');

Insert into TEST (REPORT_ID,AMOUNT,MY_ID,FUND,ORG) values ('2',200,'910','100000','67130');

Insert into TEST (REPORT_ID,AMOUNT,MY_ID,FUND,ORG) values ('2',-200,'910','100000','67120');
INSERT INTO TEST (REPORT_ID, AMOUNT, MY_ID, FUND, ORG) VALUES ('1', '40.17', '910', '100000', '67150')  
INSERT INTO TEST (REPORT_ID, AMOUNT, MY_ID, FUND, ORG) VALUES ('1', '-40.17', '910', '100000', '67150')  
INSERT INTO TEST (REPORT_ID, AMOUNT, MY_ID, FUND, ORG) VALUES ('1', '40.17', '910', '100000', '67150')  

如果您创建该表并仔细观察，您会注意到通过 report_id 和 my_id，大多数正金额都有一个直接的负金额。另一方面，我需要通过 my_id 和 report_id 识别那些没有相应负数的正数。

预期结果应如下所示

"REPORT_ID"                   "FUND"                        "MY_ID"                       "ORG"                         "AMOUNT"                      
"1"                           "100000"                      "910"                         "67150"                       "40.17"                       
"1"                           "100000"                      "910"                         "67150"                       "100"                         

任何想法如何能够实现这一点。

编辑:
发布了错误的输出结果。只是要清楚基金和组织在比赛结束之前并不重要。例如，如果我使用 plsql 写这个，我会找到我有多少个负数，然后我有多少个正数，将每个正数与每个负数进行比较并删除它们，然后我会留下任何没有负数的正数.
对于造成的困惑，我深表歉意。希望现在更清楚了。一旦我完成了所有比赛，我最终应该只剩下正数。

编辑:

additional inserts

Insert into TEST (REPORT_ID,AMOUNT,MY_ID,FUND,ORG) values ('5',71,'911','100000','67150');
Insert into TEST (REPORT_ID,AMOUNT,MY_ID,FUND,ORG) values ('5',71,'911','100000','67120');
Insert into TEST (REPORT_ID,AMOUNT,MY_ID,FUND,ORG) values ('5',71,'911','100000','67140');
Insert into TEST (REPORT_ID,AMOUNT,MY_ID,FUND,ORG) values ('5',71,'911','100000','67130');
Insert into TEST (REPORT_ID,AMOUNT,MY_ID,FUND,ORG) values ('5',71,'911','100000','67130');
Insert into TEST (REPORT_ID,AMOUNT,MY_ID,FUND,ORG) values ('5',71,'911','100000','67130');
Insert into TEST (REPORT_ID,AMOUNT,MY_ID,FUND,ORG) values ('5',-71,'911','100000','67150');
Insert into TEST (REPORT_ID,AMOUNT,MY_ID,FUND,ORG) values ('5',-71,'911','100000','67150');
Insert into TEST (REPORT_ID,AMOUNT,MY_ID,FUND,ORG) values ('5',-71,'911','100000','67150');

Answer 1

新版本

这应该只返回您想要的行。如果您不关心 org 或 fund ，那么您可以使用别名为 x 的查询:

select distinct t1.report_id, t1.fund, t1.my_id, t1.org, t1.amount
  from test t1,
      (select distinct t.report_id, t.my_id, abs(amount) as amount
         from test t
        group by t.report_id, t.my_id, abs(amount) 
       having sum(t.amount) > 0) x
 where t1.report_id = x.report_id
   and t1.my_id = x.my_id
   and t1.amount = x.amount;

上一版本

select * 
  from test t
minus
select t1.*
  from test t1,
       test t2
 where t1.amount = -1*t2.amount
   and t1.report_id = t2.report_id
   and t1.my_id = t2.my_id;

这只是给出了 amt 100 行的输出行。我已经要求您在评论中澄清为什么应该包括任何 200 行(如果应该的话)。我也不确定您是否希望包含 47.17 值之一。这样做的困难在于您提供的示例数据中的两个正值是相同的，这是正确的吗？