navicat数据库

#查询版本号：
SELECT version();

#根据创建时间排序查询最新的5条

select * from t_auth_code ORDER BY created_at DESC LIMIT 5;

#根据账户查询数据
select * from t_auth_code WHERE rcv_account = '8613419626373';

#根据账户里有@符号的模糊查询，再根据创建时间排序
select * from t_auth_code WHERE rcv_account LIKE '%@%' ORDER BY created_at DESC;

#根据时间查询账户对应的钱包账户
SELECT * FROM t_user WHERE sys_type =1 AND created_at between '2020-10-07 23:59:59' and '2020-10-09 23:59:59';

#查询数据（状态的数字代表显示出来）

SELECT id,(CASE TEMPLATE_ID WHEN 1 THEN '商品区' ELSE '通证' END) AS 'TEMPLATE_ID',SESSION_NO,`NAME`,(CASE `STATUS`WHEN 1 THEN '未开始' WHEN 2 THEN '进行中' WHEN 3 THEN '已结束' WHEN 4 THEN'已删除' END ) AS 'STATUS',GMT_START,GMT_END  FROM ACTIVITY_INFO ORDER BY id DESC LIMIT 10;

#查询没有在游戏登陆过的id
select * from user_relation_view where user_id NOT in (select middle_ground_id from user_view);

#查询顶级会员的加权分红-自己算的
SELECT 11月总手续费,分红会员个数,会员分红比例,11月总手续费 * 0.03 * 会员分红比例/分红会员个数 AS '会员分红所得' 
FROM (SELECT 11月总手续费,分红会员个数,
(CASE 分红会员个数 WHEN 1 THEN 0.1 WHEN 2 THEN 0.3 WHEN 3 THEN 0.5 WHEN 4 THEN 0.7 WHEN 5 THEN 0.9 ELSE 0.9 END ) AS '会员分红比例'
FROM ( SELECT SUM( tax_cost ) AS '11月总手续费',( SELECT COUNT( * ) FROM user_view WHERE member_grade = 7 AND vip = 1 ) AS '分红会员个数' 
FROM transaction_log_view_202111 WHERE created_at BETWEEN '2021-11-01 00:00:00' AND '2021-11-30 23:59:59' ) t ) tt;

#加权分红查询的结果加个序列号
select (@ROW:=@ROW+1) AS 'rowNo',s.* from user_share_bonus s,(select @ROW:=0)t order by s.create_time desc;

#根据Vip个数查询分红
SELECT t.id AS 'gameId', t.user_id,t.share_bonus,t.create_time FROM (
SELECT( @ROW := @ROW + 1 ) AS rowNo,s.*,c.*,u.* FROM ( SELECT @ROW := 0 ) a,user_share_bonus s,(SELECT COUNT(*) vipCount FROM user_view WHERE vip = 1 ) c,user_view u
WHERE u.middle_ground_id=s.user_id) t WHERE t.rowNo <=t.vipCount;

#查询某个用户的创建时间+15小时
SELECT mobile,created_at,date_add(created_at,INTERVAL 15 HOUR) AS 'created_atAdd' 
from t_user where mobile