https://www.cnblogs.com/guxia/p/7878657.html 大佬的笔记
- 行列转换
考点: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
- 表名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 ‘及格’