mysql - SQL 查询以获取结果集最后一行中所有列值的总和以及行总和(分组依据)

Question

有人可以帮我写一个查询来获取 TCS 和 TRS 吗？

ID  Jan Feb Mar TRS
1   4   5   6   15
2   5   5   5   15
3   1   1   1   3
TCS 10  11  12  

TCS(Total Column Sum)和TRS(Total Row Sum)分别是新的列和行，给出了它们。

Answer 1

您可以使用 GROUP BY 和 WITH ROLLUP，如下所示:

SELECT
    id
,   SUM(jan) as jan
,   SUM(feb) as feb
,   SUM(mar) as mar
,   SUM(jan+feb+mar) as TRS
FROM test
GROUP BY id WITH ROLLUP

Live demo on sqlfiddle.