1.行转列
SELECT user_name,
MAX(CASE course WHEN "math" THEN score ELSE 0 END) AS "math",
MAX(CASE course WHEN "English" THEN score ELSE 0 END) AS "English",
MAX(CASE course WHEN "Chinese" THEN score ELSE 0 END) AS "Chinese"
FROM tb GROUP BY user_name
2.列转行
select user_name, 'math' course, math_score as score from tb
union select user_name, 'English' course English_score as score from tb
union select user_name, 'Chinese' course Chinese_score as score from tb
order by user_name, course;
3.在子查询中实现多列过滤
- 单列：
select * from person where name in (select name from job)
- 多列：
select * from person where(name, sex) in (select name, sex from job)
4.同一属性的多值过滤
select a.no, a.name, b.subject, b.score, c.subject, c.score from student a
join stscore b on a.no = b.stno
join stscore c on b.stno = c.stno
and b.subject='math' and b.score>85 and c.subject ='English' and c.score>85;
- 使用关联进行查询
select a.name, b.subject, b.score, c.subject, c.score, d.subject, d.score from student a
left join stscore b on a.no = b.stno and b.subject = 'math' and b.score > 85
left join stscore c on a.no = c.stno and c.subject = 'English' and c.score > 85
left join stscore d on a.no = d.stno and d.subject = 'Chinese' and d.score > 85
where(case when b.subject is not null then 1 else 0 end) +
(case when c.subject is not null then 1 else 0 end) +
(case when d.subject is not null then 1 else 0 end) >= 2
- 使用Group by实现查询
select a.name from student a join stscore b on a.id = b.stno where
b.subject in ('math', 'English', 'chinese') and b.score > 0
group by a.name having count(*) >= 2
