una - SQL: clasificación por nombre de dominio de correo electrónico
la regla controlador de dominio del equipo genero una advertencia (13)
¿Cuál es la declaración SQL más corta y / o eficiente para ordenar una tabla con una columna de dirección de correo electrónico por su fragmento de nombre DOMINIO?
Eso es esencialmente ignorar lo que está antes de "@" en las direcciones de correo electrónico y no distingue entre mayúsculas y minúsculas. Vamos a ignorar los nombres de dominio internacionalizados para este.
Destino en: mySQL, MSSQL, Oracle
Datos de muestra de 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]
Ordenar por correo electrónico
SELECT * FROM TABLE1 ORDER BY EMAIL 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]
Ordenar por dominio
SELECT * FROM TABLE1 ORDER BY ?????? 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]
EDITAR:
No estoy pidiendo una sola declaración SQL que funcione en los 3 o más motores SQL. Cualquier contribución es bienvenida. :)
Mi sugerencia sería (para 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;
Asumiendo que realmente debe atender a MySQL, Oracle y MSSQL ... la forma más eficiente podría ser almacenar el nombre de la cuenta y el nombre de dominio en dos campos separados. El puede hacer su pedido:
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
como señala donnie, las funciones de manipulación de cadenas no son estándar ... ¡por eso tendrá que mantener los datos redundantes!
He agregado la cuenta y el dominio a la tercera consulta, ya que debo recordar que no todos los DBMS clasificarán una consulta en un campo que no se encuentre en los campos seleccionados.
MySQL, una combinación inteligente de right () e instr ()
SQL Server, right () y patindex ()
Y, como dijo alguien más, si tiene un recuento de registros decente a alto, envolver su campo de correo electrónico en las funciones de la cláusula where hará que el RDBMS no pueda usar ningún índice que pueda tener en esa columna. Por lo tanto, es posible que desee considerar la creación de una columna calculada que contenga el dominio.
Para MySQL:
select email, SUBSTRING_INDEX(email,''@'',-1) AS domain from user order by domain desc;
Para mayúsculas y minúsculas:
select user_id, username, email, LOWER(SUBSTRING_INDEX(email,''@'',-1)) AS domain from user order by domain desc;
Para SQL Server, puede agregar una columna calculada a su tabla con extractos del dominio en un campo separado. Si persiste esa columna en la tabla, puede usarla como cualquier otro campo e incluso poner un índice en ella, para acelerar las cosas, si consulta mucho por nombre de dominio:
ALTER TABLE Table1
ADD DomainName AS
SUBSTRING(email, CHARINDEX(''@'', email)+1, 500) PERSISTED
Entonces, ahora su tabla tendría una columna adicional "DomainName" que contiene algo después del signo "@" en su dirección de correo electrónico.
Prueba esto
Query (para el servidor Sql):
select * from mytbl
order by SUBSTRING(email,(CHARINDEX(''@'',email)+1),1)
Query (para Oracle):
select * from mytbl
order by substr(email,INSTR(email,''@'',1) + 1,1)
Query (para MySQL)
pygorex1 already answered
Salida:
nombre de identificación correo electrónico
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]
Si tiene millones de registros, le sugiero que cree una nueva columna solo con nombre de dominio.
Tendrá que usar las funciones de manipulación de texto para analizar el dominio. Luego ordene por la nueva columna.
Si desea que esta solución se escale, no debe intentar extraer subcolumnas. Las funciones por fila son notoriamente lentas a medida que la mesa se hace más y más grande.
Lo correcto en este caso es mover el costo de extracción de select (donde ocurre mucho) para insert/update donde ocurre menos (en la mayoría de las bases de datos normales). Al incurrir en el costo solo en la insert y update , usted aumenta en gran medida la eficiencia general de la base de datos, ya que es el único momento en el que debe hacerlo (es decir, es el único momento en que los datos cambian).
Para lograr esto, divide la dirección de correo electrónico en dos columnas distintas en la tabla, email_user y email_domain ). Luego puede dividirlo en su aplicación antes de la inserción / actualización o usar un desencadenador (o columnas precalculadas si su DBMS lo admite) en la base de datos para hacerlo automáticamente.
Luego ordena en email_domain y, cuando quiere la dirección de correo electrónico completa, usa email_name|''@''|email_domain .
Alternativamente, puede mantener la columna de email completa y usar un disparador para duplicar solo la parte del dominio en email_domain , entonces nunca tendrá que preocuparse por concatenar las columnas para obtener la dirección de correo electrónico completa.
Es perfectamente aceptable revertir desde 3NF por motivos de rendimiento siempre que sepa lo que está haciendo. En este caso, los datos en las dos columnas no pueden desajustarse simplemente porque los desencadenadores no lo permitirán. Es una buena forma de intercambiar espacio en disco (relativamente barato) por rendimiento ( siempre queremos más de eso).
Y, si es del tipo que no le gusta revertir desde 3NF en absoluto, la solución email_name/email_domain lo solucionará.
Esto también supone que solo quieres manejar las direcciones de correo electrónico del formulario a@b ; hay otras direcciones de correo electrónico válidas, pero no recuerdo haber visto ninguna en la vida real durante años.
trabaja inteligentemente y no duro:
SELECT REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING(emails.email, POSITION(''@'' IN emails.email)+1)),''.'',2)) FROM emails
Esto funcionará con Oracle:
select id,name,email,substr(email,instr(email,''@'',1)+1) as domain
from table1
order by domain asc
Para postgres la consulta es:
SELECT * FROM table
ORDER BY SUBSTRING(email,(position(''@'' in email) + 1),252)
El valor 252 es el dominio permitido más largo (dado que la longitud máxima de un correo electrónico es 254 incluida la parte local, el @ y el dominio.
Vea esto para más detalles: ¿Cuál es la longitud máxima de una dirección de correo electrónico válida?
La respuesta original para SQL Server no funcionó para mí ...
Aquí hay una versión para 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