SQL：按电子邮件域名排序？
SQL：按电子邮件域名排序？
提问于 2018-03-04 05:58:37
目标在：mySQL，MSSQL，Oracle
样本数据 TABLE1
id name email
------------------------------------------
1 John Doe johndoe@domain.com
2 Jane Doe janedoe@helloworld.com
3 Ali Baba ali@babaland.com
4 Foo Bar foo@worldof.bar.net
5 Tarrack Ocama me@am-no-president.org
电子邮件
SELECT * FROM TABLE1 ORDER BY EMAIL ASC
id name email
------------------------------------------
3 Ali Baba ali@babaland.com
4 Foo Bar foo@worldof.bar.net
2 Jane Doe janedoe@helloworld.com
1 John Doe johndoe@domain.com
5 Tarrack Ocama me@am-no-president.org
按域排序
SELECT * FROM TABLE1 ORDER BY ?????? ASC
id name email
------------------------------------------
5 Tarrack Ocama me@am-no-president.org
3 Ali Baba ali@babaland.com
1 John Doe johndoe@domain.com
2 Jane Doe janedoe@helloworld.com
4 Foo Bar foo@worldof.bar.net回答 2
Stack Overflow用户
发布于 2018-03-04 14:34:30
这个。
查询(用于SQLServer)：
select * from mytbl
order by SUBSTRING(email,(CHARINDEX('@',email)+1),1)查询(针对Oracle)：
select * from mytbl
order by substr(email,INSTR(email,'@',1) + 1,1)查询(用于MySQL)
pygorex1 already answered产出：
ID名称电子邮件
5 Tarrack Ocama me@am-no-president.org
3 Ali Baba ali@babaland.com
1 John Doe johndoe@domain.com
2 Jane Doe janedoe@helloworld.com
4 Foo Bar foo@worldof.bar.netStack Overflow用户
发布于 2018-03-04 15:38:58
对于MySQL：
select email, SUBSTRING_INDEX(email,'@',-1) AS domain from user order by domain desc;对大小写不敏感：
select user_id, username, email, LOWER(SUBSTRING_INDEX(email,'@',-1)) AS domain from user order by domain desc;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/-100007501
复制相关文章
相似问题