整理产品或者运营导出、更新数据的需求时所执行的 SQL。
一条 SQL 解决所有,适用于业务访问量不大,写代码费时、费力,上线困难的情况。
后面还会持续更新。
- # 单个的情况
- SELECT
- `username`,`mobile_phone`,
- (CASE `role`
- WHEN 1 THEN '普通会员'
- WHEN 2 THEN '铜牌会员'
- WHEN 3 THEN '银牌会员'
- WHEN 4 THEN '金牌会员'
- ELSE '未知的用户等级' END) AS `role_zh`
- FROM `t_user`
- # 多个的情况
- SELECT
- `username`,`mobile_phone`,
- (CASE `role`
- WHEN 1 THEN '普通会员'
- WHEN 2 THEN '铜牌会员'
- WHEN 3 THEN '银牌会员'
- WHEN 4 THEN '金牌会员'
- ELSE '未知的用户等级' END) AS `role_zh`,
- (CASE `status`
- WHEN 1 THEN '新注册用户'
- WHEN 2 THEN '审核通过用户'
- WHEN 3 THEN '审核拒绝用户'
- WHEN 4 THEN '资料变更的用户'
- WHEN 4 THEN '无效用户'
- ELSE '未知的用户状态' END) AS `status_zh`
- FROM `t_user`;
- SELECT `mail`, COUNT(*) AS `mail_count` FROM `表名` GROUP BY `mail` ORDER BY `mail_count` DESC;
相同效果的两条 SQL
- SELECT SUBSTRING_INDEX(`mail`, '@', -1) AS `mail_ext`, COUNT(*) AS `mail_ext_count` FROM `t_user` WHERE `mail` !='' GROUP BY `mail_ext` ORDER BY `mail_ext_count` DESC;
- SELECT SUBSTRING(`mail`, INSTR(`mail`, '@')+1) AS `mail_ext`, COUNT(*) as `mail_ext_count` FROM `t_user` WHERE `mail` != '' GROUP BY `mail_ext` ORDER BY `mail_ext_count` DESC;
注解
结果 housanpai.com
- SELECT SUBSTRING_INDEX('admin@housanpai.com', '@', -1)
结果 admin
- SELECT SUBSTRING_INDEX('admin@housanpai.com', '@', 1)
结果 6
- SELECT INSTR('admin@housanpai.com', '@');
结果 @housanpai.com
- SELECT SUBSTRING('admin@housanpai.com', 6);
结果 housanpai.com
- SELECT SUBSTRING('admin@housanpai.com', INSTR('admin@housanpai.com', '@')+1);
- SELECT * FROM(SELECT SUBSTRING_INDEX(`mail`, '@', -1) AS`mail_ext`, COUNT( * ) AS`mail_ext_count`FROM`t_user`WHERE`mail` != ''GROUP BY`mail_ext`ORDER BY`mail_ext_count`DESC) AS`main_ext_data`WHERE`main_ext_data`.`mail_ext_count` > 2;
- #先按照字段一倒序排列,字段一相同的按照字段二顺序排列SELECT * FROM`表名`ORDER BY`字段一`DESC,
- `字段二`ASC;
- SELECT * FROM`表名`WHERE CONCAT(`字段1`, `字段2`......) LIKE '%关键字%';
- SELECT CASE MAX(`字段`) IS NULL WHEN 1 THEN 1 ELSE MAX(`字段`)+1 END FROM `表名`;
把 t_user.mail 为空的更新为 t_auth_info.mail 不为空的值,t_user.id 等于 t_auth_info.uid。
- UPDATE `t_user`
- INNER JOIN (SELECT * FROM `t_auth_info` WHERE `t_auth_info`.`mail` != '') AS `auth_info_mail_all` ON `auth_info_mail_all`.`uid` = `t_user`.`id`
- SET `t_user`.`mail` = `auth_info_mail_all`.`mail`
- WHERE `t_user`.`mail` = '';
来源: https://juejin.im/entry/59f2c6c86fb9a04524051176