1.行转列
建表语句
-- 创建 course 表
create table course (
id varchar(20),
teacher_id varchar(20),
week_day varchar(20),
has_course varchar(20)
);
insert into course value
(1,1,2,"Yes"),
(2,1,3,"Yes"),
(3,2,1,"Yes"),
(4,3,2,"Yes"),
(5,1,2,"Yes")
select * from course;
select teacher_id,
case when week_day=1 then has_course else '' end mon,
case when week_day=2 then has_course else '' end tue,
case when week_day=3 then has_course else '' end tui,
case when week_day=4 then has_course else '' end tur,
case when week_day=5 then has_course else '' end fri
from course
case when x=1 then y 表示 当x列中有为1的值时 取出y列的同行值。
union :得到两个查询结果的并集,并且自动去掉重复行。不会排序
union 要求结构相同要求的是列数相同(在使用的时候要保证列数相同 有时会遇到需要填充的情况)