MySql性能调优05-[sql实战演练]

行列转换

行列式转换

第一题【列转行】

• A结果答案

select t.name 学生Name,c.不及格,c.及格 From stu t inner join (select b.no, sum(case when b.result<60 then 1 else 0 end) 不及格,sum(case when b.result>=60 then 1 else 0 end ) 及格 From res b group by b.no) c on t.no=c.no;

• B结果答案

select t.name 学生Name,c.数学,c.语文,c.英语 From stu t inner join (select b.no,sum(case when b.course='数学' then b.result else 0 end) 数学,sum(case when b.course='语文' then b.result else 0 end) 语文,sum(case when b.course='英语' then b.result else 0 end) 英语From res b group by b.no) c on t.no=c.no;

第二题【列转行】

• 答案

select a.zh,sum(case when a.date='202301' then a.amt else 0 end) a,sum(case when a.date='202302' then a.amt else 0 end) b,sum(case when a.date='202303' then a.amt else 0 end) cFrom account a group by a.zh;

having的使用

找到表中，名字重复的项

select t.name From person t group by t.name having count(*)>1;

有数据表employee，包含如下字段id、name、department、age，编写SQL，找到不与其他人同龄的年纪最大的员工的年龄

select age from employees where age not in(select age from employees group by age having count(*)>1) order by age desc limit 0,1;

有数据表employee，包含如下字段id、name、department、age，编写SQL，找到与其他人同龄的年纪最大的员工的年龄

select max(t.age) from (select age from employees group by age having count(*)>1) t;