SQL

HeChuangJun约 1228 字大约 4 分钟

t_grade成绩表

name	kemu	fenshu
张三	语文	81
张三	数学	72
李四	语文	76
李四	数学	90

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

分组及其组内比较

查出每个学科低于平均分/60的学生(结果表头为：姓名、学科、分数、学科平均分)
查出平均分临时表再连接原表

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

都大于问题

查出每门课都大于80分的学生姓名
查出某条件下都大于都小于的数据（not in）

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

行转列

姓名	语文	数学
张三	81	72

先按某个条件分组通过max/sum(case when then)或者sum/max(IF())行转列，但列数有限制

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

列转行

将上面的结果变回例子的样子

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;

求出第二(n)高/低的薪水
第2高：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 字段,(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

找出每个部门获得前三高工资的所有员工

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

获取Employee表中第二高薪水（Salary），返回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