SQL Server别名和总和

Question

我有一个像这样的表。

<表类> 项目成本平衡tbody><<tr>11032233432253

我不知道分组集是否真的是解决你问题的正确工具。您可以使用它们生成一个解决方案:

select
coalesce(project, max(case when project in (2, 3) then 23 end)) as project,
sum(cost), sum(balance)
from Projects
group by grouping sets (case when project in (2, 3) then 23 end, project)
having grouping(project) = 0 or max(case when project in (2, 3) then 23 end) is not null

https://dbfiddle.uk/?rdbms=sqlserver_2019&小提琴= ce2d7510a5cc926d03b1f0b0e51d65c1

如果这真的只是一次性的，只有这两个项目号，可能更容易做简单的联合:

select project, cost, balance
from Projects
union all
select 23, sum(cost), sum(balance)
from Projects
where project in (2, 3);

我认为您错误地将55放在第2列而不是45列，但您可以使用UNION ALL语法实现此结果-

SELECT project, Cost, Balance
FROM YOUR_TABLE
UNION ALL
SELECT 23 AS project, SUM(Cost), SUM(Balance)
FROM YOUR_TABLE
WHERE project IN (2, 3);