sql排序取前三_SQL基础三:聚合与排序
3-1 对表进⾏聚合查询
聚合函数定义:⽤于汇总的函数成为聚合函数或者聚集函数。聚合即将多⾏汇总为⼀⾏。
常⽤的5个函数:
COUNT:计算表中的记录数(⾏数)
例:计算全部数据的⾏数
SELECT COUNT(*) FROM Product;
cadeau
COUNT()中的星号代表全部列,括号中的输⼊值成为参数或parameter,输出值称为返回值。
想要计算NULL之外的数据的⾏数,通过将对象列设定为参数来实现。
结果根据参数的不同⽽不同,COUNT(*)会得到包含NULL的数据⾏数,⽽COUNT(<;列名>)会得到NULL之外的数据⾏数。
SUM:计算表中数值列中数据的合计值
SELECT SUM(<;列名>) FROM Product;
megapolis
语法与COUNT函数相同,但不能使⽤星号作为参数。在使⽤SUM时,NULL值是被排除在外的,即⽆论有多少个NULL值都会被忽略。AVG:计算表表中数值列中数据的平均值
SELECT AVG(<;列名>) FROM Product;
temp计算时会事先删除NULL再计算。
SUM和AVG函数只能对数值类型的列使⽤。
MAX:求出表中任意列中数据的最⼤值
MIN:求出表中任意列中数据的最⼩值
MAX和MIN函数原则上适⽤于任何类型的列,即只要能排序的数据,肯定会有最值。
SELECT MAX(<;列名>) FROM Product;
SELECT MIN(<;列名>) FROM Product;
在聚类函数的参数中使⽤DISTINCT,可以删除重复数据。
3-2 对表进⾏分组
1、GROUP BY
使⽤GROUP BY⼦句可以像切蛋糕那样将表分割。语法:
SELECT <;列名1>,<;列名2>,<;列名3>……
local是什么意思
FROM <;表名>
GROUP BY <;列名1>,<;列名2>,<;列名3>……;
例:按照商品种类统计数据⾏数
SELECT product_type, COUNT(*)
FROM product
GROUP BY product_type;
语言伙伴
GROUP BY⼦句中指定的列称为聚合键或者分组列,和SELECT⼦句⼀样,可以通过逗号分隔指定多列。
当聚合键中包含NULL时,也会将NULL作为⼀组特定的数据,在结果中会以“不确定”⾏(空⾏)的形式表现出来。
SELECT purcha_price, COUNT(*)
FROM product
GROUP BY purcha_price;
GROUP BY⼦句⼀定要写在FROM语句之后,如果有WHERE⼦句就写在WHERE⼦句之后。
使⽤WHERE⼦句时GROUP BY进⾏汇总,会先根据WHERE⼦句指定条件进⾏过滤,然后再进⾏汇总处理。
语法:
SELECT <;列名1>,<;列名2>,<;列名3>……
FROM <;表名>
WHERE <;条件表达式>
GROUP BY <;列名1>,<;列名2>,<;列名3>……;
例:
SELECT purcha_price, COUNT(*)
FROM product
WHERE product_type = '⾐服'
GROUP BY purcha_price;
执⾏顺序:FROM-WHERE-GROUP BY-SELECT
使⽤聚合函数和GROUP BY⼦句时需要注意以下4点:
只能写在SELECT⼦句中
GROUP BY⼦句中不能使⽤SELECT⼦句中列的别名
GROUP BY⼦句的聚合结果是⽆序的
WHERE ⼦句中不能使⽤聚合函数
2、GROUP BY+WITH CUBE
CUBE指定在结果集内不仅包含GROUP BY提供的⾏,还包含汇总⾏,汇总⾏在结果中显⽰为NULL,但⽤来表⽰所有值。结果集内的汇总⾏数取决于GROUP BY⼦句内包含的列数。CUBE返回每个可能的组和⼦组组合。
在mysql 5.6.17版本中,只定义了cube,但是不⽀持cube操作
SELECT product_type, COUNT(*)
FROM product
GROUP BY product_type
WITH CUBE;
报错:
3、GROUP BY+WITH ROLLUP
ROLLUP根据维度在数据结果集中进⾏的聚合操作。指定在结果集内不仅包含GROUP BY提供的⾏,
还包含汇总⾏,按层次结构顺序,从组内的最低级到最⾼级汇总组。组的层次结构取决于列分组时指定使⽤的顺序。更改分组顺序会影响在结果集内⽣成的⾏数。
化学工程与工艺考研CUBE和ROLLU均不⽀持区分聚合函数。
假设⽤户需要对N个维度进⾏聚合查询操作,普通的group by语句需要N个查询和N次group by操作。
闪闪发光英文
⽽rollup的优点是⼀次可以得出N次group by的结果,这样可以提⾼查询效率,同时⼤⼤减少⽹络的传输流量。
考研分数线2013例:
(1)单个维度:
不加WITH ROLLUP
SELECT product_type, COUNT(*)
FROM product
北京新东方英语学校地址GROUP BY product_type;
加WITH ROLLUP
SELECT product_type, COUNT(*)
FROM product
GROUP BY product_type
WITH ROLLUP;
和普通的GROUP BY差别不⼤,只是多了⼀个(null,8),表⽰对所有的product_type再做⼀次聚合,即订单数量总和。对单个唯独进⾏ROLLUP操作只是可以在最后得到聚合的数据,对⽐GROUP BY语句
并没有⾮常⼤的优势。
(2)多个维度
不加WITH ROLLUP
SELECT product_type, regist_date, COUNT(*)
FROM product
GROUP BY product_type,regist_date
加WITH ROLLUP
updating是什么意思
SELECT product_type, regist_date, COUNT(*)
FROM product
GROUP BY product_type,regist_date
WITH ROLLUP;
(null,null)表⽰最后的聚合
(product_type,null)表⽰仅对(product_type)⼀列进⾏分组的聚合结果
(product_type,regist_date)表⽰对(product_type,regist_date)两列进⾏分组的聚合结果,也就是group by本⾝聚集。
所以,上⾯结果等价于:
SELECT product_type, regist_date, COUNT(*)
FROM product
GROUP BY product_type,regist_date
UNION
SELECT product_type, NULL, COUNT(*)
FROM product
GROUP BY product_type
UNION
SELECT NULL, NULL, COUNT(*)
FROM product
注意:
ORDER BY不能在rollup中使⽤,两者为互斥关键字;
如果分组的列包含NULL值,那么rollup的结果可能不正确,因为在rollup中进⾏的分组统计时,null具有特殊意义。因此在进⾏rollup 时可以先将null转换成⼀个不可能存在的值,或者没有特别含义的值,⽐如:IFNULL(xxx,0)
mysql中没有像oracle那样的grouping()函数;
3-3 为聚合结果指定条件
使⽤COUNT函数等对表中数据进⾏汇总操作时,为其指定条件的不是WHERE⼦句,⽽是HAVING⼦句。
语法:
SELECT <;列名1>,<;列名2>,<;列名3>……
FROM <;表名>
WHERE <;条件表达式>
GROUP BY <;列名1>,<;列名2>,<;列名3>……
HAVING <;分组结果对应的条件>;
HAVING⼦句要写在GROUP BY后边
使⽤HAVING⼦句时SELECT语句的顺序:
SELECT-FROM-WHERE-GROUP BY-HAVING
例:从按照商品种类进⾏分组后的结果中,取出“包含数据⾏数为2⾏”的组
SELECT product_type, COUNT(*)
FROM product
GROUP BY product_type
HAVING COUNT(*) = 2;
对⽐不加HAVING的情况:
SELECT product_type, COUNT(*)
FROM product
GROUP BY product_type;