无锡网站建设设计公司,网站百度,夜蝶直播app,建立网站目录结构的意义10-分组数据
两个新的select子句#xff1a;group by子句和having子句。
10.1-数据分组
上面我们学到了#xff0c;使用SQL中的聚集函数可以汇总数据#xff0c;这样#xff0c;我们就能够对行进行计数#xff0c;计算和#xff0c;计算平均数。
目前为止#xff0c…10-分组数据
两个新的select子句group by子句和having子句。
10.1-数据分组
上面我们学到了使用SQL中的聚集函数可以汇总数据这样我们就能够对行进行计数计算和计算平均数。
目前为止所有的计算都是在表的所有数据或者匹配特定的where子句的数据上进行的。
select count(*) as num_prods
from products
where vend_id DLL01;
如上述SQL语句返回供应商为DLL01的所有产品数目。
但是现在有一个功能就是想要返回每个供应商的产品数目或者返回只提供一种商品的供应商数目。
这个时候就需要用到这次要写的分组的内容了。
使用分组可以将数据分为多个逻辑组对每个组进行聚集计算。 10.2-创建分组
分组是使用select语句和group by子句进行创建的。
select vend_id,count(*) as num_prods
from products
group by vend_id; 上述SQL语句执行后会分别查出来供应商为BRS01的产品数目供应商为DLL01的产品数目供应商为FNG01的产品数目。
group by子句指示DBMS按照vend_id排序并分组数据。这么做就会对每个不同的vend_id进行分别查询。
因为使用了group by子句就不必指定要计算和估值的每个组了系统会自动完成。group by子句提示DBMS对数据按照vend_id进行分组然后对每个组而不是整个结果集进行聚集。根据上述的SQL语句DBMS按照我们的指示分为三组然后每组进行分别计算。
使用group by之前需要知道下面内容 gruop by子句可以包含任意数目的列因而可以对分组进行嵌套更细致地进行分组。 如果group by 子句中嵌套了分组数据将在最后指定的分组上进行汇总。在建立分组时所有列都一起计算所以不能从个别列取数据。 group by 子句中累出的每一列都必须是检索列或者有效的表达式但是不能为聚集函数。如果在select中使用表达式则必须在gruop by子句中指定相同的表达式不能使用别名。 大多数SQL实现不允许group by 列带有长度可变的数据类型如文本字段备注型字段。 除聚集计算语句外select语句中的每一列都必须在group by子句中给出。 如果分组列中包含具有NULL值的行则NULL将作为一个分组返回。如果列中有多行NULL值他们将分为一组。 group by子句必须出现在where子句之后order by子句之前。 10.3-过滤分组
除了使用group by分组数据外SQL还能过滤分组可以规定包括哪些分组排除哪些分组。例如我们查找至少有两个订单的顾客商品数量大于100的供应商等。必须基于完整的分组而不是个别的行进行过滤。
看到过滤就想起了where子句但是过滤分组这个功能可能不能使用where因为where过滤的是某些不符合条件的行而不是组。
SQL提供的另一个子句having子句是专门用来过滤分组的。having类似于where。
目前为止所有where子句都可以使用having进行替换。只不过having用户过滤组where用于过滤行。
having支持所有where操作符where子句的条件包括通配符条件和带多个操作符的子句学过的这些有关where的所有技术和选项都使用having。句法是相同的只是关键字不同而已。
select cust_id,count(*) as orders
from orders
group by cust_id
having count(*) 2; 上述SQL语句通过分组查询订单量大于等于2的顾客id和订单数量。可以看到我们是通过having过滤组数据的。
having和where的差别where在数据分组前进行过滤having在数据分组后进行过滤。经过where过滤后的数据就不包含在组中了。
select vend_id,count(*) as num_prods
from products
where prod_price 4
group by vend_id
having count(*) 2; 上述SQL语句用于查询产品列表中某个供应商供应的产品数量大于等于2并且产品价格大于等于4的供应商的数量。
第一行正常使用selec子句使用聚集函数count(*)。第三行过滤产品价格大于等于4的行第四行按照vend_id进行分组然后第五行过滤计数大于等于2的组。
如果没有where子句会怎么样呢
select vend_id,count(*) as num_prods
from products
group by vend_id
having count(*) 2; 上述SQL语句除去了where子句可以看到数据比上面多了一条。
关于使用where和having如果没有group by子句大多数DBMS会同等对待这两个子句。但是实际开发过程中应该知道只用having时后面要跟group by子句。 10.4-分组和排序
group by 和 order by 对于第一条区别有时我们使用group by大部分情况是按照分组顺序进行排序的但并不是总是这样。如果想要指定输出的数据为某种指定的排序那么还是要指定order by子句即使它的效果等同于group by子句。
select order_num,count(*) as items
from orderitems
group by order_num
having count(*) 3; 但是如果我们按照订购物品的数目进行排序输出。
select order_num,count(*) as items
from orderitems
group by order_num
having count(*) 3
order by items,order_num; 上述SQL语句按照order_num分组数据查询符合大于等于3的数据然后按照数量进行排序。 10.5-select子句顺序 练习 OrderItems表包含每个订单的每个产品。编写SQL语句返回每个订单号order_num各有多少行数order_lines并 按order_lines对结果进行排序。 select order_num,count(*) as order_lines
from orderitems
group by order_num
order by order_lines; 编写SQL语句返回名为cheapest_item的字段该字段包含每个供应商成本最低的产品使用Products表中的prod_price 然后从最低成本到最高成本对结果进行排序。 select vend_id,min(prod_price) as cheapest_item
from products
group by vend_id
order by cheapest_item; 确定最佳顾客非常重要请编写SQL语句返回至少含100项的所有订单的订单号OrderItems表中的order_num。 select order_num,count(*) as orders
from orderitems
where quantity 100
group by order_num; 确定最佳顾客的另一种方式是看他们花了多少钱。编写SQL语句返回总价至少为1000的所有订单的订单号OrderItems表中的order_num。提示需要计算总和item_price乘以quantity。按订单号对结果进行排序。 select order_num
from orderitems
where (item_price * quantity) 1000
group by order_num; 下面的SQL语句有问题吗尝试在不运行的情况下指出。 SELECT order_num, COUNT(*) AS items
FROM OrderItems
GROUP BY items
HAVING COUNT(*) 3
ORDER BY items, order_num; group by 子句应当时候表中的字段名而不是别名正确group by order_num;
