java面试-sql

---

t_grade成绩表

name	kemu	fenshu
张三	语文	81
张三	数学	72
李四	语文	76
李四	数学	90
王五	语文	81
王五	数学	100
…	…	…

case when使用

• 分数等级：每门课成绩的等级划分为三个，分数小于80为及格，大于等于80低于90分为中等；大于等于90分为优秀，结果如下

name	kemu	fenshu
张三	语文	中等
张三	数学	合格
李四	语文	合格
李四	数学	优秀
王五	语文	中等
王五	数学	优秀

select name,kemu,case when fenshu>=90 then  '优秀' when  fenshu<90 and fenshu>=80 then '中等'when  fenshu<80 then '合格' end  as fenshu from t_grade

分组及其组内比较

• 如：查出每个学科低于其平均分的学生等。首先查询出每条记录对应的分组函数值select * from table t1 left join (select 分组函数(统计字段) as 比较字段 from table group by 分组字段) t2 on 唯一条件 where 比较条件
• 用一条sql语句查询出每个学科低于其平均分的学生/平均分低于60的学生(查询结果表头名为：姓名、学科、分数、学科平均分)

Select 
	a.name,a.kemu,a.fenshu,(Select avg(b.fenshu) From score b where a.kemu=b.kemu GROUP BY b.kemu )
From 
	score a Where a.fenshu<(Select avg(b.fenshu) From score b where a.kemu=b.kemu GROUP BY b.kemu ) 
GROUP BY 
	a.kemu

SELECT 
	a.name,a.kemu,a.fenshu,b.aa
FROM 
	test a,(SELECT kemu,AVG(fenshu) aa FROM test GROUP BY kemu) b
WHERE a.kemu=b.kemu AND a.fenshu<b.aa

都大于问题

• 查出某条件下都大于都小于的数据（not in）
• 用一条sql语句查询出每门课都大于80分的学生姓名

SELECT 
	name,kemu,fenshu 
from 
	t_grade 
where name not in(select name from t_grade where fenshu<=80)

行转列

• 如：把表中特定行的数据去重后做为列名;将同一个人的科目显示出来
• 利用max(case when then)或者sum(case when then)或者sum(IF()) 或者max(IF())：先将数据按某个条件分组，然后通过sum或者max将行转列，但是问题是，行转列有限制
• 用一条sql语句写出如下的查询结果

姓名	语文	数学
张三	81	72
李四	76	90
王五	81	100

SELECT 
	a.name '分数',a.fenshu '语文',b.fenshu '数学'
FROM 
	(SELECT NAME,fenshu FROM test WHERE kemu='语文') a,
	(SELECT NAME,fenshu FROM test WHERE kemu='数学') b
WHERE 
	a.name=b.name;

SELECT
	name as 姓名,
	MAX(
		CASE 
		WHEN  kemu='语文' THEN
			fenshu
		END
	) AS 语文,
	MAX(
		CASE 
		WHEN kemu='数学' THEN
			fenshu
		END
	) AS 数学 
FROM
	t_grade
GROUP BY name

列转行

• 如：把表中特定列做为每一行数据对应行的值（union）
• 将上面的结果变回例子的样子

select name, '语文' kemu , 语文 as SCORE from t_grade
union select name, '数学' kemu, 数学 as SCORE from t_grade
union select name, '英语' kemu, 英语 as SCORE from t_grade
order by name,kemu;

top-n问题

• 如：求出第二(n)高/低的薪水

• 如果是找第二高之类的问题：select max(distinct 字段) from table where 字段< (select max(distinct 字段) from table group by 分组字段) where 条件

• 如果是找第n高的问题：select distinct 字段 FROM table t1 where (SELECT count(distinct 字段) from table t2 WHERE t2.字段 > t1.字段) = n-1

• 如果是找前n高的问题：select distinct 字段 from table t1 where (select count(distinct 字段) from table t2 where t2.字段 > t1.字段)< n

• 如果是找当前记录处于第几的问题：select 字段,(select count(distinct 字段) from table t2 where t2.字段 >= t1.字段) from Scores t1 order by 字段 DESC

• Employee 表包含所有员工信息，每个员工有其对应的工号 Id，姓名 Name，工资 Salary 和部门编号 DepartmentId

Id	Name	Salary	DepartmentId
1	Joe	85000	1
2	Henry	80000	2
3	Sam	60000	2
4	Max	90000	1
5	Janet	69000	1
6	Randy	85000	1
7	Will	70000	1

• Department 表包含公司所有部门的信息。

Id	Name
1	IT
2	Sales

• 编写一个 SQL 查询，找出每个部门获得前三高工资的所有员工

|Department|Employee|Salary|
|:-😐:-😐:-😐:-😐
|IT|Max|90000|
|IT|Randy|85000|
|IT|Joe|85000|
|IT|Will|70000|
|Sales|Henry|80000|
|Sales|Sam| 60000|

SELECT
	d.NAME AS Department,
	e.NAME AS Employee,
	e.Salary AS Salary 
FROM
	employee e
	LEFT JOIN Department d ON d.id = e.DepartmentId 
WHERE
	e.id IN (
	SELECT
		e1.id 
	FROM
		Employee e1
		LEFT JOIN Employee e2 ON e2.DepartmentId = e1.DepartmentId 
		AND e2.Salary > e1.Salary 
	GROUP BY
		e1.Id 
	HAVING
		count( DISTINCT e2.Salary ) <= 2 --选取top几
	) 
and e.DepartmentId in (select Id from Department) 补全数据，不用补全的话不用加上这句
ORDER BY
	d.Id ASC,
	e.Salary DESC

• 编写一个 SQL 查询，获取 Employee 表中第二高的薪水（Salary）
• 例如上述 Employee 表，SQL查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水，那么查询应返回 null

删除重复数据

-- 删除多余的重复记录(多个字段)，只保留最小id的记录
DELETE FROM student WHERE id IN (
    SELECT * FROM (
        SELECT id FROM student WHERE (stuno,stuname) -- 注意：此处一定要加括号，当成联合字段来处理
        IN (
            -- 查找学号和姓名均重复的学生信息
            SELECT stuno,stuname FROM student GROUP BY stuno,stuname HAVING COUNT(1) > 1
        ) AND id NOT IN (
            -- 查询最小id的记录
            SELECT MIN(id) FROM student GROUP BY stuno,stuname HAVING COUNT(1) > 1
        )
    ) AS stu_repeat_copy

);

查找每个学科分数低于60的人数

CREATE TABLE `xueke`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tid` int(11) NULL DEFAULT NULL,
  `sid` int(11) NULL DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `score` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of xueke
-- ----------------------------
INSERT INTO `xueke` VALUES (1, 1, 1, '数学', 80);
INSERT INTO `xueke` VALUES (2, 1, 2, '数学', 50);
INSERT INTO `xueke` VALUES (3, 2, 3, '英语', 61);
INSERT INTO `xueke` VALUES (4, 2, 4, '英语', 59);
INSERT INTO `xueke` VALUES (5, 3, 5, '语文', 62);
INSERT INTO `xueke` VALUES (6, 3, 6, '语文', 58);
INSERT INTO `xueke` VALUES (7, 1, 7, '数学', 81);

select name,count(sid) from xueke where score < 60 GROUP BY name;

去除最高值与最低值的平均问题

SELECT
	t.cid,
	round(AVG(t.saleMoney),2) '平均值'
FROM
(
SELECT
	a.date,
	a.cid,
	a.saleMoney,
	RANK() over(partition by a.cid order by a.saleMoney) as rank1,
	RANK() over(partition by a.cid order by a.saleMoney desc) as rank2
FROM
	city_sale a
GROUP BY a.date,a.cid
)t
WHERE t.rank1>1 
and t.rank2>1
GROUP BY t.cid

lead、lag函数平移列（求记录之间的差值最大值或者最小值）

select 
	t2.uid,
	count(1),
	min(t2.minute2)
from
(
select *,
	TIMESTAMPDIFF(MINUTE,t.crtime,t.rank1) minute2
from 
(
SELECT
	a.uid,
	a.crtime,
	lead(a.crtime,1) over(PARTITION BY a.uid ORDER BY a.crtime) as rank1
FROM
	user_sale a
)t
)t2
where t2.minute2>0
GROUP BY t2.uid
ORDER BY count(1) desc

中位数

## 单个cid的中位数

SELECT
	avg(t.saleMoney)
FROM
(
SELECT
	a.cid,
	a.saleMoney,
	row_number() over(ORDER BY a.saleMoney desc) rank1,
	row_number() over(ORDER BY a.saleMoney) rank2
FROM
	city_sale a
where a.cid=10240
)t
where  t.rank1=t.rank2+1 or t.rank1=t.rank2-1 or rank1=rank2

## 分组求中位数

SELECT
	t.cid,
	avg(t.saleMoney)
FROM
(
SELECT
	a.cid,
	a.saleMoney,
	row_number() over(PARTITION by a.cid ORDER BY a.saleMoney desc) rank1,
	row_number() over(PARTITION by a.cid ORDER BY a.saleMoney) rank2
FROM
	city_sale a
)t
where  t.rank1=t.rank2+1 or t.rank1=t.rank2-1 or rank1=rank2

GROUP BY t.cid

---

总结

本文介绍了的使用，如有问题欢迎私信和评论