MySQL中group by实战详细案例笔记

🕗 发布于 2024-11-27 19:34 mysql 笔记 数据库

案例1：统计每个部门的员工数量

表结构和数据：

department表

dept_id	dept_name
1	IT
2	HR
3	Sales

employee表

emp_id	emp_name	dept_id
1	Alice	1
2	Bob	2
3	Charlie	1
4	David	3
5	Eve	1
6	Frank	2

SQL语句：

-- 创建department表
CREATE TABLE department (
  dept_id INT PRIMARY KEY,
  dept_name VARCHAR(50)
);

-- 插入department数据
INSERT INTO department VALUES (1, 'IT'), (2, 'HR'), (3, 'Sales');

-- 创建employee表
CREATE TABLE employee (
  emp_id INT PRIMARY KEY,
  emp_name VARCHAR(50),
  dept_id INT,
  FOREIGN KEY (dept_id) REFERENCES department(dept_id)
);

-- 插入employee数据
INSERT INTO employee VALUES 
(1, 'Alice', 1), (2, 'Bob', 2), (3, 'Charlie', 1), 
(4, 'David', 3), (5, 'Eve', 1), (6, 'Frank', 2);

-- 查询每个部门的员工数量
-- 选择部门名称
SELECT d.dept_name, 
        -- 计算每个部门的员工数量，使用COUNT函数对员工ID进行计数
        COUNT(e.emp_id) AS emp_count 
FROM department d
        -- 左连接employee表，以department表的dept_id与employee表的dept_id为连接条件
LEFT JOIN employee e ON d.dept_id = e.dept_id
        -- 按部门名称分组，以便计算每个部门的员工数量
GROUP BY d.dept_name;

查询结果：

dept_name	emp_count
IT	3
HR	2
Sales	1

案例2：计算每个产品的总销售额

表结构和数据：

product表

product_id	product_name	price
1	Laptop	999.99
2	Phone	599.99
3	Tablet	349.99

sales表

sale_id	product_id	quantity	sale_date
1	1	2	2023-10-01
2	2	5	2023-10-02
3	1	1	2023-10-03

SQL语句：

-- 创建product表
CREATE TABLE product (
  product_id INT PRIMARY KEY,
  product_name VARCHAR(100),
  price DECIMAL(10, 2)
);

-- 插入product数据
INSERT INTO product VALUES 
(1, 'Laptop', 999.99), (2, 'Phone', 599.99), (3, 'Tablet', 349.99);

-- 创建sales表
CREATE TABLE sales (
  sale_id INT PRIMARY KEY,
  product_id INT,
  quantity INT,
  sale_date DATE,
  FOREIGN KEY (product_id) REFERENCES product(product_id)
);

-- 插入sales数据
INSERT INTO sales VALUES 
(1, 1, 2, '2023-10-01'), (2, 2, 5, '2023-10-02'), (3, 1, 1, '2023-10-03');

-- 查询每个产品的总销售额

SELECT 
    p.product_name,  -- 选择产品名称
    COALESCE(SUM(s.quantity * p.price), 0.00) AS total_sales  -- 计算每个产品的总销售额，如果没有销售记录则返回0.00
FROM 
    product p  -- 从产品表中选择数据
LEFT JOIN  -- 使用 LEFT JOIN 保证即使产品没有销售记录，仍然显示在结果中
    sales s ON p.product_id = s.product_id  -- 连接销售表，通过产品 ID 进行匹配
GROUP BY 
    p.product_name;  -- 按产品名称分组，计算每个产品的总销售额

– 查询结果

product_name	total_sales
Laptop	2999.97
Phone	2999.95
Tablet	0.00

案例3：按月份统计销售额

表结构和数据：

(使用上述的product和sales表)

SQL语句：

-- 按月份统计销售额
SELECT 
    DATE_FORMAT(s.sale_date, '%Y-%m') AS month,       -- 使用 DATE_FORMAT 格式化销售日期，提取年月部分作为月份
    SUM(s.quantity * p.price) AS monthly_sales         -- 计算每月的总销售额，销售数量 * 产品价格的总和
FROM 
    product p                                          -- 从产品表 (product) 中选择数据
JOIN 
    sales s ON p.product_id = s.product_id             -- 将产品表与销售表 (sales) 连接，基于产品 ID 匹配
GROUP BY 
    DATE_FORMAT(s.sale_date, '%Y-%m')                  -- 按照年月分组销售数据
ORDER BY 
    month;                                            -- 按月份升序排列结果

– 查询结果

month	monthly_sales
2023-10	5999.92

在这里插入图片描述

案例4：统计每种订单状态的数量

表结构和数据：

orders表

order_id	customer_id	order_status	order_date
1	1	Pending	2023-10-01
2	2	Shipped	2023-10-02
3	3	Delivered	2023-10-03
4	1	Cancelled	2023-10-04

SQL语句：

-- 创建orders表
CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  order_status VARCHAR(20),
  order_date DATE
);

-- 插入orders数据
INSERT INTO orders VALUES 
(1, 1, 'Pending', '2023-10-01'), (2, 2, 'Shipped', '2023-10-02'),
(3, 3, 'Delivered', '2023-10-03'), (4, 1, 'Cancelled', '2023-10-04');

-- 查询每种订单状态的数量
SELECT order_status, COUNT(*) AS count
FROM orders
GROUP BY order_status;

– 查询结果

order_status	count
Pending	1
Shipped	1
Delivered	1
Cancelled	1

案例5：找出每个客户的总消费额

表结构和数据：

orders表

order_id	customer_id	order_status	order_date
1	1	Pending	2023-10-01
2	2	Shipped	2023-10-02
3	3	Delivered	2023-10-03

order_details表

order_id	product_id	quantity	price
1	1	1	999.99
2	2	2	599.99
3	3	3	349.99

SQL语句：

-- 创建order_details表
CREATE TABLE order_details (
  order_id INT,
  product_id INT,
  quantity INT,
  price DECIMAL(10, 2),
  PRIMARY KEY (order_id, product_id),
  FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

-- 插入order_details数据
INSERT INTO order_details VALUES 
(1, 1, 1, 999.99), (2, 2, 2, 599.99), (3, 3, 3, 349.99);

-- 查询每个客户的总消费额
SELECT o.customer_id, SUM(od.quantity * od.price) AS total_amount
FROM orders o
JOIN order_details od ON o.order_id = od.order_id
GROUP BY o.customer_id;

– 查询结果

customer_id	total_amount
1	999.99
2	1199.98
3	1049.97

案例6：统计每位员工的平均销售额

表结构和数据：

employee表

emp_id	emp_name	dept_id
1	Alice	1
2	Bob	2
3	Charlie	1
4	David	3

product表

product_id	product_name	price
1	Laptop	999.99
2	Phone	599.99

sales表

sale_id	emp_id	product_id	quantity	sale_date
1	1	1	2	2023-10-01
2	2	2	5	2023-10-02
3	1	1	1	2023-10-03


### 1. 创建 `employee` 表和插入数据


-- 创建 employee 表
CREATE TABLE employee (
  emp_id INT PRIMARY KEY,        -- 员工 ID
  emp_name VARCHAR(100),         -- 员工姓名
  dept_id INT                    -- 部门 ID
);

-- 插入 employee 表数据
INSERT INTO employee VALUES 
(1, 'Alice', 1),
(2, 'Bob', 2),
(3, 'Charlie', 1),
(4, 'David', 3);


### 2. 创建 `sales` 表和插入数据


-- 创建 sales 表
CREATE TABLE sales (
  sale_id INT PRIMARY KEY,       -- 销售 ID
  emp_id INT,                    -- 员工 ID
  product_id INT,                -- 产品 ID
  quantity INT,                  -- 销售数量
  sale_date DATE,                -- 销售日期
  FOREIGN KEY (emp_id) REFERENCES employee(emp_id),  -- 外键，关联到 employee 表
  FOREIGN KEY (product_id) REFERENCES product(product_id) -- 外键，关联到 product 表
);

-- 插入 sales 表数据
INSERT INTO sales VALUES 
(1, 1, 1, 2, '2023-10-01'),
(2, 2, 2, 5, '2023-10-02'),
(3, 1, 1, 1, '2023-10-03');


### 3. 创建 `product` 表和插入数据


-- 创建 product 表
CREATE TABLE product (
  product_id INT PRIMARY KEY,      -- 产品 ID
  product_name VARCHAR(100),       -- 产品名称
  price DECIMAL(10, 2)             -- 产品价格
);

-- 插入 product 表数据
INSERT INTO product VALUES 
(1, 'Laptop', 999.99),
(2, 'Phone', 599.99);

SQL语句：

-- 查询每位员工的平均销售额
-- 从employee表中选择员工姓名
SELECT 
    e.emp_name,
    -- 计算每个员工的平均销售额，并将其四舍五入到小数点后两位
    ROUND(AVG(s.quantity * p.price), 2) AS avg_sale_amount
FROM 
    employee e
-- 使用LEFT JOIN与sales表连接，确保每个员工即使没有销售记录也会出现在结果中
LEFT JOIN 
    sales s ON e.emp_id = s.emp_id
-- 使用LEFT JOIN与product表连接，以获取产品的价格信息
LEFT JOIN 
    product p ON s.product_id = p.product_id
-- 根据员工姓名分组，以计算每个员工的平均销售额
GROUP BY 
    e.emp_name;

– 查询结果

emp_name	avg_sale_amount
Alice	1499.99
Bob	2999.95
Charlie	NULL
David	NULL

案例7：找出每个客户的最近一次购买日期和产品

表结构和数据：

customers表

customer_id	customer_name
1	John
2	Jane
3	Bob

product表

product_id	product_name	price
1	Laptop	999.99
2	Phone	599.99
3	Tablet	999.99

orders表

order_id	customer_id	order_date	product_id
1	1	2023-10-01	1
2	2	2023-10-02	2
3	3	2023-10-03	1
4	1	2023-10-05	3

SQL语句：

-- 创建 customers 表
CREATE TABLE customers (
  customer_id INT PRIMARY KEY,     -- 客户 ID
  customer_name VARCHAR(100)       -- 客户姓名
);

-- 插入 customers 表数据
INSERT INTO customers (customer_id, customer_name) VALUES 
(1, 'John'),
(2, 'Jane'),
(3, 'Bob');


-- 创建 product 表
CREATE TABLE product (
  product_id INT PRIMARY KEY,      -- 产品 ID
  product_name VARCHAR(100),       -- 产品名称
  price DECIMAL(10, 2)             -- 产品价格
);

-- 插入 product 表数据
INSERT INTO product VALUES 
(1, 'Laptop', 999.99),
(2, 'Phone', 599.99),
(3, 'Tablet', 999.99);

-- 创建 orders 表
CREATE TABLE orders (
  order_id INT PRIMARY KEY,        -- 订单 ID
  customer_id INT,                 -- 客户 ID
  order_date DATE,                 -- 订单日期
  product_id INT,                  -- 产品 ID
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)  -- 外键，关联到 customers 表
);

-- 插入 orders 表数据
INSERT INTO orders (order_id, customer_id, order_date, product_id) VALUES 
(1, 1, '2023-10-01', 1),
(2, 2, '2023-10-02', 2),
(3, 3, '2023-10-03', 1),
(4, 1, '2023-10-05', 3);






-- 查询每个客户的最近一次购买日期和产品
SELECT c.customer_name, o.order_date, p.product_name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
LEFT JOIN product p ON o.product_id = p.product_id
WHERE (c.customer_id, o.order_date) IN (
  SELECT customer_id, MAX(order_date)
  FROM orders
  GROUP BY customer_id
);

##注释

SELECT
c.customer_name, – 选择客户姓名
o.order_date, – 选择最近一次购买的订单日期
p.product_name – 选择最近一次购买的产品名称 FROM
customers c – 从 customers 表中选择数据，c 为别名 LEFT JOIN
orders o ON c.customer_id = o.customer_id – 连接 orders 表，使用 LEFT JOIN 保证即使客户没有订单也会显示 LEFT JOIN
product p ON o.product_id = p.product_id – 连接 product 表，使用 LEFT JOIN 保证即使订单没有对应的产品也会显示 WHERE
(c.customer_id, o.order_date) IN ( – 过滤条件，确保只选取每个客户的最新订单
SELECT customer_id, MAX(order_date) – 子查询：获取每个客户的最新订单日期
FROM orders – 从 orders 表中查询
GROUP BY customer_id – 按客户 ID 分组，确保每个客户只有一个最新订单
);

– 查询结果

customer_name	order_date	product_name
John	2023-10-05	Tablet
Jane	2023-10-02	Phone
Bob	2023-10-03	Laptop

##用 GROUP BY的注意事项：

GROUP BY 列必须与SELECT中的非聚合列匹配

注：在SELECT语句中使用的非聚合列必须出现在GROUP BY子句中，否则MySQL可能会报错（取决于SQL模式配置）。

示例：

-- 假设有一个sales表，包含product_id, sale_date, quantity列

-- 正确用法
SELECT product_id, SUM(quantity) AS total_quantity
FROM sales
GROUP BY product_id;

-- 错误用法（在非严格模式下可能运行，但在严格模式下会报错）
SELECT product_id, sale_date, SUM(quantity) AS total_quantity
FROM sales
GROUP BY product_id;

聚合函数的使用

注：GROUP BY常与聚合函数一起使用，以对分组数据进行汇总计算。

示例：

SELECT product_id, 
       COUNT(*) AS total_sales,
       SUM(quantity) AS total_quantity,
       AVG(quantity) AS average_quantity
FROM sales
GROUP BY product_id;

性能考虑

注：未索引的GROUP BY列可能会导致全表扫描，降低查询性能。

示例：

-- 未索引的列可能会导致性能问题
SELECT category, COUNT(*) 
FROM products
GROUP BY category;

-- 解决方案：在category列上创建索引
CREATE INDEX idx_category ON products(category);

隐式排序

注：MySQL默认对GROUP BY的结果进行排序。

示例：

-- 默认情况下，按category排序
SELECT category, COUNT(*) 
FROM products
GROUP BY category;

-- 如果不需要排序，使用 ORDER BY NULL 来禁用隐式排序
SELECT category, COUNT(*) 
FROM products
GROUP BY category
ORDER BY NULL;

HAVING vs. WHERE

注：WHERE用于分组前过滤数据，而HAVING用于过滤分组后的结果。

示例：

-- 使用WHERE来过滤在分组之前的数据
SELECT category, COUNT(*) 
FROM products
WHERE price > 100
GROUP BY category;

-- 使用HAVING来过滤分组后的结果
SELECT category, COUNT(*) 
FROM products
GROUP BY category
HAVING COUNT(*) > 10;

NULL值处理

注：GROUP BY会将NULL值视为一个独立的组。

示例：

SELECT category, COUNT(*) 
FROM products
WHERE category IS NULL OR category = ''
GROUP BY category;
-- 这里，NULL和空字符串会被视为同一组

MySQL的ONLY_FULL_GROUP_BY模式

注：在ONLY_FULL_GROUP_BY模式下，SELECT中的所有非聚合列必须在GROUP BY中出现。

示例：

-- 在ONLY_FULL_GROUP_BY模式下，这样的查询会报错
SELECT category, sale_date, COUNT(*) 
FROM products
GROUP BY category;
-- 需要修改为：
SELECT category, MAX(sale_date), COUNT(*) 
FROM products
GROUP BY category;

分组的顺序

注：分组的顺序会影响结果的显示顺序，使用ORDER BY来指定排序。

示例：

-- 默认排序
SELECT category, COUNT(*) 
FROM products
GROUP BY category;

-- 自定义排序
SELECT category, COUNT(*) 
FROM products
GROUP BY category
ORDER BY category DESC;

子查询和GROUP BY

注：子查询的结果会影响主查询中的GROUP BY操作。

示例：

SELECT product_id, (SELECT COUNT(*) FROM sales WHERE sales.product_id = products.product_id) AS sales_count
FROM products
GROUP BY product_id;

GROUP BY 与窗口函数

注：窗口函数可以提供更复杂的数据分析功能，有时可以替代GROUP BY。

示例：

-- 使用GROUP BY
SELECT product_id, SUM(quantity) OVER (PARTITION BY product_id) AS total_quantity
FROM sales;

-- 使用窗口函数
SELECT product_id, quantity, SUM(quantity) OVER (PARTITION BY product_id) AS total_quantity
FROM sales;

限制分组结果

注：使用LIMIT可能会导致结果不完整或不准确。

示例：

-- 这可能会返回不完整的结果
SELECT category, COUNT(*) 
FROM products
GROUP BY category
LIMIT 5;

SQL注入风险

注：确保对用户输入进行适当的转义和验证，防止SQL注入攻击。

示例：

-- 危险的做法
SELECT category, COUNT(*) 
FROM products
WHERE category = '$_GET['category']'
GROUP BY category;

-- 安全的做法：使用预处理语句和参数化查询
$stmt = $pdo->prepare("SELECT category, COUNT(*) FROM products WHERE category = :category GROUP BY category");
$stmt->execute([':category' => $_GET['category']]);

原文地址：https://blog.csdn.net/weixin_47139678/article/details/144057087

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

上一篇：自研芯片逾十年，亚马逊云科技Graviton系列芯片全面成熟
下一篇：WonderJourney 学习笔记

如何还原 HTTP 请求日志中的 URL 编码参数？详解 %40 到 @
%40 是 URL 编码后的 @ 符号，说明请求体中的参数被编码后记录下来了。这是因为在 HTTP 请求中，某些特殊字符（如 @, &, =）会被 URL 编码以确保安全性。但是看着不直观，如
阅读更多2024-11-27
JavaScript核心语法（5）
这篇文章讲一下ES6中的核心语法：扩展运算符和模块化。
阅读更多2024-11-27
医疗数据质量安全，数据安全解决方案，医院关心的数据安全问题，信息安全方案（Word原件）
3.4.2网络可信接入3.4.3应用身份识别3.4.4抵御SQL注入3.4.5虚拟补丁防护3.4.6阻断漏洞攻击
阅读更多2024-11-27
ssm_mysql_医院院内物资管理系统
在当今的中国改革开放经济体制下，中国经济正以快速稳健的步伐前行。并且随着经济的发展，各领域的信息化管理也得到了充足的发展，而且愈发普及。现如今，几乎所有的行业中都有计算机的身影，甚至在其业务中占据着较
阅读更多2024-11-27
Day3 洛谷Day3 1161+1179+1200+1304
洛谷零基础刷题Day3 1161+1179+1200+1304
阅读更多2024-11-27
C# WinForm怎么使用COM组件
用Visual Studio的自动代码补全功能，然后这里显示两个方法，每个都试试，然后就知道是谁了。然后在引用管理器的 COM 分页下选择自己要添加的COM组件，当然前提是你已经用。命令注册过了此
阅读更多2024-11-27
外网访问多人协作 OnlyOffice 文档服务器
OnlyOffice 文档服务器是一款强大的在线办公套件，这款文档服务器包含了文档、电子表格和演示文稿的查看器和编辑器，能够支持实时协作编辑和多人协作。
阅读更多2024-11-27
C# 创建快捷方式文件和硬链接文件
平常我们最常window桌面上点击的左下角带小箭头的文件就是快捷方式了，大家都很熟悉它。快捷方式是Windows提供的一种快速启动程序、打开文件或文件夹的方法。它是应用程序或文件夹、文件的快速链接。快
阅读更多2024-11-27
11.26 深度学习-激活函数
神经元死亡：由于$$ReLU$$在$$x≤0$$时输出为$$0$$，如果某个神经元输入值是负，那么该神经元将永远不再激活，成为“死亡”神经元。# 数值不稳定性：在计算过程中，如果$$z_i$$的数值过
阅读更多2024-11-27
LeetCode数组题
1.数组有序，且最大值出现在数组的两端，可以考虑用双指针法，这里是重点考虑结果集也需要一个指针k指向结果集终止位置，不能依靠fast指针进行填充，因为当出现slow指针数平方大于fast时，fast位
阅读更多2024-11-27

MySQL中group by实战详细案例笔记

相关文章