数据库面试题1:
User 字段:userid,name,data,gameid;
Game字段:gameid,gname
要求2014-08-06号02:00到08:00期间某一用户用户登录次数?
1)求出在2014-08-06号02:00到08:00期间所有玩家的信息以及游戏名字 作为view视图
select u.userid,u.name,u.data,g.gname from user u,game g where u.data between 2014-08-06- 02:00 and 2014 -08 -06:08:00 and u.gameid = g.gameid
2)从view中查找出所有玩家 的userid的登陆次数
select count(userid) from view
3)按照玩家id分组查询,从view中查出每一玩家在此期间的登陆次数
select count(*), userid from view group by userid
改善版
有一张游戏登陆信息表usergame
字段为userid,username,logintime,gname;要求出玩家在某一期间登陆某一游戏的次数;
1>查处在某一期间登陆某一游戏的所有次数;
select count(userid) from usergame where logintime between...and... and gname = "dandandao";
2>按照userid分组查询,查出此期间玩家登录次数;
select count(userid) ,userid from usergame where logintime between...and... and gname = "dandandao" group by userid;
3>查处登录次数大于5的玩家名字;
select usergame from usergame where userid in( (select userid from usergame where logintime between...and... and gname = "dandandao" group by userid
having count(userid) > 5));