sql - 在特定组之后在 SQL 输出中动态创建行

Question

我的临时表中有如下数据:

AccountID   Date          Product1   Product2  Product3
--------------------------------------------------------   
101         02/24/2019       3          4        5
101         02/20/2019       4          5        6
102         02/24/2019       5          1        2
102         02/20/2019       1          2        5
102         02/16/2019       2          3        2
103         02/24/2019       2          3        4

我想在每个帐户后获得一个总行，其中包含针对特定产品帐户订购的产品总数

所需输出:

AccountID   Date          Product1   Product2  Product3
--------------------------------------------------------   
101         02/24/2019       3          4        5
101         02/20/2019       4          5        6
            total            7          9        11
102         02/24/2019       5          1        2
102         02/20/2019       1          2        5
102         02/16/2019       2          3        2  
            total            8          6        9
103         02/24/2019       2          3        4
            total            2          3        4

注意:特定帐户下的行数不是恒定的

请指导我。

Answer 1

您可以使用ROLLUP来获得所需的输出。

SELECT CASE 
         WHEN date IS NULL THEN '' 
         ELSE Cast(accountid AS VARCHAR(10)) 
       END AS accountid, 
       CASE 
         WHEN date IS NULL THEN 'Total' 
         ELSE Cast(date AS VARCHAR(10)) 
       END AS date, 
       product1, 
       product2, 
       product3 
FROM   (SELECT accountid, 
               date, 
               Sum(product1) AS Product1, 
               Sum(product2) AS Product2, 
               Sum(product3) AS Product3 
        FROM   @mytable 
        GROUP  BY rollup( accountid, date ))t 
WHERE  accountid IS NOT NULL 

输出

+-----------+------------+----------+----------+----------+
| accountid | date       | product1 | product2 | product3 |
+-----------+------------+----------+----------+----------+
| 101       | 2019-02-20 | 4        | 5        | 6        |
+-----------+------------+----------+----------+----------+
| 101       | 2019-02-24 | 3        | 4        | 5        |
+-----------+------------+----------+----------+----------+
|           | Total      | 7        | 9        | 11       |
+-----------+------------+----------+----------+----------+
| 102       | 2019-02-16 | 2        | 3        | 2        |
+-----------+------------+----------+----------+----------+
| 102       | 2019-02-20 | 1        | 2        | 5        |
+-----------+------------+----------+----------+----------+
| 102       | 2019-02-24 | 5        | 1        | 2        |
+-----------+------------+----------+----------+----------+
|           | Total      | 8        | 6        | 9        |
+-----------+------------+----------+----------+----------+
| 103       | 2019-02-24 | 2        | 3        | 4        |
+-----------+------------+----------+----------+----------+
|           | Total      | 2        | 3        | 4        |
+-----------+------------+----------+----------+----------+

Online Demo