MySQL/ MariaDB 分组查询实战 如何查询每组最后一条、第一条记录,单多字段查询、分组筛选、最大小值排序

A kitten
蒋川
B 端产品经理,卡拉云联合创始人
阅读时长 1 分钟

MySQL 分组查询实战 如何查询每组最后一条、第一条记录,单多字段查询、分组筛选、最大小值排序

在工作中,MySQL 分组查询的使用场景太高频了。特别是分组查询中 SQL 语句之间的灵活组合运用,更是必会技能。比如在股票交易中,我们要查询多只股票收盘价格,这个价格不一定是全天最高或最低价(分组查询最后一条记录),看起来简单,但要考虑到查询效率,就不容易了。

本教程从实际案例出发,详细讲解分组查询最常用的所有情况。本文也是我在工作中,总结的实践经验,非常实用,建议你收藏备用。

1. 创建 MySQL 分组查询教程示例数据

(1)我们先创建一个示例数据库,方便后文演示。请拿起你的键盘 Ctrl+CV 一起操作。

CREATE DATABASE kalacloud_demo;

USE kalacloud_demo

CREATE TABLE `kalacloud_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
	`stock_code` varchar(16) NOT NULL,
  `stock_name` varchar(16) NOT NULL,
  `price` decimal(10,2) NOT NULL default 0,
  `traders` varchar(16),
  PRIMARY KEY (`id`)
);

本教程使用「股票交易员全天交易记录」作为应用示例讲解分组查询,你日常会碰到的分组查询场景,都在这里了。

(2)然后再给这个「股票交易记录」表里添加测试数据,交易员全天交易记录。

INSERT INTO `kalacloud_table` (`stock_code`,`stock_name`,`price`,`traders`) VALUES ('BILI','哔哩哔哩',178.73,'宋阿美');
INSERT INTO `kalacloud_table` (`stock_code`,`stock_name`,`price`,`traders`) VALUES ('9988','阿里巴巴',219.99,'宋阿美');
INSERT INTO `kalacloud_table` (`stock_code`,`stock_name`,`price`,`traders`) VALUES ('9988','阿里巴巴',233.11,'谢国庆');
INSERT INTO `kalacloud_table` (`stock_code`,`stock_name`,`price`,`traders`) VALUES ('0700','腾讯控股',213.23,'宋阿美');
INSERT INTO `kalacloud_table` (`stock_code`,`stock_name`,`price`,`traders`) VALUES ('1810','小米集团',97.12,'吕小果');
INSERT INTO `kalacloud_table` (`stock_code`,`stock_name`,`price`,`traders`) VALUES ('0700','腾讯控股',205.31,'谢国庆');
INSERT INTO `kalacloud_table` (`stock_code`,`stock_name`,`price`,`traders`) VALUES ('9988','阿里巴巴',215.11,'谢国庆');
INSERT INTO `kalacloud_table` (`stock_code`,`stock_name`,`price`,`traders`) VALUES ('0700','腾讯控股',233.23,'宋阿美');
INSERT INTO `kalacloud_table` (`stock_code`,`stock_name`,`price`,`traders`) VALUES ('BILI','哔哩哔哩',177.77,'吕小果');
INSERT INTO `kalacloud_table` (`stock_code`,`stock_name`,`price`,`traders`) VALUES ('1810','小米集团',99.10,'谢国庆');
INSERT INTO `kalacloud_table` (`stock_code`,`stock_name`,`price`,`traders`) VALUES ('BILI','哔哩哔哩',169.32,'宋阿美');
INSERT INTO `kalacloud_table` (`stock_code`,`stock_name`,`price`,`traders`) VALUES ('9988','阿里巴巴',212.56,'吕小果');
INSERT INTO `kalacloud_table` (`stock_code`,`stock_name`,`price`,`traders`) VALUES ('1810','小米集团',93.21,'吕小果');
INSERT INTO `kalacloud_table` (`stock_code`,`stock_name`,`price`,`traders`) VALUES ('1810','小米集团',91.21,'吕小果');
INSERT INTO `kalacloud_table` (`stock_code`,`stock_name`,`price`,`traders`) VALUES ('BILI','哔哩哔哩',173.72,'宋阿美');

(3)「股票交易记录」示例:股票代码、股票名、交易价格(含开盘价和收盘价)、交易员。

「股票交易记录」示例

在你的 MySQL 服务器里准备好数据,然后跟着我的教程一起实践,咱们开始吧。

特别提示:如果你还没有配置好自己的 MySQL 服务器,可看我之前写的教程

2. 查询各股收盘价(查询每组中「最后一条」记录)

方法一:左连接

SELECT m1.id,m1.stock_code 股票代码,m1.stock_name 股票名,m1.price 收盘价,m1.traders 交易员
FROM kalacloud_table m1 LEFT JOIN kalacloud_table m2
 ON (m1.stock_name = m2.stock_name AND m1.id < m2.id)
WHERE m2.id IS NULL;

方法二:窗口函数(MySQL 8.0 新特性)

WITH ranked_kalacloud_table AS (
  SELECT m.*, ROW_NUMBER() OVER (PARTITION BY stock_name ORDER BY id DESC) AS rn
  FROM kalacloud_table AS m
)
SELECT id,stock_code 股票代码,stock_name 股票名,price 收盘价,traders 交易员
FROM ranked_kalacloud_table 
WHERE rn = 1 
ORDER BY id ASC;

方法三:子查询法

SELECT id,stock_code 股票代码,stock_name 股票名,price 收盘价,traders 交易员 
FROM kalacloud_table WHERE id in
(SELECT max(id) FROM kalacloud_table GROUP BY stock_name);

以上三种方法的返回值是一致的,均为下表:

查询每组中「最后一条」记录

3. 查询各股开盘价(查询每组中「第一条」记录)

方法一:左连接法

SELECT m1.id,m1.stock_code 股票代码,m1.stock_name 股票名,m1.price 收盘价,m1.traders 交易员
FROM kalacloud_table m1 LEFT JOIN kalacloud_table m2
 ON (m1.stock_name = m2.stock_name AND m1.id > m2.id)
WHERE m2.id IS NULL;

方法二:窗口函数法(MySQL 8.0 新特性)

WITH ranked_kalacloud_table AS (
  SELECT m.*, ROW_NUMBER() OVER (PARTITION BY stock_name ORDER BY id ASC) AS rn
  FROM kalacloud_table AS m
)
SELECT id,stock_code 股票代码,stock_name 股票名,price 收盘价,traders 交易员
FROM ranked_kalacloud_table 
WHERE rn = 1 
ORDER BY id ASC;

方法三:子查询法

SELECT id,stock_code 股票代码,stock_name 股票名,price 收盘价,traders 交易员 
FROM kalacloud_table WHERE id in
(SELECT min(id) FROM kalacloud_table GROUP BY stock_name);

执行返回值:

查询每组中「第一条」记录

4. 查询每股全天交易次数(单字段分组查询)

SELECT 
	stock_name 股票名, COUNT(id) 全天交易次数
FROM
	kalacloud_table
GROUP BY stock_name;

执行返回值:

单字段分组查询

5. 查询每个交易员,每股的交易次数(多字段分组查询)

SELECT 
	stock_name 股票名 ,traders 交易员, COUNT(id) 交易次数
FROM
	kalacloud_table
GROUP BY traders,stock_name ;

返回值:

多字段分组查询

6. 查询单个交易员,每股的交易次数(分组前筛选)

SELECT 
	stock_name 股票名, COUNT(id) 交易次数
FROM
	kalacloud_table
WHERE
	traders = '宋阿美'
GROUP BY stock_name;

返回值:

分组前筛选

7. 查询单个交易员,交易次数大于 2 的股票(分组后筛选)

SELECT
	stock_name 股票名, COUNT(id) 交易次数
FROM
	kalacloud_table
WHERE
	traders = '宋阿美'
GROUP BY stock_name
HAVING count(id) >= 2;

分组后筛选

特别提示:WHERE 和 HAVING 的区别

  • WHERE:在分组前对记录进行筛选。

  • HAVING:在分组查询完成后的结果里,再进行筛选,然后返回 SQL 查询结果。

观察上面两个示例,我们可以得知,HAVING 的查询操作是在上一条 SQL 查询完成后,在完成的结果上再进行 HAVING 筛选,即两级查询。

8. 获取每只股票当日最低价并排序(分组查询最小值并排序)

SELECT
	stock_name 股票名, min(price) 最低点买入,traders 交易员
FROM
	kalacloud_table
GROUP BY stock_name
ORDER BY min(price) asc;

分组查询最小值并排序

我们看到短线交易员宋阿美和吕小果,最能踩在股票最低价买入。

9.分组查询语句大合集示例

最后,我们来写一组包含所有分组查询基础语句的大合集,复习一下。

SELECT [选择的列名]
FROM [表名]
WHERE [查询条件]
GROUP BY [分组表达式]
HAVING [分组过滤条件]
ORDER BY [排序条件]
LIMIT [行数];

特别提示:分组查询语句之间,有明确的上下顺序规则,其实也很好理解,从上到下,就是计算机运算数据的必要顺序。调换顺序逻辑上也不通。我们来一起看一个应用实例。

查询交易员「宋阿美」交易次数最多的一只股票

SELECT
  traders 交易员,stock_name 股票名, COUNT(id) 交易次数
FROM
  kalacloud_table
WHERE
  traders = '宋阿美'
GROUP BY stock_name
HAVING count(id)>=2
ORDER BY COUNT(id) DESC
LIMIT 1;

返回值:

查询交易员「宋阿美」交易次数最多的一只股票

总结 - 如何快速搭建分组查询数据看板

分组查询在实际工作中非常常用,属于需要精通级别的技能。通常分组查询用于各类数据汇总统计和查询。

有时候我们写的分组查询需要周期性的复用,每次复制粘贴代码跑一遍很繁琐。这时,你可以使用卡拉云,只要你会写 MySQL ,就能使用卡拉云快速搭建自己的数据工具。

快速搭建股票分组查询数据看板

仅需拖拽组件,即可快速生成,上文的代码复制粘贴到 SQL 语句里,直接跑出结果。

注意看红框部分:如果你想 filter 股票代码,卡拉云也帮你写好,直接拖拽搜索组件,即可完成搜索。

卡拉云是新一代低代码开发工具,免安装部署,可一键接入包括 MySQL 在内的常见数据库及 API。可根据自己的工作流,定制开发。无需繁琐的前端开发,只需要简单拖拽,即可快速搭建企业内部工具。数月的开发工作量,使用卡拉云后可缩减至数天,欢迎免费试用卡拉云

卡拉云可一键接入常见的数据库及 API

卡拉云可一键接入常见的数据库及 API

不用会前端,直接写 SQL 即可快速搭建内部工具

上文中的「今日收盘价」的代码,复制粘贴进去,直接映射到表格,点击表头自动排序。

卡拉云搭建数据工具,就是如此的迅速和好用,现在就点这里 开始试用卡拉云,5分钟搭建一套属于你的数据工具。

更多数据库相关教程可访问 卡拉云 查看。如果你还有什么疑问,欢迎一起讨论。我的微信 HiJiangChuan。

卡拉云联合创始人蒋川的微信

有关 MySQL 教程,可继续拓展学习:

© 2021 卡拉云 Built with ❤️ in Beijing

京ICP备15049164号-9