PROSAGA码农传奇-TSN/SDN-从同一列但在不同条件下获取总和

从同一列但在不同条件下获取总和

作者: 春风助手
发布时间: 2025-03-06 05:31:51 (5天前)

自我加入前取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> 
```