如何用合计显示第一列中的第二列

Question

我有一张这样的桌子：

Id  ProjectName SubProjectName  Cost
1   Project1    SubProject1 100
2   Project1    SubProject2 60
3   Project2    SubProject3 50
4   Project2    SubProject4 150
5   Project3    SubProject5 200
6   Project4    SubProject6 40

我想要这样的输出：

project1   160
subproject1 100
subproject1 60
project2    200
subproject3 50
subproject4 150
project3    200
subproject5 200
project4    40
subproject6 40

Answer 1

您可以使用UNION ALL。例如：

select projectname, sum(cost) from t group by projectname
union all
select subprojectname, cost from t

现在，如果您想要完全相同的顺序，您可以这样做：

select name, cost
from (
  select min(id) as id, 0 as sub, projectname as name, sum(cost) as cost
  from t 
  group by projectname
  union all
  select id, 1, subprojectname, cost from t
) x
order by id, sub

Answer 2

在Group by like中尝试汇总

SELECT CASE WHEN SubProjectName is not null THEN SubProjectName ELSE ProjectName END ProjectName ,SUM(cost) cost FROM TblCost
GROUP BY  ROLLUP (ProjectName,SubProjectName)
ORDER BY ProjectName,SubProjectName