SQL查询语句（三）—— 分组数据

一、GROUP BY 分组

SELECT 
    c1, c2,..., cn, aggregate_function(ci)
FROM
    table
WHERE
    where_conditions
GROUP BY c1 , c2,...,cn;

语句执行顺序

将订单状态的值分组

SELECT 
    status
FROM
    orders
GROUP BY status;

与聚合函数一起使用

每种状态下的订单数量

SELECT 
    status, COUNT(*)
FROM
    orders
GROUP BY status;

所有订单的总金额

SELECT 
    status, 
    SUM(quantityOrdered * priceEach) AS amount
FROM
    orders
INNER JOIN orderdetails 
    USING (orderNumber)
GROUP BY 
    status;

查询返回订单号和每个订单的总金额

SELECT 
    orderNumber,
    SUM(quantityOrdered * priceEach) AS total
FROM
    orderdetails
GROUP BY 
    orderNumber;

查询获取每年的总销售额

SELECT 
    YEAR(orderDate) AS year,
    SUM(quantityOrdered * priceEach) AS total
FROM
    orders
INNER JOIN orderdetails 
    USING (orderNumber)
WHERE
    status = 'Shipped'
GROUP BY 
    YEAR(orderDate);

过滤由GROUP BY子句返回的组，使用having

SELECT 
    YEAR(orderDate) AS year,
    SUM(quantityOrdered * priceEach) AS total
FROM
    orders
INNER JOIN orderdetails 
    USING (orderNumber)
WHERE
    status = 'Shipped'
GROUP BY 
    year
HAVING 
    year > 2003;

标准SQL不允许您在GROUP BY子句中使用别名，但是MySQL支持这一点

SELECT 
    YEAR(orderDate) AS year, 
    COUNT(orderNumber)
FROM
    orders
GROUP BY 
    year;

MySQL还允许您按升序或降序对组进行排序，而标准SQL则不允许

SELECT 
    status, 
    COUNT(*)
FROM
    orders
GROUP BY 
    status DESC;

二、HAVING

用于为一组行或集合指定过滤条件

SELECT 
    select_list
FROM 
    table_name
WHERE 
    search_condition
GROUP BY 
    group_by_expression
HAVING 
    group_condition;

语句执行顺序

获取订单号，每个订单售出的商品数以及每个商品的总销售额

SELECT 
    ordernumber,
    SUM(quantityOrdered) AS itemsCount,
    SUM(priceeach*quantityOrdered) AS total
FROM
    orderdetails
GROUP BY ordernumber;

订单的总销售额大于1000

SELECT 
    ordernumber,
    SUM(quantityOrdered) AS itemsCount,
    SUM(priceeach*quantityOrdered) AS total
FROM
    orderdetails
GROUP BY 
   ordernumber
HAVING 
   total > 1000;

HAVING使用OR 或者 AND

总金额大于1000且包含多个600项目的订单：

SELECT 
    ordernumber,
    SUM(quantityOrdered) AS itemsCount,
    SUM(priceeach*quantityOrdered) AS total
FROM
    orderdetails
GROUP BY ordernumber
HAVING 
    total > 1000 AND 
    itemsCount > 600;

参考资料

basic-mysql-tutorial