SQL：按电子邮件域名排序

Question

按域名片段对包含电子邮件地址列的表进行排序的最短和/或高效的 SQL 语句是什么？

这本质上是忽略电子邮件地址中“@”之前的任何内容并且不区分大小写。让我们忽略这个域名的国际化域名。

目标：mySQL、MSSQL、Oracle

来自 TABLE1 的示例数据

id   name           email 
------------------------------------------
 1   John Doe       [email protected]
 2   Jane Doe       [email protected]
 3   Ali Baba       [email protected]
 4   Foo Bar        [email protected]
 5   Tarrack Ocama  [email protected]

通过电子邮件订购
从表 1 中选择 * 按电子邮件 ASC 排序

id   name           email 
------------------------------------------
 3   Ali Baba       [email protected]
 4   Foo Bar        [email protected]
 2   Jane Doe       [email protected]
 1   John Doe       [email protected]
 5   Tarrack Ocama  [email protected]

按域排序
从表 1 中选择 * 排序依据 ?????? ASC

id   name           email 
------------------------------------------
 5   Tarrack Ocama  [email protected]
 3   Ali Baba       [email protected]
 1   John Doe       [email protected]
 2   Jane Doe       [email protected]
 4   Foo Bar        [email protected]

编辑：
我并不是要求单个 SQL 语句可以在所有 3 个或更多 SQL 引擎上运行。欢迎任何贡献。 :)

Answer 1

试试这个

查询（对于 Sql Server）：

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 name email< /强>

5   Tarrack Ocama   [email protected]
3   Ali Baba    [email protected]
1   John Doe    [email protected]
2   Jane Doe    [email protected]
4   Foo Bar [email protected]

Answer 2

对于 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;

Answer 3

如果您希望此解决方案能够扩展，那么您不应该尝试提取子列。随着表变得越来越大，每行函数的速度非常慢。

在这种情况下，正确的做法是将提取成本从 select（这种情况经常发生）转移到 insert/update，其中它发生的次数较少（在大多数普通数据库中）。通过仅在插入和更新上产生成本，您可以大大提高数据库的整体效率，因为这是您进行操作的唯一时间点需要这样做（即，这是数据更改的唯一时间）。

为了实现此目的，请将电子邮件地址拆分为表中的两个不同的列：email_user 和 email_domain）。然后，您可以在插入/更新之前在应用程序中将其拆分，或者在数据库中使用触发器（或预先计算的列，如果您的 DBMS 支持）来自动执行此操作。

然后，您对 email_domain 进行排序，当您需要完整的电子邮件地址时，您可以使用 email_name|'@'|email_domain。

或者，您可以保留完整的 email 列，并使用触发器仅复制 email_domain 中的域部分，然后您就无需担心连接列以获得完整的内容电子邮件。

如果您知道自己在做什么，出于性能原因从 3NF 恢复是完全可以接受的。在这种情况下，两列中的数据不能仅仅因为触发器不允许而不同步。这是用磁盘空间（相对便宜）换取性能的好方法（我们总是想要更多）。

而且，如果您根本不喜欢从 3NF 恢复，email_name/email_domain 解决方案将解决该问题。

这还假设您只想处理 a@b 形式的电子邮件地址 - 还有其他有效的电子邮件地址，但我不记得多年来在野外看到过其中任何一个。

Answer 4

对于 SQL Server，您可以将计算列添加到表中，并将域提取到单独的字段中。如果将该列保留到表中，则可以像任何其他字段一样使用它，甚至可以在其上放置索引，以加快速度（如果您经常按域名查询）：

ALTER TABLE Table1
  ADD DomainName AS 
     SUBSTRING(email, CHARINDEX('@', email)+1, 500) PERSISTED

所以现在您的表将有一个附加列“DomainName” ”，其中包含您的电子邮件地址中“@”符号之后的任何内容。

Answer 5

假设您确实必须满足 MySQL、Oracle 和 MSSQL 的要求……最有效的方法可能是将帐户名和域名存储在两个单独的字段中。您可以进行排序：

select id,name,email from table order by name

select id,name,email,account,domain from table order by email

select id,name,email,account,domain from table order by domain,account

正如唐尼指出的那样，字符串操作函数是非标准的..这就是为什么您必须保持数据冗余！

我已将帐户和域添加到第三个查询中，因为我似乎记得并非所有 DBMS 都会对不在所选字段中的字段进行查询排序。

Answer 6

这适用于 Oracle：

select id,name,email,substr(email,instr(email,'@',1)+1) as domain
from table1
order by domain asc

Answer 7

对于 postgres，查询为：

SELECT * FROM table
ORDER BY SUBSTRING(email,(position('@' in email) + 1),252)

值 252 是允许的最长域（因为，电子邮件的最大长度为 254，包括本地部分，@< /code> 和域，

了解更多详细信息：有效电子邮件地址的最大长度是多少？

Answer 8

您将必须使用文本操作函数来解析域。然后按新列排序。

Answer 9

MySQL，right() 和 instr()

SQL Server， right() 和 patindex()

Oracle，instr() 和 substr()

而且，正如其他人所说，如果您有相当高的记录数，则将您的电子邮件字段包装在您的 where 子句中的函数中将使其如此RDBMS 无法使用该列上可能拥有的任何索引。因此，您可能需要考虑创建一个保存域的计算列。

Answer 10

如果您有数百万条记录，我建议您创建仅包含域名的新列。

Answer 11

我的建议是（对于mysql）：

SELECT 
    LOWER(email) AS email,
    SUBSTRING_INDEX(email, '@', + 1) AS account,
 REPLACE(SUBSTRING_INDEX(email, '@', -1), CONCAT('.',SUBSTRING_INDEX(email, '.', -1)),'') -- 2nd part of mail - tld.
  AS domain,
    CONCAT('.',SUBSTRING_INDEX(email, '.', -1)) AS tld
FROM
********
ORDER BY domain, email ASC;

And then just add a WHERE...

Answer 12

SQL Server 的原始答案对我不起作用......

这是 SQL Server 的版本......

select SUBSTRING(email,(CHARINDEX('@',email)+1),len(email)), count(*) 
from table_name 
group by SUBSTRING(email,(CHARINDEX('@',email)+1),len(email))
order by count(*) desc

Answer 13

更聪明地工作而不是更努力地工作：

SELECT REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING(emails.email, POSITION('@' IN emails.email)+1)),'.',2)) FROM emails