mysql 查询技巧_MySQL查询技巧

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