从同一列中求和，但条件不同

Question

样本数据

create table to_delete ( otsn number, dtsn number,total_js number)  
insert into to_delete values (1,5,2) ;  
insert into to_delete values (2,4,3) ;  
insert into to_delete values (3,3,5) ;  
insert into to_delete values (4,2,7) ;  
insert into to_delete values (5,1,10) ;  

我需要得到基于otsn和dtsn的total_js和

SELECT  
    j.otsn,  
    SUM(j.total_js),  
    j1.dtsn,  
    SUM(j1.total_js)  
FROM  
    to_delete j,  
    to_delete j1  
WHERE  
    j.otsn = j1.dtsn  
GROUP BY  
    j.otsn,  
    j1.dtsn; 

但在真正的表中，我没有得到预期的结果，也没有得到otsn或dtsn的正确值，但两者都不是。

我想要的输出是

TSN, sum(total_js) of otsn, sum(total_js) of dtsn
1, 2 , 10
2, 3

、7

tsn代表过境站，otsn代表原中转站，dtsn代表最中转站。因此，有一个表，其中orgin和dest都在那里，总计数。

如果我必须计算有多少人进入otsn，计数(Total_js)，如果我必须计算多少人从dtsn到dtsn，计数(Total_js)

因此，在最后的输出，我需要一个TSN与传入的总数和传出总数。

Answer 1

在自动加入之前，取otsn和dtsn之和：

SQL> create table to_delete ( otsn number, dtsn number,total_js number)  ;
Table created
SQL> insert into to_delete values (1,5,2) ;
1 row inserted
SQL> insert into to_delete values (2,4,3) ;
1 row inserted
SQL> insert into to_delete values (3,3,5) ;
1 row inserted
SQL> insert into to_delete values (4,2,7) ;
1 row inserted
SQL> insert into to_delete values (5,1,10) ;
1 row inserted
SQL> WITH cte_otsn AS
  2   (SELECT otsn,
  3           SUM(total_js) o_total
  4      FROM to_delete
  5     GROUP BY otsn),
  6  cte_dtsn AS
  7   (SELECT dtsn,
  8           SUM(total_js) d_total
  9      FROM to_delete
 10     GROUP BY dtsn)
 11  SELECT d.dtsn,
 12         o.o_total,
 13         d.d_total
 14    FROM cte_otsn o
 15    JOIN cte_dtsn d
 16      ON d.dtsn = o.otsn;
      DTSN    O_TOTAL    D_TOTAL
---------- ---------- ----------
         1          2         10
         2          3          7
         5         10          2
         4          7          3
         3          5          5

SQL> 

更新：

SQL> create table to_delete (OTSN number,DTSN number,OHR number,DHR number,TOTAL_JS number);
Table created
SQL> INSERT INTO to_delete VALUES (1,5,2,3,2);
1 row inserted
SQL> INSERT INTO to_delete VALUES (2,4,2,4,3);
1 row inserted
SQL> INSERT INTO to_delete VALUES (3,3,3,3,5);
1 row inserted
SQL> INSERT INTO to_delete VALUES (4,2,1,4,7);
1 row inserted
SQL> INSERT INTO to_delete VALUES (5,1,4,1,10);
1 row inserted
SQL> WITH cte_data(tsn,hr,total,tsn_type) AS
  2   (SELECT otsn,
  3           ohr,
  4           total_js,
  5           'o'
  6      FROM to_delete
  7    UNION ALL
  8    SELECT dtsn,
  9           dhr,
 10           total_js,
 11           'd'
 12      FROM to_delete)
 13  SELECT tsn, hr, COALESCE(OCNT, 0) AS ocnt, COALESCE(dcnt, 0) AS dcnt
 14    FROM cte_data
 15   PIVOT (SUM(total) FOR tsn_type IN ('o' AS "OCNT", 'd' AS "DCNT"));

       TSN         HR       OCNT       DCNT
---------- ---------- ---------- ----------
         4          1          7          0
         2          4          0          7
         4          4          0          3
         1          2          2          0
         2          2          3          0
         3          3          5          5
         1          1          0         10
         5          4         10          0
         5          3          0          2
9 rows selected

SQL> 

Answer 2

甲骨文的解决方案应该是..。

    SELECT a.otsn, SUM (a.total_js) OTSN_TOTAL, SUM (b.total_js) DTSN_TOTAL
    FROM to_delete a, to_delete b
   WHERE a.otsn = b.dtsn(+)
   GROUP BY a.otsn;

谢谢，快乐编码:)

Answer 3

您的真实表可能有NULL值，而缺少otsn/dtsn值，因为SUM给出了不正确的值。最好使用FULL OUTER JOIN并相应地处理NULL，如图所示。

SELECT  
    NVL(j.otsn,j1.dtsn),  
    SUM( CASE WHEN j.total_js  IS NULL THEN 0 ELSE j.total_js END ) OTSN_TOTAL,  
    SUM( CASE WHEN j1.total_js IS NULL THEN 0 ELSE j1.total_js END ) DTSN_TOTAL  
FROM  
    to_delete j FULL OUTER JOIN  
    to_delete j1  
ON  
    j.otsn = j1.dtsn  
GROUP BY  
    j.otsn,  
    j1.dtsn;