PostgreSQL-使第一行显示为其他行的总数
有什么方法可以使第一行与其余行有所不同,以便显示相应列的总和?
Is there any way to make first row be different then the rest, so it would show total sum of the appropriate columns?
例如:
fruits|a|b|c
total|3|4|6
apples|1|2|3
bananas|1|1|2
oranges|1|1|1
是否有可能像这样进行查询或违反sql的逻辑?
就像这样(暂时忽略第一行):
Is it possible to make query like that or it is against the logic of sql? It would be like this (ignoring the first row for now):
SELECT fruits, sum(a), sum(b), sum(c)
FROM basket
所以第一行是不同。它会显示单词'total'而不是水果名称,并且会显示a(1 + 1 + 1 = 3),b(2 + 1 + 1 = 4)和c(3 + 2 + 1 = 6)的总和。有可能这样做吗?谢谢
So the first row would be different. It would show word 'total' instead of fruit name, and would show total sum of a (1+1+1=3), b (2+1+1=4) and c (3+2+1=6). Is it possible to do like that? Thanks
您可以避免使用CTE对表进行第二次全面扫描:
You can avoid a second full scan of the table with a CTE:
PostgreSQL 9.2模式:
create table basket(fruits text, a integer, b integer, c integer);
insert into basket(fruits, a, b, c) values('apples', 1, 1, 1),
('apples', 0, 1, 2),
('bananas', 1, 1, 2),
('oranges', 1, 1, 1);
查询:
with w as ( select fruits, sum(a) a, sum(b) b, sum(c) c
from basket
group by fruits )
select * from w union all select 'total', sum(a), sum(b), sum(c) from w
结果:
| FRUITS | A | B | C |
-----------------------
| bananas | 1 | 1 | 2 |
| oranges | 1 | 1 | 1 |
| apples | 1 | 2 | 3 |
| total | 3 | 4 | 6 |
SQL Fiddle 此处