1.行轉(zhuǎn)列
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.列轉(zhuǎn)行
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.在子查詢中實(shí)現(xiàn)多列過濾
- 單列：
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;
- 使用關(guān)聯(lián)進(jìn)行查詢
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實(shí)現(xiàn)查詢
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
