mysql难题收录

1.计算相邻两行的年龄的差距

表中的数据如下

select (s.age-(select age from stu where id - s.id = 1)) from stu as s;

select a.age-b.age from stu a,stu b where a.id-b.id=1;

select a.age-b.age from stu a inner join stu b on a.id = b.id + 1;

 

2.找出0-200之间的数据的id数值中带有7的数据

(select a.id from student a where mod(a.id,10)=7 and a.id <201)
union
(select a.id from student a limit 69,10)
union
(select a.id from student a limit 169,10);

分析:
先找出末尾是7的数值,在找出十位上是7的数据....

 3. case when then end 的使用

select name,address,case s.Address when "浙江余杭" then "Yuhang" else "other" end as res from student s;
select name,address,case s.address when s.address is null then "unknow" else s.address as address from student s;

 4. if 的使用

select s.address,if(s.Address = "浙江余杭", "Yuhang", "others") from student s;