postgreSQL GROUP BY

前端之家收集整理的这篇文章主要介绍了postgreSQL GROUP BY前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

postgreSQL GROUP BY 官方文档

GROUP BY语法:

SELECT select_list
    FROM ...
    [WHERE ...]
    GROUP BY grouping_column_reference [,grouping_column_reference]...

测试表及示例:

=> SELECT * FROM test1;
 x | y ---+---
 a | 3
 c | 2
 b | 5
 a | 1
(4 rows)

用法一:

=> SELECT x FROM test1 GROUP BY x;
 x ---
 a
 b
 c
(3 rows)

用法二:

=> SELECT x,sum(y) FROM test1 GROUP BY x;
 x | sum ---+-----
 a | 4
 b | 5
 c | 2
(3 rows)

我们不能这样写SELECT* FROM test1的GROUP BY X,因为可能与每个组相关联的列Y没有单一的价值.

用法三:计算每个产品的销售总额

SELECT product_id,p.name,(@H_502_69@sum(s.units) * p.price) AS sales FROM products p LEFT JOIN sales s USING (product_id) GROUP BY product_id,p.price;

GROUP BY HAVING 语法

SELECT select_list FROM ... [WHERE ...] GROUP BY ... HAVING boolean_expression

示例一:

=> SELECT x,sum(y) FROM test1 GROUP BY x HAVING sum(y) > 3;
 x | sum ---+-----
 a | 4
 b | 5
(2 rows)

=> SELECT x,sum(y) FROM test1 GROUP BY x HAVING x < 'c';
 x | sum ---+-----
 a | 4
 b | 5
(2 rows)

示例二(高端用法):

SELECT product_id,(@H_502_69@sum(s.units) * (p.price - p.cost)) AS profit FROM products p LEFT JOIN sales s USING (product_id) WHERE s.date > CURRENT_DATE - INTERVAL '4 weeks' GROUP BY product_id,p.price,p.cost HAVING @H_502_69@sum(p.price * s.units) > 5000;

在上面的示例中,子句在不分组的列中选择行(在最后的四周中,表达式是唯一的表达式),而有子句限制了总销售额超过5000的组的输出。请注意,在查询的所有部分中,聚合表达式不一定需要是相同的。 如果一个查询包含聚合函数调用,但没有子句的组,分组仍然发生:结果是一个单一组行(或可能没有行,如果单行被消除)。如果它包含有一个有子句,即使没有任何聚合函数调用或组。

猜你在找的Postgre SQL相关文章