PostgreSQL-使第一行显示为其他行的总数 [英] PostgreSQL - making first row show as total of other rows

问题描述

有什么方法可以使第一行与其余行有所不同，以便显示相应列的总和？

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 此处

这篇关于PostgreSQL-使第一行显示为其他行的总数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！