• MYSQL 学习笔记记录整理之二:分组数据


    1、创建分组
    GROUP BY
    SELECT ven_id,COUNT(*) AS num_prods FROM products GROUP BY ven_id;
    注意:
    GROUP BY可以包含任意数目的列
    GROUP BY列出的每个列都必须是检索列或者有效的表达式(但不能是聚集函数)。如果在SELECT语句中使用表达式,则必须在GROUP BY中指定相同的表达式,不能使用别名。
    除聚集计算语句外,SELECT语句中的每个列都必须在GROUP BY子句中给出。
    GROUP BY必须出现在WHERE语句之后,ORDER BY语句之前。
     
    2、过滤分组
    HAVING过滤分组,WHERE过滤行。
    SELECT cust_id ,COUNT(*) AS orders
    FROM orders GROUP BY cust_id
    HAVING COUNT(*) >=2;
    COUNT(*) >=2--过滤2个以上的订单分组
     
     
    例子:列出具有2个(含)以上、价格为10(含)以上的产品的供应商
    SELECT cust_id ,COUNT(*) AS num_prods
    FROM products
    WHERE prod_price>=10
    GROUP BY ven_id
    HAVING COUNT(*) >=2;
     
    3、分组和排序
    例子:检索总订单价格大于等于50的订单号和总计订单价格
    SELECT order_num,SUM(quantiy*item_price) AS ordertotal
    FROM orderitems
    GROUP BY order_num
    HAVING SUM(quantiy*item_price)>=50;
     
     
    为按总计订单价格排序输出,需要添加ORDER BY语句,如下:
    SELECT order_num,SUM(quantiy*item_price) AS ordertotal
    FROM orderitems
    GROUP BY order_num
    HAVING SUM(quantiy*item_price)>=50
    ORDER BY ordertotal;
     
  • 相关阅读:
    5-29
    5-28
    5-27
    -5-26
    5-25
    5-24
    5-21
    RabbitMQ消息中间件极速入门与实战
    细说java多线程之内存可见性
    全面解析java注解
  • 原文地址:https://www.cnblogs.com/by170628/p/7944493.html
Copyright © 2020-2023  润新知