SQL分组和聚合(Grouping and Aggregates)

发布时间:2017-2-21 11:03:37 编辑:www.fx114.net 分享查询网我要评论
本篇文章主要介绍了"SQL分组和聚合(Grouping and Aggregates) ",主要涉及到SQL分组和聚合(Grouping and Aggregates) 方面的内容,对于SQL分组和聚合(Grouping and Aggregates) 感兴趣的同学可以参考一下。

这章应该是难点,也是成为SQL高手的必经之路。

注意有GROUP 语句时,WHERE和HAVING的场合。

前者用于检索前的条件过滤 。

后者用于检索出来结果之后的条件过滤。

==========

SELECT open_emp_id , COUNT(*) AS how_many FROM account GROUP BY open_emp_id HAVING open_emp_id > 10;
SELECT product_cd, MAX(avail_balance) max_balance, MIN(avail_balance) min_balance, AVG(avail_balance) avg_balance, SUM(avail_balance) tot_balance, COUNT(*) num_accounts FROM account GROUP BY product_cd ;
SELECT COUNT(DISTINCT open_emp_id);
SELECT MAX(pending_balance - avail_balance) max_uncleared FROM account;
SELECT COUNT(*) num_rows, COUNT(val) num_vals, SUM(val) total, MAX(val) max_val, AVG(val) avg_val FROM number_tbl;
SELECT product_cd, open_branch_id, SUM(avail_balance) total_balance FROM account GROUP BY product_cd, open_branch_id;
SELECT EXTRACT(YEAR FROM start_date) year, COUNT(*) how_many FROM employee GROUP BY EXTRACT(YEAR FROM start_date);
SELECT product_cd, open_branch_id, SUM(avail_balance) tot_balance FROM account GROUP BY product_cd, open_branch_id WITH ROLLUP;
SELECT product_cd, SUM(avail_balance) prod_balance FROM account WHERE status = 'ACTIVE' GROUP BY product_cd HAVING MIN(avail_balance) >= 1000 AND MAX(avail_balance) <= 10000;

上一篇:docker 私有镜像管理工具harbor 安装
下一篇:使用 Object.create 创建对象,super 关键字,class 关键字

相关文章

相关评论