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

前言

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

窗口函数

-- 建表
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 \'用户\',
	b.dates \'月份\',
	b.s \'当月访问次数\',
	max( a.s ) \'最大访问次数\',
	sum( a.s ) \'总访问次数\'
FROM
	middle a
	INNER JOIN middle b ON a.id = b.id 
and
	a.dates <= b.dates 
GROUP BY
	b.id,
	b.dates;

如果上面的代码思路有点绕不过来，可以看一下展开后的group by之前的样子

使用窗口函数

-- 创建一个临时表，保存分组后的当月访问次数
CREATE TABLE middle AS SELECT
id,dates,
sum( v_num ) AS s 
FROM
	mianshi1 
GROUP BY
	id,
	dates
order by id;
-- 使用窗口函数	
SELECT
	id \'用户\',
	dates \'月份\',
	s \'当月访问次数\',
	max( s ) over ( PARTITION BY id rows BETWEEN unbounded preceding AND current ROW ) \'最大访问次数\',
	sum( s ) over ( PARTITION BY id rows BETWEEN unbounded preceding AND current ROW ) \'总访问次数\'
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\",\"李四\",\"男\",\"1990\",\"英语系\",\"辽宁市阜新市\"),(\"201905\",\"王五\",\"女\",\"1991\",\"英语系\",\"福建省厦门市\"),(\"201906\",\"王六\",\"男\",\"1988\",\"计算机系\",\"湖南省衡阳市\");
-- 子查询之后一定要给表随便起个名字，不然报错
SELECT
	department \'院系\',
	male \'男\',
	female \'女\',
	male + female \'合计\' 
FROM
	(
	SELECT
		department,
		sum( CASE gender WHEN \'男\' THEN 1 ELSE 0 END ) male,
		sum( CASE gender WHEN \'女\' THEN 1 ELSE 0 END ) female 
	FROM
		student1 
	GROUP BY
	department 
	) a;

参考文献

1. 没有窗口函数，你能很快做出这道MySQL面试题吗？
2. 看似简单的一道SQL面试题，你是否能够很快写出答案？