MySQL常见业务场景的SQL

#表结构 id，userid,login_ip,login_time

#求user表登录次数超过2次的用户

    select userid from login group by userid having count(userid)>=2;

#求user表登录超过2次用户总数

    select count(*) from (select userid from login group by userid having count(userid)>=2) as b;

#表结构 id user_name user_pwd register_time

#表2结构id user_id course_id  buytime

#查询买课的用户列表

    select a.user_name from user a left join course b on a.id = b.id where b.course_id >= 1;

#表结构  username goods num

#写出购物商品为2种以上的购物人的信息

    select username from good group by username having count(goods)>2;

#表结构 number math chinese english physics chemistry

#求高考总分在600分以上的考生号

    select number from exam group by useid having sum(math+chinese+english+physics+chemistry)>6000;

#表结构 name score course

#求每门课都大于80分的学生

    select name from test.stu group by name having count(score) =sum(case  when score>80 then 1 else 0 end );

    select name from stu group by name having name not in (select name from stu where score <80);

    select name from test.stu group by name having min(score)>=80;

#case编程

    select

    (case when语文>=80 then '优秀' when语文>60 then '及格' else '不及格' end) as 语文,

    (case when 数学>=80 then '优秀' when数学>60 then '及格' else '不及格' end) as数学,

    (case when英语>=80 then '优秀' when英语>60 then '及格' else '不及格' end) as 英语

    from tab5

#5.一个日期判断的sql语句请取出tab5表中日期(SendTime字段)为当天的所有记录 (SendTime字段为datetime型，包含日期与时间)

    select * from tab5 t where to_char(t.SendTime,'yyyy-mm-dd')=to_char(sysdate,'yyyy-mm-dd')

#统计胜负场次

    select rq,sum(case when shengfu='胜' then 1 else 0 end) as胜,sum(case when shengfu='负' then 1 else 0 end) as负from tab3 group by rq