在“选择查询”的结果集中添加行

Question

我正在尝试实现以下内容例如。

这就是我所拥有的...

  Name     Amount
  AAA        15
  AAA        20
  CCC        30
  CCC        50

这就是我想要的

  Name     Amount
  AAA        15
  AAA        20
             35     --(want to insert row which display sum of 1st & 2nd rows)
  CCC        30
  CCC        50 
             80      --(want to insert row which display sum of 3rd & 4th rows)

Answer 1

如果您只是按名称查找行的摘要，则可以使用联合、分组依据和顺序：

DECLARE @tbl TABLE (Name char(3), Amount int)
insert @tbl
VALUES 
 ('AAA',15)
,('AAA',20)
,('CCC',30)
,('CCC',50)

select Name, Amount
FROM @tbl
UNION ALL
SELECT Name, SUM(Amount)
FROM @tbl GROUP BY Name
ORDER BY Name

对于使用子查询和 CTE（仅用作实际表的示例替换）的工作示例，请采用以下命令：

with vals as
(
    select 'aaa' as Name, 15 as Amount
    union all
    select 'aaa' as Name, 10 as Amount
    union all
    select 'bbb' as Name, 20 as Amount
    union all
    select 'bbb' as Name, 30 as Amount
    union all
    select 'bbb' as Name, 50 as Amount
)
select *
from
(
    select 'Amount' as ValType, Name, Amount
    from vals
    union all
    select 'Total' as ValType, Name, sum(Amount)
    from vals
    group by Name
)
order by Name, ValType
;

这将按Name列分组，并在子查询中求和，然后使用手动添加的ValType标识符列对输出进行排序。

Answer 2

我已经使用 UNION 完成了此操作

SELECT * FROM 
(SELECT * FROM (VALUES ('AAA',15),
                      ('AAA',20),
                      ('CCC',30),
                      ('CCC',50)) CT (NAME,AMOUNT)

UNION ALL
SELECT NAME+' '+ 'Total' as Tot ,SUM(AMOUNT) FROM (VALUES ('AAA',15),
                      ('AAA',20),
                      ('CCC',30),
                      ('CCC',50)) CT (NAME,AMOUNT)                      
GROUP BY NAME+' '+ 'Total') X

Answer 3

我建议做 2 个选择，然后使用联合全部加入它们

Select name, amount from table
union all
select '' as total, sum(amount) from table