SQL: Tri par Email nom de domaine

Pour MySQL:

select email, SUBSTRING_INDEX(email,'@',-1) AS domain from user order by domain desc;

pour les cas insensibles:

select user_id, username, email, LOWER(SUBSTRING_INDEX(email,'@',-1)) AS domain from user order by domain desc;

si vous voulez que cette solution soit mise à l'échelle, vous devriez essayez d'extraire des sous-colonnes. Les fonctions par rangée sont notoirement lentes car la table devient de plus en plus grande.

droit la chose à faire dans ce cas est de déplacer le coût de l'extraction de select (où cela arrive souvent) à insert/update où il se produit moins (dans la plupart des bases de données normales). En supportant le coût seulement sur insert et update, vous augmentez considérablement l'ensemble de la l'efficacité de la base de données, puisque c'est le point dans le temps où vous devez le faire (c.-à-d., c'est le seul moment où les données changent).

pour ce faire, divisez l'adresse courriel en deux colonnes distinctes dans le tableau, email_user et email_domain). Ensuite, vous pouvez soit le diviser dans votre application avant l'insertion/mise à jour ou utiliser un déclencheur (ou des colonnes pré-calculées si votre SGBD le supporte) dans la base de données pour le faire automatiquement.

Puis vous tri sur email_domain et, lorsque vous voulez l'adresse email complète, vous utilisez email_name|'@'|email_domain.

alternativement, vous pouvez garder le plein email colonne et utilisez un déclencheur pour dupliquer juste la partie du domaine dans email_domain, alors vous n'avez pas besoin de vous soucier de concaténer les colonnes pour obtenir l'adresse email complète.

il est parfaitement acceptable de revenir de 3NF pour des raisons de performance si vous savez ce que vous faites. Dans ce cas, les données des deux colonnes ne peuvent pas être désynchronisées tout simplement parce que les détonateurs ne le permettent pas. C'est un bon moyen d'échanger de l'espace disque (relativement bon marché) pour les performances (nous toujours vous voulez plus de qui).

et, si vous êtes du genre à ne pas aimer revenir du tout de 3NF, le email_name/email_domain solution de résoudre ce problème.

ceci suppose aussi que vous voulez simplement gérer les adresses e-mail du formulaire a@b - il y a d'autres adresses email valides, mais je ne me souviens pas les avoir vues dans la nature pour an.

pour SQL Server, vous pouvez ajouter un colonne calculée à votre table avec des extraits du domaine dans un champ séparé. Si vous persistez cette colonne dans la table, vous pouvez l'utiliser comme n'importe quel autre champ et même mettre un index dessus, pour accélérer les choses, si vous interrogez par nom de domaine beaucoup:

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

donc maintenant votre table aurait une colonne supplémentaire "nom de domaine" qui contient n'importe quoi après le signe " @ " dans votre adresse e-mail.

en supposant que vous devez vraiment satisfaire MySQL, Oracle et MSSQL .. le moyen le plus efficace pourrait être de stocker le nom du compte et le nom de domaine dans deux champs distincts. Vous pouvez faire votre commande:

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

comme donnie le fait remarquer, les fonctions de manipulation de chaîne ne sont pas standard .. c'est pourquoi vous devez conserver les données redondantes!

j'ai ajouté le compte et le domaine à la troisième requête, puisque je me couche pour rappeler que tous les DBMSs ne trient pas une requête sur un champ qui n'est pas les champs sélectionnés.

Pour postgres la requête est:

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

La valeur 252 est le domaine le plus long autorisé (puisque, la longueur maximale d'un email est 254 y compris la partie locale, le @, et le domaine.

Voir ce pour plus de détails: Quelle est la longueur maximale d'une adresse de courriel valide?

vous allez devoir utiliser les fonctions de manipulation de texte pour analyser le domaine. Puis commandez par la nouvelle colonne.

MySQL, une combinaison intelligente de droite() et instr ()

SQL Server,droite() et patindex ()

Oracle, instr () et substr ()

et, comme l'a dit quelqu'un d'autre, si vous avez un décent à haut nombre de disques, envelopper votre champ d'e-mail dans des fonctions en vous où la clause le fera de sorte que le RDBMS ne peut pas utiliser n'importe quel index que vous pourriez avoir sur cette colonne. Donc, vous pouvez envisager de créer une colonne calculée qui détient le domaine.

si vous avez des millions d'enregistrements, je vous suggère de créer une nouvelle colonne avec le nom de domaine seulement.

cela fonctionnera avec Oracle:

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

Ma suggestion serait (pour 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;

la réponse originale pour SQL Server ne fonctionnait pas pour moi....

Voici une version pour 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

travailler plus intelligemment, pas plus fort:

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