【数分面试题】SQL专项题目记录二

https://www.cnblogs.com/guxia/p/7878657.html 大佬的笔记

1. 行列转换
   
   考点：case when
   注意： pivot纵表转横表，unpivot 横表转纵表
   https://blog.csdn.net/lch_2016/article/details/81947448
   SELECT date, sum( CASE result WHEN '胜' THEN 1 ELSE 0 END ) AS '胜', sum( CASE result WHEN '负' THEN 1 ELSE 0 END ) AS '负' FROM shengfu GROUP BY date

2.表中有A B C三列，用SQL语句实现：
第一列，当A列大于B列选择A列否则选择B列，第二列，当B列大于C列选择B列否则C列
考点：case when

create table compare 
(
   A int,
   B int,
   C int
)

insert into compare values(10,20,30);
insert into compare values(20,30,10);
insert into compare values(30,10,20);
insert into compare values(10,20,30);

select * from compare

select (case when a>b and a>c then a
			 when b>a and b>c then b
			 else c
			 end)
from compare

3. 表名test_table中有一个date型字段create_date,取出所有create_date为今天得记录
   考点：curdate(), now()

select date_format(now(),'%y-%m-%d') as date

select * from test_table where crate_date = curdate()

4.
考点：case when,分组分类

select
(case when 语文>=80 then ‘优秀’
when 语文>=60 then ‘及格’
else ‘不及格’) as 语文,
(case when 数学>=80 then ‘优秀’
when 数学>=60 then ‘及格’
else ‘不及格’) as 数学,
(case when 英语>=80 then ‘优秀’
when 英语>=60 then ‘及格’