企业级Mysql实战

Mysql企业级sql编写实战

  • 1 一对多，列表展示最新记录字段
    • 1.1 场景
    • 1.2 需求
    • 1.3 实现
      • 1.3.1 表及数据准备
      • 1.3.2 Sql编写
  • 2 区间统计（if/case when）
    • 2.1 场景
    • 2.2 需求
    • 2.3 实现
      • 2.2.1 表及数据准备
      • 2.3.2 sql编写
  • 3 多类别分组统计（竖表转横表）
    • 3.1 场景
    • 3.2 需求
    • 3.3 实现
      • 3.3.1 表及数据准备
      • 3.3.2 Sql编写
  • 4 占比统计（多层子查询）
    • 4.1 场景
    • 4.2 需求
    • 4.3 实现
      • 4.3.1 表及数据准备
      • 4.3.2 sql编写

1 一对多，列表展示最新记录字段

1.1 场景

在一对多的数据关联下，列表页面展示获取字表中的最新记录的指定字段，比如：

  1. 人员最近登录记录
  2. 订单的最新支付状态
  3. 产品的最新库存更新记录
    这些场景都需要在一对多关系的主表中，展示子表中满足特定条件（通常是时间戳最新）的记录字段。

1.2 需求

假设我们有两张表：sys_user（用户表）和sys_login_log（用户登录记录表）。sys_user表中的每个用户可能在sys_login_log表中有多条登录记录。我们的需求是在用户列表页面展示每个用户的最近一次登录时间、登录IP等信息。

1.3 实现

1.3.1 表及数据准备

-- 创建 sys_user表
CREATE TABLE `sys_user` (`id` bigint(10) NOT NULL COMMENT '主键',`user_name` varchar(200) DEFAULT NULL COMMENT '用户名',`dept_id` bigint(10) DEFAULT NULL COMMENT '所属部门',PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;-- 创建 sys_login_log表
CREATE TABLE `sys_login_log` (`id` bigint(20) NOT NULL COMMENT '主键',`user_id` bigint(20) DEFAULT NULL COMMENT '所属用户',`dept_id` bigint(20) DEFAULT NULL COMMENT '所属部门',`login_time` datetime DEFAULT NULL COMMENT '登录时间',`login_ip` varchar(20) DEFAULT NULL COMMENT '登录ip',PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;-- 插入一些示例数据到 sys_user表
INSERT INTO `sys_user` (`id`, `user_name`, `dept_id`) VALUES (1832959318869716994, '用户2', 1833094812337737730);
INSERT INTO `sys_user` (`id`, `user_name`, `dept_id`) VALUES (1832959390013501441, '用户3', 1839196308802420737);
INSERT INTO `sys_user` (`id`, `user_name`, `dept_id`) VALUES (1832959495936454657, '用户4', 1839196494123548673);-- 插入一些示例数据到 sys_login_log表
INSERT INTO `sys_login_log` (`id`, `user_id`, `dept_id`, `login_time`, `login_ip`) VALUES (1, 1832959318869716994, 1833094812337737730, '2025-02-07 22:39:58', '192.168.1.1');
INSERT INTO `sys_login_log` (`id`, `user_id`, `dept_id`, `login_time`, `login_ip`) VALUES (2, 1832959390013501441, 1839196308802420737, '2025-02-07 22:38:20', '192.168.1.2');
INSERT INTO `sys_login_log` (`id`, `user_id`, `dept_id`, `login_time`, `login_ip`) VALUES (3, 1832959495936454657, 1839196494123548673, '2025-02-07 22:40:02', '192.168.1.3');
INSERT INTO `sys_login_log` (`id`, `user_id`, `dept_id`, `login_time`, `login_ip`) VALUES (4, 1832959318869716994, 1833094812337737730, '2025-02-07 23:01:04', '192.168.1.2');
-- 从数据分析来看，用户【1832959318869716994】对应的最近登录时间与ip为 2025-02-07 23:01:04 192.168.1.2

原始数据：
在这里插入图片描述

1.3.2 Sql编写

  1. 如果只需要查询最近的登录时间（子表的其中一个字段的值），使用max函数或者子查询查询即可
-- max 函数实现
SELECT usr.*,max(log.login_time) '最近登录时间'
from sys_user usr
LEFT JOIN sys_login_log log on log.user_id = usr.id
GROUP BY usr.id-- 子查询方式实现
SELECT usr.*,(select login_time from sys_login_log WHERE user_id = usr.id ORDER BY login_time desc LIMIT 1) '最近登录时间'
from sys_user usr
GROUP BY usr.id

执行效果如下
在这里插入图片描述

  1. 如果需要字表中的多个字段，则只有通过子查询进行查询
-- 方式一 通过获取id进行关联查询（推荐使用）
SELECT usr.*,log.login_time,log.login_ip
from sys_user usr
LEFT join sys_login_log log on log.id = (SELECT id from sys_login_log WHERE user_id = usr.id ORDER BY login_time desc LIMIT 1)-- 方式二，先通过子查询分组，获取匹配字段，然后根据该字段关联进行查询
SELECT usr.*,log.login_time,log.login_ip
FROM sys_user usr
left JOIN (SELECT user_id,  MAX(login_time) AS latest_login_timeFROM sys_login_logGROUP BY user_id
)latest_logins ON usr.id = latest_logins.user_id
left JOIN sys_login_log log ON latest_logins.user_id = log.user_id AND latest_logins.latest_login_time = log.login_time

查询效果如下，由查询结果可看出，login_time 和 login_ip 是我们想要获取的数据
在这里插入图片描述

2 区间统计（if/case when）

2.1 场景

在进行数据统计过程中，需要按区间范围进行统计，比如：

  1. 年龄区间的人员数（10-20、20-30，30以上）,表头：年龄区间、人员数量
  2. 金额区间的商品数（0-1万、1-2万、2万以上），表头：金额区间、商品数量

2.2 需求

针对系统用户 ，需要按年龄段分别统计各年龄段的人数占比情况

2.3 实现

2.2.1 表及数据准备

-- 创建表结构
CREATE TABLE `tb_person` (`id` bigint(10) NOT NULL COMMENT '主键',`user_name` varchar(200) DEFAULT NULL COMMENT '用户名',`age` int(10) DEFAULT NULL COMMENT '年龄',PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;-- 初始化测试数据
INSERT INTO `tb_person` (`id`, `user_name`, `age`) VALUES (1, '人员1', 10);
INSERT INTO `tb_person` (`id`, `user_name`, `age`) VALUES (2, '人员2', 15);
INSERT INTO `tb_person` (`id`, `user_name`, `age`) VALUES (3, '人员3', 33);
INSERT INTO `tb_person` (`id`, `user_name`, `age`) VALUES (4, '人员4', 23);
INSERT INTO `tb_person` (`id`, `user_name`, `age`) VALUES (5, '人员5', 18);
INSERT INTO `tb_person` (`id`, `user_name`, `age`) VALUES (6, '人员6', 50);
INSERT INTO `tb_person` (`id`, `user_name`, `age`) VALUES (7, '人员7', 29);
INSERT INTO `tb_person` (`id`, `user_name`, `age`) VALUES (8, '人员8', 36);
INSERT INTO `tb_person` (`id`, `user_name`, `age`) VALUES (9, '人员9', 46);

原始数据：
在这里插入图片描述

2.3.2 sql编写

-- 1、 使用if 实现
SELECT if(age <= 20, '20岁及以下',if(age BETWEEN 21 and 30,'21-30岁','30岁以上')) '年龄段',count(*) '人数' 
from tb_person
GROUP BY if(age <= 20, '20岁及以下',if(age BETWEEN 21 and 30,'21-30岁','30岁以上'))-- 2、使用case  when  实现
SELECT CASE WHEN age <= 20 THEN '20岁及以下'WHEN age BETWEEN 21 AND 30 THEN '21-30岁'ELSE '30岁以上'END AS '年龄段',COUNT(*) AS '人数'
FROM  tb_person
GROUP BY CASE WHEN age <= 20 THEN '20岁及以下'WHEN age BETWEEN 21 AND 30 THEN '21-30岁'ELSE '30岁以上'END;

实现效果如下：
在这里插入图片描述

3 多类别分组统计（竖表转横表）

3.1 场景

数据报表统计过程中，需要按多类别进行组合分组进行统计，比如：

  1. 商城需要按商品类型统计每月销售额情况，表头：类别、1月、2月…
    这种报表通常需要将原本按行存储的数据（竖表）转换为按列展示的数据（横表），以便更直观地查看和分析数据。

3.2 需求

创建一个表来存储订单数据，包括商品类型、订单时间和订单月份。
编写SQL查询，将订单数据按商品类型分组，根据年份筛选统计每月的订单金额

3.3 实现

3.3.1 表及数据准备

-- 订单表创建
CREATE TABLE `tb_order` (`id` bigint(10) NOT NULL COMMENT '主键',`cate` varchar(20) DEFAULT NULL COMMENT '分类',`order_date` datetime DEFAULT NULL COMMENT '订单日期',`order_amount` decimal(10,2) DEFAULT NULL COMMENT '订单金额',  -- 金额建议用decimal进行存储PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;-- 初始化数据
INSERT INTO `tb_order` (`id`, `cate`, `order_date`, `order_amount`) VALUES (1, '服装', '2024-01-08 14:23:10', 1000.00);
INSERT INTO `tb_order` (`id`, `cate`, `order_date`, `order_amount`) VALUES (2, '手机', '2024-02-08 14:23:10', 1000.00);
INSERT INTO `tb_order` (`id`, `cate`, `order_date`, `order_amount`) VALUES (3, '平板', '2024-02-08 14:23:10', 2000.00);
INSERT INTO `tb_order` (`id`, `cate`, `order_date`, `order_amount`) VALUES (4, '电脑', '2024-03-08 14:23:10', 4000.00);

原始数据：
在这里插入图片描述

3.3.2 Sql编写

SELECT cate,
sum(if(MONTH(order_date) =1,order_amount,0)) '1月',
sum(if(MONTH(order_date) =2,order_amount,0)) '2月',
sum(if(MONTH(order_date) =3,order_amount,0)) '3月'
from tb_order ord
WHERE year(order_date) =2024 -- 查询指定年份
GROUP BY cate;

实现效果如下，根据结果可看出，统计出每个分类下每月的销售额汇总数据
在这里插入图片描述

4 占比统计（多层子查询）

4.1 场景

数据报表统计过程中，需要统计某一分类的占比情况，比如：

  1. 成绩管理系统中，按学科分组，统计该学科总人数、及格人数、及格率

4.2 需求

创建一个学生考试成绩表，包含所属学生、学科、成绩字段，以60分为及格线，统计及格人数和及格率，按学科分组，统计该学科总人数、及格人数、及格率

4.3 实现

4.3.1 表及数据准备

-- 成绩表创建
CREATE TABLE `tb_score` (`id` bigint(10) NOT NULL COMMENT '主键',`stu_id` bigint(10) DEFAULT NULL COMMENT '学生id',`subject` varchar(50) DEFAULT NULL COMMENT '学科',`score` int(2) DEFAULT NULL COMMENT '成绩',PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 初始化数据
INSERT INTO `tb_score` (`id`, `stu_id`, `subject`, `score`) VALUES (1, 1, '语文', 50);
INSERT INTO `tb_score` (`id`, `stu_id`, `subject`, `score`) VALUES (2, 2, '语文', 70);
INSERT INTO `tb_score` (`id`, `stu_id`, `subject`, `score`) VALUES (3, 3, '语文', 80);
INSERT INTO `tb_score` (`id`, `stu_id`, `subject`, `score`) VALUES (4, 1, '数学', 48);
INSERT INTO `tb_score` (`id`, `stu_id`, `subject`, `score`) VALUES (5, 2, '数学', 20);
INSERT INTO `tb_score` (`id`, `stu_id`, `subject`, `score`) VALUES (6, 3, '数学', 80);
INSERT INTO `tb_score` (`id`, `stu_id`, `subject`, `score`) VALUES (7, 1, '英语', 70);
INSERT INTO `tb_score` (`id`, `stu_id`, `subject`, `score`) VALUES (8, 2, '英语', 20);

原始数据：
在这里插入图片描述

4.3.2 sql编写

SELECT t.*,CONCAT(round(t.pass_stu / t.total_stu * 100,2),'%') '及格率'
from (-- 通过子查询，先进行初步统计SELECT subject,count(stu_id) total_stu,sum(if(score>= 60,1,0)) pass_stu from tb_scoreGROUP BY subject
)t

查询效果
在这里插入图片描述

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.rhkb.cn/news/15490.html

如若内容造成侵权/违法违规/事实不符，请联系长河编程网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

C语言基础第04天:数据的输出和输出

C语言基础第04天:数据的输出和输出

C语言基础:04天笔记 内容提要 回顾C语言数据的输入输出 回顾 运算符 算术运算符 结果:数值 - * / % (正) -(负) -- i和i 相同点:i自身都会增1 不同点:他们运算的最终结果是不同的. i先使用 ,后加1; i先计算,后使用 赋值运算符 结果:赋值后的变量的值 赋值顺序:由右…
阅读更多...
DeepSeek训练成本与技术揭秘

DeepSeek训练成本与技术揭秘

引言&#xff1a;在当今人工智能蓬勃发展的时代&#xff0c;DeepSeek 宛如一颗耀眼的新星&#xff0c;突然闯入大众视野&#xff0c;引发了全球范围内的热烈讨论。从其惊人的低成本训练模式&#xff0c;到高性能的模型表现&#xff0c;无一不让业界为之侧目。它打破了传统认知&…
阅读更多...
数组与指针1

数组与指针1

1. 数组名的理解 1.1 数组名是数组首元素的地址 int arr[10] {1,2,3,4,5,6,7,8,9,10};int *p &arr[0]; 这里我们使用 &arr[0] 的方式拿到了数组第一个元素的地址&#xff0c;但是其实数组名本来就是地址&#xff0c;而且是数组首元素的地址。如下&#xff1a; 1.2…
阅读更多...
Axure原型图怎么通过链接共享

Axure原型图怎么通过链接共享

一、进入Axure 二、点击共享 三、弹出下面弹框&#xff0c;点击发布就可以了 发布成功后&#xff0c;会展示链接&#xff0c;复制即可共享给他人 四、发布失败可能的原因 Axure未更新&#xff0c;首页菜单栏点击帮助选择Axure更新&#xff0c;完成更新重复以上步骤即可
阅读更多...
软件模拟I2C案例（寄存器实现）

软件模拟I2C案例（寄存器实现）

引言 在经过前面对I2C基础知识的理解&#xff0c;对支持I2C通讯的EEPROM芯片M24C02的简单介绍以及涉及到的时序操作做了整理。接下来&#xff0c;我们就正式进入该案例的实现环节了。本次案例是基于寄存器开发方式通过软件模拟I2C通讯协议&#xff0c;然后去实现相关的需求。 阅…
阅读更多...
脚手架开发【实战教程】prompts + fs-extra

脚手架开发【实战教程】prompts + fs-extra

创建项目 新建文件夹 mycli_demo 在文件夹 mycli_demo 内新建文件 package.json {"name": "mycli_demo","version": "1.0.0","bin": {"mycli": "index.js"},"author": "","l…
阅读更多...
【大模型】DeepSeek-V3技术报告总结

【大模型】DeepSeek-V3技术报告总结

系列综述&#xff1a; &#x1f49e;目的&#xff1a;本系列是个人整理为了学习DeepSeek相关知识的&#xff0c;整理期间苛求每个知识点&#xff0c;平衡理解简易度与深入程度。 &#x1f970;来源&#xff1a;材料主要源于DeepSeek官方技术报告进行的&#xff0c;每个知识点的…
阅读更多...
只需三步！5分钟本地部署deep seek——MAC环境

只需三步！5分钟本地部署deep seek——MAC环境

MAC本地部署deep seek 第一步:下载Ollama第二步:下载deepseek-r1模型第三步&#xff1a;安装谷歌浏览器插件 第一步:下载Ollama 打开此网址&#xff1a;https://ollama.com/&#xff0c;点击下载即可&#xff0c;如果网络比较慢可使用文末百度网盘链接 注&#xff1a;Ollama是…
阅读更多...
力扣hot100刷题第一天

力扣hot100刷题第一天

哈希 1. 两数之和 题目 给定一个整数数组 nums 和一个整数目标值 target&#xff0c;请你在该数组中找出 和为目标值 target 的那 两个 整数&#xff0c;并返回它们的数组下标。 你可以假设每种输入只会对应一个答案&#xff0c;并且你不能使用两次相同的元素。你可以按任意…
阅读更多...
Linux（CentOS）安装 Nginx

Linux（CentOS）安装 Nginx

CentOS版本&#xff1a;CentOS 7 Nginx版本&#xff1a;1.24.0 两种安装方式&#xff1a; 一、通过 yum 安装&#xff0c;最简单&#xff0c;一键安装&#xff0c;全程无忧。 二、通过编译源码包安装&#xff0c;需具备配置相关操作。 最后附&#xff1a;设置 Nginx 服务开…
阅读更多...
项目6：基于大数据校园一卡通数据分析和可视化

项目6：基于大数据校园一卡通数据分析和可视化

1、项目简介 本项目是基于大数据的清华校园卡数据分析系统&#xff0c;通过Hadoop&#xff0c;spark等技术处理校园卡交易、卡号和商户信息数据。系统实现消费类别、男女消费差异、学院消费排行和年级对比等分析&#xff0c;并通过Web后端和可视化前端展示结果。项目运行便捷&…
阅读更多...
Django项目中创建app并快速上手（pycharm Windows）

Django项目中创建app并快速上手（pycharm Windows）

1.打开终端 我选择的是第二个 2.运行命令 python manage.py startapp 名称 例如&#xff1a; python manage.py startapp app01 回车&#xff0c;等待一下&#xff0c;出现app01的文件夹说明创建成功 3.快速上手 1.app注册 增加一行 "app01.apps.App01Config"&#…
阅读更多...
使用Docker + Ollama在Ubuntu中部署deepseek

使用Docker + Ollama在Ubuntu中部署deepseek

1、安装docker 这里建议用docker来部署&#xff0c;方便简单 安装教程需要自己找详细的&#xff0c;会用到跳过 如果你没有安装 Docker&#xff0c;可以按照以下步骤安装&#xff1a; sudo apt update sudo apt install apt-transport-https ca-certificates curl software-p…
阅读更多...
信创领域的PostgreSQL管理员认证

信创领域的PostgreSQL管理员认证

信创产业&#xff0c;全称为信息技术应用创新产业&#xff0c;是中国为应对国际技术竞争、保障信息安全、实现科技自立而重点发展的战略性新兴产业。其核心目标是通过自主研发和生态构建&#xff0c;逐步替代国外信息技术产品&#xff0c;形成自主可控的国产化信息技术体系。 发…
阅读更多...
jemalloc的malloc案例来分析GOT表和PLT表有关流程

jemalloc的malloc案例来分析GOT表和PLT表有关流程

一、背景 在之前的博客 跟踪jemalloc 5.3.0的第一次malloc的源头原因及jemalloc相关初始化细节拓展-CSDN博客 里&#xff0c;我们分析了在preload jemalloc的库之后&#xff0c;main之前的一次malloc分配&#xff08;分配72704字节&#xff09;的源头原因并做了jemalloc的初始…
阅读更多...
Centos Ollama + Deepseek-r1+Chatbox运行环境搭建

Centos Ollama + Deepseek-r1+Chatbox运行环境搭建

Centos Ollama Deepseek-r1Chatbox运行环境搭建 内容介绍下载ollama在Ollama运行DeepSeek-r1模型使用chatbox连接ollama api 内容介绍 你好&#xff01; 这篇文章简单讲述一下如何在linux环境搭建 Ollama Deepseek-r1。并在本地安装的Chatbox中进行远程调用 下载ollama 登…
阅读更多...
使用sunshine和moonlight串流时的音频输出问题

使用sunshine和moonlight串流时的音频输出问题

设备&#xff1a;电脑和平板串流&#xff0c;把平板当副屏使用 1.如果启用安装steam音频驱动程序&#xff0c;则平板有声&#xff0c;电脑无声&#xff0c;在moonlight端可以设置平板和电脑同时发声&#xff0c;但是有点卡 2.只想电脑发声&#xff0c;平板无声 禁用安装steam…
阅读更多...
微信小程序案例2——天气微信小程序（学会绑定数据）

微信小程序案例2——天气微信小程序（学会绑定数据）

文章目录 一、项目步骤1 创建一个weather项目2 进入index.wxml、index.js、index.wxss文件，清空所有内容，进入App.json，修改导航栏标题为“中国天气网”。3进入index.wxml，进行当天天气情况的界面布局，包括温度、最低温、最高温、天气情况、城市、星期、风行情况，代码如下…
阅读更多...
如何在WPS和Word/Excel中直接使用DeepSeek功能

如何在WPS和Word/Excel中直接使用DeepSeek功能

以下是将DeepSeek功能集成到WPS中的详细步骤&#xff0c;无需本地部署模型&#xff0c;直接通过官网连接使用&#xff1a;1. 下载并安装OfficeAI插件 &#xff08;1&#xff09;访问OfficeAI插件下载地址&#xff1a;OfficeAI助手 - 免费办公智能AI助手, AI写作&#xff0c;下载…
阅读更多...
数字电路-基础逻辑门实验

数字电路-基础逻辑门实验

基础逻辑门是数字电路设计的核心元件&#xff0c;它们执行的是基本的逻辑运算。通过这些基本运算&#xff0c;可以构建出更为复杂的逻辑功能。常见的基础逻辑门包括与门&#xff08;AND&#xff09;、或门&#xff08;OR&#xff09;、非门&#xff08;NOT&#xff09;、异或门…
阅读更多...
最新文章
推荐文章

Copyright @ 2022~2023