排名和信息导出

SELECT max(a.score),b.usernick,b.college,b.classname,b.realname,c.xuehao 
FROM websocket.rank as a  
LEFT JOIN glutlife.userinfo_extra AS b on a.userid=b.userid 
LEFT JOIN glutlife.userinfo as c on a.userid=c.id 
where a.appid=178 
GROUP by  a.userid,b.usernick,b.college,b.classname,b.realname,c.xuehao 
order BY MAX(a.score) DESC limit 30

执行耗时20ms。

活动中奖信息导出

SELECT a.award,b.usernick,b.realname,b.college,b.classname,c.xuehao,IF(b.sex=0,'男','女')
FROM module.getprize_record as a 
LEFT join glutlife.userinfo_extra as b on b.userid=a.userid 
LEFT join glutlife.userinfo as c on c.id=a.userid 
WHERE a.award>0 and a.configid=33 
GROUP by  b.usernick,b.realname,b.college,b.classname,c.xuehao,a.award,b.sex
order by a.award ASC

轻应用活跃度查询

SELECT distinct b.yb_userid ,c.yb_username,c.yb_realname,c.yb_collegename,c.yb_classname
FROM glutlife.app_tongji_login  as a 
JOIN glutlife.userinfo as b  on b.id=a.userid 
JOIN glutlife.yiban_userinfo as  c on b.yb_userid= c.yb_userid 
WHERE a.createTime>'2020-11-05' AND a.createTime<'2020-12-05'

表中有相同的数据，则跳过，没有相同的数据，则插入

INSERT INTO `money`(`name`, `tag1`, `tag2`,`tag3`,`tag4`,`old_price`, `new_price`, `image`,`image2`) SELECT '$name','$tag1','$tag2','$tag3','$tag4','$price_old','$price_new','$img','$img1'FROM DUAL WHERE NOT EXISTS(SELECT name from money where image='$img')

表中有相同的数据，则跳过，没有相同的数据，则插入

INSERT INTO `table_name`(`field1`,`field2`)SELECT 'FIELD1','filed2' FROM DUAL WHERE NOT EXISTS(子查询)

hell