如何在第一列中显示第二列的总计

tnkciper 于 2021-07-24 发布在 Java

关注(0)|答案(2)|浏览(536)

我有一张这样的table：

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

sql

来源：https://stackoverflow.com/questions/63031954/how-to-display-second-column-in-first-column-with-total

2条答案

按热度按时间

tp5buhyn1#

你可以用 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

赞(0）回复(0）举报 2021-07-24

edqdpe6u2#

按喜欢的方式分组尝试汇总

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

赞(0）回复(0）举报 2021-07-24

我来回答

如何在第一列中显示第二列的总计

2条答案

相关问题

热门标签

最新问答