【面试】
1、有一个表game_order
1)请写出SQL查询2019-02-15日充值最高前10名的账号。
2)请用一条SQL写出各区间充值统计信息, 查询结果如下:
解:1)
SELECT * FROM game_order
WHERE DATE_FORMAT(FROM_UNIXTIME(created_at),'%Y-%m-%d')='2019-02-15'
ORDER BY money desc LIMIT 10;
在mysql中,可以使用下面语句,存入int类型的时间,该事件为Unix的时间戳,也就是一个11位数字的字符串。这种存储方式可以可以根据用户的时区来显示时间,不会导致时间混乱。
UPDATE game_order SET created_at = unix_timestamp(now()) WHERE uid=2
2)
select count(case when money <= 500 then 1 end) as '1-500元',
count(case when money >500 and money <=1000 then 1 end) as '500-1000元',
count(case when money >1000 and money <=5000 then 1 end) as '1000-5000元',
count(case when money >5000 then 1 end) as '5000元以上'
from game_order;