项目中的较高级sql用法

排名和信息导出

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