SQL数据库关键字之groupby小结.docx

SQL数据库关键字之group by 小结1、解释：Group By语句从英文的字面意义上理解就是“根据(by)一定的规则进行分组(Group)”。它的作用是根据给定数据列的每个成员对查询结果进行分组统计，最终得到一个分组汇总表2、常见用法： 创建客户信息库（customers）create table customers( customersid int identity(1,1) not null, name nvarchar(50), city nvarchar(50), customerstype nvarchar(50), addtime date, adddepartment nvarchar(10), quantity int)插入数据语句insert into customers(name,city,customerstype,addtime,adddepartment,quantity) values(张三,中国,普通客户,2010-10-23,财务部,3) insert into customers(name,city,customerstype,addtime,adddepartment,quantity) values(李四,法国,主要客户,2012-11-1,销售部,2) insert into customers(name,city,customerstype,addtime,adddepartment,quantity) values(王五,中国,普通客户,2011-1-12,编辑部,12) insert into customers(name,city,customerstype,addtime,adddepartment,quantity) values(王六,德国,特殊客户,2011-1-12,编辑部,5) insert into customers(name,city,customerstype,addtime,adddepartment,quantity) values(马七,中国,主要客户,2012-2-29,财务部,3) insert into customers(name,city,customerstype,addtime,adddepartment,quantity) values(赵八,德国,特殊客户,2010-4-23,财务部,6) insert into customers(name,city,customerstype,addtime,adddepartment,quantity) values(钱九,美国,特殊客户,2011-6-16,编辑部,2) insert into customers(name,city,customerstype,addtime,adddepartment,quantity) values(孙十,中国,主要客户,2012-12-2,销售部,7) insert into customers(name,city,customerstype,addtime,adddepartment,quantity) values(张四,法国,特殊客户,2010-11-2,编辑部,2) insert into customers(name,city,customerstype,addtime,adddepartment,quantity) values(张五,中国,普通客户,2012-11-23,销售部,1)表查询结果如下customersid name city customerstype addtime adddepartment quantity1 张三 中国 普通客户 2010-10-23 财务部 32 李四 法国 主要客户 2012-11-01 销售部 23 王五 中国 普通客户 2011-01-12 编辑部 124 王六 德国 特殊客户 2011-01-12 编辑部 55 马七 中国 主要客户 2012-02-29 财务部 36 赵八 德国 特殊客户 2010-04-23 财务部 67 钱九 美国 特殊客户 2011-06-16 编辑部 28 孙十 中国 主要客户 2012-12-02 销售部 79 张四 法国 特殊客户 2010-11-02 编辑部 210 张五 中国 普通客户 2012-11-23 销售部 12.1 group by 这个是 group by 最常见的用法 ，group by 分组的字段1,分组字段2. 例如：对customers 根据 customerstype 进行分组语句： select customerstype from customers group by customerstype 运行结果如下 customerstype 普通客户 特殊客户 主要客户解释：1、SELECT子句中的列名必须为分组列或列函数 2、列函数对于GROUP BY子句定义的每个组各返回一个结果”，根据customerstype分组，对每个类型返回一个结果2.2 group by 常和 sum，max，min ，count 等聚合函数一起使用例如：对 customers根据 customerstype 进行分组 统计每个类别中的客户个语句： select customerstype,COUNT(*) from customers group by customerstype 运行结果如下 customerstype number 普通客户 3 特殊客户 4 主要客户 3例如：对 customers根据 customerstype 进行分组 获取每组的最大customersid语句： select customerstype,MAX(customersid) as number from customers group by customerstype 运行结果如下 customerstype number 普通客户 10 特殊客户 9 主要客户 82.3 group by 字句和where 字句一起使用 在SQL中where字句的运行顺序是先于 group by 字句的，where 字句会会在形成组和计算列函数之前消除不符合条件的行例如：查询由财务部门添加的用户各个类型的最大customersid语句： select customerstype,MAX(customersid) as number from customers where adddepartment=财务部group by customerstype 运行结果如下 customerstype number 普通客户 1 特殊客户 6 主要客户 5解释：where 字句过滤掉了不是 财务部 添加的用户信息，再对之后的结果进行 group by 操作2.4 group by 字句和having() 字句一起使用 在SQL 中 having() 字句的运行顺序是后于 group by 字句的， having() 字句的的作用是筛选满足条件的组例如：查询客户数超过1个的国家和客户数量语句： select city, count(*) number from customers GROUP by city having count(*)1 运行结果如下 city number 德国 2 法国 2 中国 5解释：系统会先对customers 根据 city 分组，生产虚拟表，之后having 生产的虚拟表进行筛选，将数量不大于1的剔除2.5 group by 字句和ROLLUP（）一起使用可方便的生成合计、小计、总计 等混合统计的报表例如语句 SELECT city,customerstype, sum(quantity) quantity FROM customers GROUP BY ROLLUP (city,customerstype) 运行结果如下 city customerstype quantity 德国 特殊客户 11 德国 NULL 11 法国 特殊客户 2 法国 主要客户 2 法国 NULL 4 美国 特殊客户 2 美国 NULL 2 中国 普通客户 16 中国 主要客户 10 中国 NULL 26 NULL NULL 43解释：ROLLUP会为 (city,customerstype)、和 (city) 值的每个唯一组合生成一个带有小计的行。还将计算一个总计行 列是按照从右到左的顺序汇总的。列的顺序会影响 ROLLUP 的输出分组，而且可能会影响结果集内的行数2.6 group by 字句和CUBE（）一起使用生成简单的 GROUP BY 聚合行、ROLLUP 超聚合行和交叉表格行。CUBE 针对表达式的所有排列输出一个分组。例如语句 SELECT city,customerstype, SUM (quantity) quantity FROM customers GROUP BY CUBE (city,customerstype) 运行结果如下 city customerstype quantity 中国 普通客户 16 NULL 普通客户 16 德国 特殊客户 11 法国 特殊客户 2 美国 特殊客户 2 NULL 特殊客户 15 法国 主要客户 2 中国 主要客户 10 NULL 主要客户 12 NULL NULL 43 德国 NULL 11 法国 NULL 4 美国 NULL 2 中国 NULL 26解释：CUBE会为 (city,customerstype)、(city) 和 (customerstype) 值的每个唯一组合生成一个带有小计的行，还会生成一个总计行。 CUBE中列的顺序不影响 CUBE 的输出。2.7 group by allgroup by all 需和 where 一起使用，否则all 不起作用，查询结果中包含又 group by 字句产生的所有分组，即使这些组没有符合 where 字句的条件 ，这些没有符合条件的结果会以 null 显示例如： select city,SUM(quantity) quantity from customers where quantity5 group by all city 运行的结果为 city quantity 德国 6 法国 NULL美国 NULL中国 19 解释：其中 法国和美国 quantity 的和值不符合 where 条件，分别为 4 和2，单仍然在查询的结果中显示 以上语句，将all 去掉后，运行的结果如下 德国 6 中国 19