一条 MySQL 语句完成产品运营的导出和更新数据的需求(持续更新)

整理产品或者运营导出、更新数据的需求时所执行的 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;

注解

SELECT SUBSTRING_INDEX('admin@housanpai.com', '@', -1) 结果 housanpai.com

SELECT SUBSTRING_INDEX('admin@housanpai.com', '@', 1) 结果 admin

SELECT INSTR('admin@housanpai.com', '@'); 结果 6

SELECT SUBSTRING('admin@housanpai.com', 6); 结果 @housanpai.com

SELECT SUBSTRING('admin@housanpai.com', INSTR('admin@housanpai.com', '@')+1); 结果 housanpai.com

分组数据作为表和条件

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;

LIKE 多个字段

SELECT * FROM `表名` WHERE CONCAT(`字段1`,`字段2`......) LIKE '%关键字%';

查询某整型字段最大的值并且加 1 后进行输出,如果值为 NULL 则输出 1

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` = '';
知识共享许可协议
本作品采用知识共享署名-非商业性使用-禁止演绎 4.0 国际许可协议进行许可。
随喜
支付宝随喜
微信随喜

Lillard appeared on the Pull Up with CJ McCollum podcast, where he was asked about George calling the game-winning bucket a bad shot. nike factory outlet http://www.nikefactoryoutletstoreonline.com/

Mike Mayock should send Giants GM David Gettleman a gift basket. The stink of the Giants' reach for Jones drowned out the groans of Raiders fans dumbfounded by Oakland's selection of defensive end Clelin Ferrell with the No. 4 overall pick. Ferrell is a quality player and certainly a first-round talent, but top-five is quite a stretch. Mayock himself thought Ferrell would be a trade-down possibility, and yet, with superior pass rushers like Josh Allen, Ed Oliver, and Rashan Gary still on the board, the Raiders stood pat and took Ferrell. pandora charms http://www.pandora-jewelryoutlet.us/

Biden declined to apologize to Anita Hill, the lawyer who accused Supreme Court Justice Clarence Thomas of sexual harassment during Thomas’s 1991 confirmation hearing, which was chaired by Biden. The former Delaware senator has been criticized for his treatment of Hill, and after 28 years reached out to her prior to the launch of his campaign. Hill told the New York Times that she wasn’t satisfied with Biden’s non-apology. When offered a chance to issue a more complete apology, Biden said he didn’t feel that he treated her badly. Nike Shoes http://www.nikeshoes.us.org/

http://www.nfl-jerseys.us.org/ Cheap NFL Jerseys Cheap Nfl Jerseys Wholesale http://www.wholesalenfljerseysshop.us/

lnzxvm,Hi there, just wanted to say, I liked this article. It was helpful. Keep on posting! NFL Jerseys Cheap http://www.cheapjerseyselitenfl.us/

http://www.pittsburghsteelers-jerseys.us/ Pittsburgh Steelers Jerseys Jordan 11 Concord 2018 http://www.jordan11-concord.com/

Secure Ordering Pyridium Internet Shop <a href=http://buycheapcial.com>canadian pharmacy cialis</a> Generique Cialis Net Rhineinc.In

oqykytazlp,Definitely believe that which you said. Your favourite justification appeared to be on the net the simplest thing to remember of. Air Jordan 12 Gym Red http://www.jordan12gymred.us.com/

Viagra Bestellen Met Ideal Viagra Cialis Donne <a href=http://yafoc.com>propecia fiabilidad</a> Propecia 0.25 Eod

ubfqwulozj Yeezy Boost 350,Very helpful and best artical information Thanks For sharing. Travis Scott Air Jordan 1 http://www.travisscottjordan1.us.com/

How To Buy Clomid On Line <a href=http://cthosts.net>canadian pharmacy cialis</a> Viagra Vente Qubec Viagra Per Erboristeria Vous Durer Plus Longtemps