SQL：插入带有汇总值的行 [英] SQL: insert rows with summarized values

问题描述

请查看我的第一个问题以查看我的主题： SQL：两列分区

我有以下表格：

  ------ ---------------------------- 
 | No1 | No2 |量|时间戳
 ---------------------------------- 
 | A | B | 10 | 01.01.2018 
 | C | D | 20 | 02.01.2018 
 | B | A | 30 | 03.01.2018 
 | D | C | 40 | 04.01.2018 
 ---------------------------------- 
  

目前我有以下结果：

  ----------------------------------------------- ------ 
 | No1 | No2 |总和（金额）通过分区|时间戳
 --------------------------------------------- -------- 
 | A | B | 40 | 01.01.2018 
 | C | D | 60 | 02.01.2018 
 | B | A | 40 | 03.01.2018 
 | D | C | 60 | 04.01.2018 
 ------------------------------------------- ---------- 
  

与SQL（来自Vamsi Prabhala的第一个问题答案）：

  select no1，no2，sum（amount）over（partition by least（no1，no2），greatest（no1 ，no2））作为总数，时间戳
从tbl 
  

现在的问题是如何我可以将结果添加到结果中：

  ----------------- ----------------------------------- 
 | No1 | No2 |总和（金额）通过分区|时间戳
 --------------------------------------------- ------- 
 | A | B | 40（可选）| 01.01.2018 
 | B | A | 40（可选）| 02.01.2018 
 | AB |（NULL）| 40 | 
 | C | D | 60（可选）| 03.01.2018 
 | D | C | 60（可选）| 04.01.2018 
 | CD |（NULL）| 60 | 
 ---------------------------------------------- ------ 
  

请注意，可以有多行，例如值（ No1 = A，No2 = B）

UPDATE：添加timestamp列以更具体地实现我想要实现的功能

h2_lin>解决方案

  SELECT 
 LEAST（No1，No2）|| '：'|| GREATEST（No1，No2）as set_label，
 No1，
 No2，
 SUM（金额）AS金额，
图章
 FROM 
 tbl 
 GROUP BY 
 GROUPING SETS（
（至少（No1，No2），最大（No1，No2），No1，No2，Stamp），
（至少（No1，No2），最大No1，No2））
）
  

http://sqlfiddle.com/#!4/9afd5/18

如果每一行都有唯一的标识符，会更好......

http://sqlfiddle.com/#!4/e9e95/1

please see my first question to my topic: SQL: partition over two columns

I have following table:

----------------------------------
| No1 | No2  | Amount| Timestamp
----------------------------------
| A   |  B   |    10 |  01.01.2018
| C   |  D   |    20 |  02.01.2018
| B   |  A   |    30 |  03.01.2018
| D   |  C   |    40 |  04.01.2018
----------------------------------

I have the following results at the moment:

-----------------------------------------------------
| No1 | No2  | Sum(Amount) over partition | Timestamp
-----------------------------------------------------
| A   |  B   |    40                      | 01.01.2018
| C   |  D   |    60                      | 02.01.2018
| B   |  A   |    40                      | 03.01.2018
| D   |  C   |    60                      | 04.01.2018
-----------------------------------------------------

with the SQL (from the first question with Vamsi Prabhala's answer):

select no1,no2,sum(amount) over(partition by least(no1,no2),greatest(no1,no2)) as total, timestamp
from tbl

The question for me now is how can I add rows to the results like:

----------------------------------------------------
| No1 | No2  | Sum(Amount) over partition | Timestamp
----------------------------------------------------
| A   |  B   |    40  (optional)          | 01.01.2018
| B   |  A   |    40  (optional)          | 02.01.2018
| AB  |(NULL)|    40                      |
| C   |  D   |    60  (optional)          | 03.01.2018
| D   |  C   |    60  (optional)          | 04.01.2018
| CD  |(NULL)|    60                      |
----------------------------------------------------

Please be aware that there can be multiple rows with for example the values (No1=A,No2=B)

UPDATE: added timestamp column to be more specific what I want to achieve

解决方案

SELECT
  LEAST(No1, No2) || ':' || GREATEST(No1, No2)     AS set_label,
  No1,
  No2,
  SUM(Amount)                               AS Amount,
  Stamp
FROM
  tbl
GROUP BY
  GROUPING SETS (
    (LEAST(No1, No2), GREATEST(No1, No2), No1, No2, Stamp),
    (LEAST(No1, No2), GREATEST(No1, No2))
  )

http://sqlfiddle.com/#!4/9afd5/18

Would be better if each row has a unique identifier...

http://sqlfiddle.com/#!4/e9e95/1

这篇关于SQL：插入带有汇总值的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！