MySQL_查询面试题（持续更新）

最新推荐文章于 2024-05-31 10:33:21 发布

林_胖

最新推荐文章于 2024-05-31 10:33:21 发布

阅读量1.1k

点赞数 1

分类专栏：面试题 MySQL 文章标签： mysql 面试数据库 sql

本文链接：https://blog.csdn.net/Lin_Xiao_Dai/article/details/105777477

版权

这篇博客整理了一系列MySQL面试中的查询题目，包括计算月最大值、性别合计、仓库动态合计、年月累计值等。通过案例解析，展示了如何运用动态SQL解决不确定数量的仓库合计问题，以及使用窗口函数处理复杂的数据聚合和连续签到计算。同时，列转行、日期间隔等实际问题的探讨，为读者提供了丰富的SQL实战经验。

摘要由CSDN通过智能技术生成

前言

资源全部来自于网络，我会尽量在每一题上都写上注释，没什么好说的，做就完事了。

第一题计算月最大值

在这里插入图片描述

-- 建表
CREATE TABLE mianshi1 (id VARCHAR (20),dates VARCHAR (20),v_num INT) charset=utf8;

-- 插入数据
INSERT INTO mianshi1 VALUES ("A","2015-01",5),("A","2015-01",15),("B","2015-01",5),("A","2015-01",8),("B","2015-01",25),("A","2015-01",5),("A","2015-02",4),("A","2015-02",6),("B","2015-02",10),("B","2015-02",5),("A","2015-03",16),("A","2015-03",22),("B","2015-03",23),("B","2015-03",10),("B","2015-03",11);

-- 创建一个临时表，保存分组后的当月访问次数
CREATE TABLE middle
AS
SELECT id, dates, SUM(v_num) AS s
FROM mianshi1
GROUP BY id, dates
ORDER BY id;

-- 不使用窗口函数
-- 将中间表进行自链接，然后再根据b表进行分组，对分组后的a表数据进行聚合运算
SELECT a.id AS '用户', b.dates AS '月份', b.s AS '当月访问次数'
	, MAX(a.s) AS '最大访问次数'
	, SUM(a.s) AS '总访问次数'
FROM middle a
	INNER JOIN middle b
	ON a.id = b.id
		AND a.dates <= b.dates
GROUP BY b.id, b.dates;

-- 使用窗口函数	
SELECT id AS '用户', dates AS '月份', s AS '当月访问次数'
	, MAX(s) OVER (PARTITION BY id  ROWS BETWEEN unbounded PRECEDING AND CURRENT ROW) AS '最大访问次数'
	, SUM(s) OVER (PARTITION BY id  ROWS BETWEEN unbounded PRECEDING AND CURRENT ROW) AS '总访问次数'
FROM middle;

第二题计算性别合计

在这里插入图片描述
计算各院系的男女计数以及合计。

-- 建表
CREATE TABLE student1 (id VARCHAR (20),NAME VARCHAR (20),gender CHAR (1),birth VARCHAR (20),department VARCHAR (20),address VARCHAR (20)) charset=utf8;

-- 插入数据 
INSERT INTO student1 VALUES ("201901","张大佬","男","1985","计算机系","北京市海淀区"),("201902","郭大侠","男","1986","中文系","北京市昌平区"),("201903","张三","女","1990","中文系","湖南省永州市"),("201904","李四","