Guadazi
0%

sql-case

Posted on Edited on In

参考 最强最全面的大数据 SQL 面试题和答案

每个学生最好成绩的科目

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE TABLE `course` (
 `student` varchar(20) DEFAULT NULL,
 `course` varchar(20) DEFAULT NULL,
 `score` int DEFAULT NULL
) ENGINE = InnoDB CHARSET = utf8mb4 COLLATE utf8mb4_0900_ai_ci;
insert into `course` values
('王五','语文','89'),
('王五','数学','136'),
('王五','理综','130'),
('王五','英语','92'),
('张三','英语','110'),
('张三','数学','110'),
('张三','语文','110'),
('张三','理综','110'),
('李四','理综','109'),
('李四','语文','120'),
('李四','数学','100'),
('李四','英语','120');
1
select * from course;
student course score
王五 语文 89
王五 数学 136
王五 理综 130
王五 英语 92
张三 英语 110
张三 数学 110
张三 语文 110
张三 理综 110
李四 理综 109
李四 语文 120
李四 数学 100
李四 英语 120

求每个学生成绩最好的科目

可能成绩最好的科目，分数一样

1
2
3
4
5
6
7
8
SELECT m.student, m.max_score, c.course
FROM (
 SELECT student, MAX(score) AS max_score
 FROM course
 GROUP BY student
) m, course c
WHERE m.student = c.student
 AND m.max_score = c.score;
student max_score course
王五 136 数学
张三 110 英语
张三 110 数学
张三 110 语文
张三 110 理综
李四 120 语文
李四 120 英语

行列转换

样例数据准备

1
2
3
4
5
6
7
8
9
create database sql_case;
-- 年份-部门-绩效表%%
CREATE TABLE `t1` (
 `fyear` int DEFAULT NULL COMMENT '年份',
 `fdept` char(2) DEFAULT NULL COMMENT '部门',
 `fscore` int DEFAULT NULL COMMENT '绩效评分'
) ENGINE = InnoDB CHARSET = utf8mb4 COLLATE utf8mb4_0900_ai_ci

insert into t1 values ('2014',  'B',  '9'), ('2015',  'A',  '8'), ('2014',  'A',  '10'), ('2015',  'B',  '7');

多行转多列

1
select * from t1
fyear fdept fscore
2014 B 9
2015 A 8
2014 A 10
2015 B 7

求每年各部门的分数

1
2
3
4
5
6
7
8
9
10
SELECT 
 fyear, 
 MAX(CASE 
  WHEN fdept = 'a' THEN fscore
 END) as fdept_a, 
 MAX(CASE 
  WHEN fdept = 'b' THEN fscore
 END) as fdept_b
FROM t1
GROUP BY fyear;
fyear fdept_a fdept_b
2014 10 9
2015 8 7

one more thing

错误写法

1
2
3
4
5
6
7
8
9
10
SELECT 
 fyear, 
 CASE 
  WHEN fdept = 'a' THEN fscore
 END as fdept_a, 
 CASE 
  WHEN fdept = 'b' THEN fscore
 END as fdept_b
FROM t1
GROUP BY fyear;

报错

1
(1055, "Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'sql_case.t1.fdept' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by")

参考 https://www.cnblogs.com/jim2016/p/6322703.html

对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中，也就是说查出来的列必须在group by后面出现否则就会报错，或者这个字段出现在聚合函数里面。

只选择出现在group by后面的列，或者给列增加聚合函数

如何关闭ONLY_FULL_GROUP_BY选项?

服务级别(重启Mysql服务):

1
2
3
set @@GLOBAL.sql_mode='';

set sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

全局配置:

在 [mysqld]和[mysql]下添加

1
sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

2. 多列转多行

问题描述：将问题一的结果转成源表，问题一结果表名为t1_2

1
2
3
4
5
6
7
8
9
10
11
create table t1_2
 SELECT 
  fyear, 
  MAX(CASE 
   WHEN fdept = 'a' THEN fscore
  END) as fdept_a, 
  MAX(CASE 
   WHEN fdept = 'b' THEN fscore
  END) as fdept_b
 FROM t1
 GROUP BY fyear;
1
select * from t1_2;
fyear fdept_a fdept_b
2014 10 9
2015 8 7

答案

1
2
3
4
5
6
7
8
9
SELECT fyear, fdept, fscore
FROM (
 SELECT fyear, 'A' AS fdept, fdept_a AS fscore
 FROM t1_2
 UNION ALL
 SELECT fyear, 'B' AS fdept, fdept_b AS fscore
 FROM t1_2
) t
-- 必须写别名，否则报错： (1248, 'Every derived table must have its own alias')
fyear fdept fscore
2014 A 10
2015 A 8
2014 B 9
2015 B 7

3. 同一部门有多个绩效情况下，多行转多列

2014年公司组织架构调整，导致部门出现多个绩效，业务及人员不同，无法合并算绩效

1
2
3
4
5
6
7
8
CREATE TABLE t1_3 LIKE t1;

INSERT INTO t1_3
VALUES ('2014', 'B', '9'),
 ('2015', 'A', '8'),
 ('2014', 'A', '10'),
 ('2015', 'B', '7'),
 ('2014', 'B', '6');
fyear fdept fscore
2014 B 9
2015 A 8
2014 A 10
2015 B 7
2014 B 6 
1
2
3
4
5
6
SELECT 
  fyear,
  fdept,
  group_concat(fscore order by fscore separator ',') as fscore
 FROM t1_3
 GROUP BY fyear,fdept;
fyear fdept fscore
2014 A 10
2014 B 9,6
2015 A 8
2015 B 7 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT 
 fyear, 
 MAX(CASE 
  WHEN fdept = 'a' THEN fscore
 END) as fdept_a, 
 MAX(CASE 
  WHEN fdept = 'b' THEN fscore
 END) as fdept_b
FROM (
SELECT 
 fyear,
 fdept,
 group_concat(fscore order by fscore separator ',') as fscore
FROM t1_3
GROUP BY fyear,fdept 
) t
GROUP BY fyear;
fyear fdept_a fdept_b
2014 10 6,9
2015 8 7

排名中取他值

1
2
3
4
5
6
7
8
create table t2 like t1;
-- init values
insert into t2 values
('2014',  'A',   '3'),
('2014',  'B',   '1'),
('2014',  'C',   '2'),
('2015',  'A',   '4'),
('2015',  'D',   '3');
1
select * from t2;
fyear fdept fscore
2014 A 3
2014 B 1
2014 C 2
2015 A 4
2015 D 3

按a分组取b字段最小时对应的c字段

1
2
3
4
5
6
7
8
9
10
select 
  fyear,
  fdept,
  fscore,
  row_number() over 
  (
    partition by fyear 
    order by fdept
  ) as rn
from t2;
fyear fdept fscore rn
2014 A 3 1
2014 B 1 2
2014 C 2 3
2015 A 4 1
2015 D 3 2 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
select 
fyear,
fscore as fscore_of_min_fdept
 from
(
  select 
    fyear,
    fdept,
    fscore,
    row_number() over 
    (
      partition by fyear 
      order by fdept
    ) as rn
  from t2
) tt where tt.rn=1;
fyear fscore_of_min_fdept
2014 3
2015 4

按a分组取b字段排第二时对应的c字段

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
select 
fyear,
fscore as fscore_of_sec_min_fdept
 from
(
  select 
    fyear,
    fdept,
    fscore,
    row_number() over 
    (
      partition by fyear 
      order by fdept
    ) as rn
  from t2
) tt where tt.rn=2;
fyear fscore_of_sec_min_fdept
2014 1
2015 3

按a分组取b字段最小和最大时对应的c字段

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select 
  fyear,
  fdept,
  fscore,
  row_number() over 
  (
    partition by fyear 
    order by fdept
  ) as rn_asc,
  row_number() over 
  (
    partition by fyear 
    order by fdept desc
  ) as rn_desc
from t2;
fyear fdept fscore rn_asc rn_desc
2014 C 2 3 1
2014 B 1 2 2
2014 A 3 1 3
2015 D 3 2 1
2015 A 4 1 2 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
select
  fyear,
  min(if(rn_asc = 1, fscore, null)) as fscore_of_min_dept,
  max(if(rn_desc = 1, fscore, null)) as fscore_of_max_dept
from
(
  select 
    fyear,
    fdept,
    fscore,
    row_number() over 
    (
      partition by fyear 
      order by fdept
    ) as rn_asc,
    row_number() over 
    (
      partition by fyear 
      order by fdept desc
    ) as rn_desc
  from t2
) tt
where tt.rn_asc = 1 or tt.rn_desc =1
group by fyear
fyear fscore_of_min_dept fscore_of_max_dept
2014 3 2
2015 4 3

按a分组取b字段第二小和第二大时对应的c字段

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
select
  fyear,
  min(if(rn_asc = 2, fscore, null)) as score_of_second_min_dept,
  max(if(rn_desc = 2, fscore, null)) as score_of_second_max_dept
from
(
  select 
    fyear,
    fdept,
    fscore,
    row_number() over 
    (
      partition by fyear 
      order by fdept
    ) as rn_asc,
    row_number() over 
    (
      partition by fyear 
      order by fdept desc
    ) as rn_desc
  from t2
) tt
where tt.rn_asc = 2 or tt.rn_desc =2
group by fyear
fyear score_of_second_min_dept score_of_second_max_dept
2014 1 1
2015 3 4

按a分组取b字段前两小和前两大时对应的c字段

需保持fdept字段最小、最大排首位

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
select
  fyear as `year`,
  group_concat(if(rn_asc <= 2, fscore, null) order by rn_asc) as score_of_less_2_min_dept,
  group_concat(if(rn_desc <= 2, fscore, null) order by rn_desc) as score_of_less_2_max_dept
from
(
  select 
    fyear,
    fdept,
    fscore,
    row_number() over 
    (
      partition by fyear 
      order by fdept
    ) as rn_asc,
    row_number() over 
    (
      partition by fyear 
      order by fdept desc
    ) as rn_desc
  from t2
) tt
where tt.rn_asc <= 2 or tt.rn_desc <=2
group by fyear
year score_of_less_2_min_dept score_of_less_2_max_dept
2014 3,1 2,1
2015 4,3 3,4

累计求值

1
2
3
4
5
6
7
8
create table t3 like t1;

insert into t3 values
('2015',  'A',   '4'),
('2014',  'A',   '3'),
('2014',  'C',   '2'),
('2014',  'B',   '1'),
('2015',  'D',   '3');
fyear fdept fscore
2015 A 4
2014 A 3
2014 C 2
2014 B 1
2015 D 3

按a分组按b字段排序，对c累计求和

1
2
3
4
5
6
7
8
9
10
select 
  fyear,
  fdept,
  fscore,
  sum(fscore) over
  (
    partition by fyear order by fdept
  ) as accu_sum_score 
from 
  t3;
fyear fdept fscore accu_sum_score
2014 A 3 3
2014 B 1 4
2014 C 2 6
2015 A 4 4
2015 D 3 7

按a分组按b字段排序，对c取累计平均值

1
2
3
4
5
6
7
8
9
10
select 
  fyear,
  fdept,
  fscore,
  avg(fscore) over
  (
    partition by fyear order by fdept
  ) as accu_avg_score 
from 
  t3;
fyear fdept fscore accu_avg_score
2014 A 3 3.0000
2014 B 1 2.0000
2014 C 2 2.0000
2015 A 4 4.0000
2015 D 3 3.5000

按a分组按b字段排序，对b取累计排名比例

1
2
3
4
5
6
7
select
  fyear as `year`,
  fdept as `dept`,
  fscore as `score`,
  round(row_number() over (partition by fyear order by fdept)/(count(fscore) over(partition by fyear)),2) as accu_avg_score
from t3
order by fyear,fdept
year dept score accu_avg_score
2014 A 3 0.33
2014 B 1 0.67
2014 C 2 1.00
2015 A 4 0.50
2015 D 3 1.00

按a分组按b字段排序，对b取累计求和比例

1
2
3
4
5
6
7
select
  fyear as `year`,
  fdept as `dept`,
  fscore as `score`,
  round(row_number() over (partition by fyear order by fdept)/(sum(fscore) over(partition by fyear)),2) as accu_ratio_score
from t3
order by fyear,fdept
year dept score accu_ratio_score
2014 A 3 0.17
2014 B 1 0.33
2014 C 2 0.50
2015 A 4 0.14
2015 D 3 0.29

窗口大小控制

1
2
3
4
5
6
7
8
create table t4 like t1;

insert into t4 values
('2014',  'A',   '3'),
('2014',  'B',   '1'),
('2014',  'C',   '2'),
('2015',  'A',   '4'),
('2015',  'D',   '3');
fyear fdept fscore
2014 A 3
2014 B 1
2014 C 2
2015 A 4
2015 D 3

按a分组按b字段排序，对c取前后各一行的和

1
2
3
4
5
select 
  fyear as `year`,
  fdept as dept,
  lag(fscore,1,0) over(partition by fyear order by fdept) | lead(fscore,1,0) over(partition by fyear order by fdept) as sum_range_score
from t4;
year dept sum_range_score
2014 A 1
2014 B 5
2014 C 1
2015 A 3
2015 D 4

按a分组按b字段排序，对c取平均值

前一行与当前行的均值

1
2
3
4
5
6
select
  fyear as `year`,
  fdept as `dept`,
  fscore as `score`,
  lag(fscore,1) over(partition by fyear order by fdept) as lag_score
from t4;
year dept score lag_score
2014 A 3
2014 B 1 3
2014 C 2 1
2015 A 4
2015 D 3 4 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
select
  `year`,
  `dept`,
  `score`,
  case when lag_score is null then score else (score | lag_score)/2 end as avg_lag2
from
(
  select
    fyear as `year`,
    fdept as `dept`,
    fscore as `score`,
    lag(fscore,1) over(partition by fyear order by fdept) as lag_score
  from t4
) tt
year dept score avg_lag2
2014 A 3 3
2014 B 1 2.0000
2014 C 2 1.5000
2015 A 4 4
2015 D 3 3.5000

产生连续数值

数据扩充与收缩

合并与拆分

模拟循环操作

不使用distinct或group by去重

容器–反转内容

多容器–成对提取数据

多容器–转多行

抽象分组–断点排序

业务逻辑的分类与抽象–时效

时间序列–进度及剩余

时间序列–构造日期

时间序列–构造累计日期

时间序列–构造连续日期

时间序列–取多个字段最新的值

时间序列–补全数据

时间序列–取最新完成状态的前一个状态

非等值连接–范围匹配

非等值连接–最新匹配

N指标–累计去重