IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

  1. #1
    Membre régulier
    Inscrit en
    février 2006
    Messages
    256
    Détails du profil
    Informations forums :
    Inscription : février 2006
    Messages : 256
    Points : 77
    Points
    77
    Par défaut Faire un SELECT WHERE sur un autre SELECT AS
    Salut

    Je montre ma commande SQL et ensuite j'explique son but :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    select REPLACE(email_addr,'.','') as "email_a_tester"
    from compte
    where email_addr like '%@gmail.com' and
    email_addr not like ''
    group by REPLACE(email_addr,'.','')
    HAVING COUNT(email_addr) >= 1

    Avec les commentaires:
    select REPLACE(email_addr,'.','') as "email_a_tester" // je retire tous les points "." dans les adresses emails en créant une colonne propre avec "as" car sinon ça va créer une colonne avec le nom en "REPLACE(email_addr,'.','')" et c'est pas beau
    from compte // la table
    where email_addr like '%@gmail.com' and // je récupère seulement emails se terminant par %@gmail.com, pas les autres
    email_addr not like '' // je ne récupère pas les quelques comptes créés autrefois avec un numéro de tél (qui sont sans adresse email)
    group by REPLACE(email_addr,'.','') // c'est comme le select tout en haut du coup
    HAVING COUNT(email_addr) >= 1 // je récupère toute la liste d'email pour les lister dans la colonne "email_a_tester"

    Vous l'aurez compris, cette table elle liste toutes les adresses emails venant d'une table en enlevant tous les ".", donc "to.to@gmail.com" s’affichera en "toto@gmailcom".

    Maintenant ce que je cherche à faire c'est comment devrais-je faire pour rechercher "toto@gmailcom" à partir de la colonne "email_a_tester" ?

    PS: "email_a_tester" c'est un anti-doublons-gmail-avec-ou-sans.+, plus d'informations ici : https://support.google.com/mail/answer/7436150?hl=fr (c'est valable à la fois pour les "." et les "+" visiblement) - Actuellement je teste qu'avec les "." pour faire simple.

    Cdt

  2. #2
    Expert éminent sénior
    Avatar de escartefigue
    Homme Profil pro
    bourreau
    Inscrit en
    mars 2010
    Messages
    6 488
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loir et Cher (Centre)

    Informations professionnelles :
    Activité : bourreau
    Secteur : Finance

    Informations forums :
    Inscription : mars 2010
    Messages : 6 488
    Points : 20 130
    Points
    20 130
    Billets dans le blog
    2
    Par défaut
    Bonsoir,

    De ce que je comprends de votre besoin, il suffit d'encapsuler votre requête dans une autre (utiliser une table dérivée donc) :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    select [...] 
    from ma_table as T1
    inner join
         (select REPLACE(email_addr,'.','') as email_a_tester
          from compte
          where email_addr like '%@gmail.com' 
            and email_addr not like ''
          group by REPLACE(email_addr,'.','')
          HAVING COUNT(email_addr) >= 1
         ) as T2
       on T2.email_a_tester=T1.courriel
    where [...]
    On peut faire la même chose avec EXISTS, IN...

    Par contre, comme il existe des adresses valides avec un point avant le @, pourquoi faire cette gymnastique ?

  3. #3
    Membre régulier
    Inscrit en
    février 2006
    Messages
    256
    Détails du profil
    Informations forums :
    Inscription : février 2006
    Messages : 256
    Points : 77
    Points
    77
    Par défaut
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    select T2.email_a_tester
    from compte
    inner join
         (select REPLACE(email_addr,'.','') as email_a_tester
          from compte
          where email_addr like '%@gmail.com' 
            and email_addr not like ''
          group by REPLACE(email_addr,'.','')
          HAVING COUNT(email_addr) >= 1
         ) as T2
       on T2.email_a_tester="toto@gmailcom" limit 1
    C'est super merci ça fonctionne bien

    Par contre, comme il existe des adresses valides avec un point avant le @, pourquoi faire cette gymnastique ?
    Imaginons que tu crées un compte gmail et ton adresse principale c'est escartefigue@gmail.com, tu peux être contacter en modifiant l'email escartefigue@gmail.com par des "." ou des "+" (exemple: e.s.c.a.rte.fig.ue@gmail.com) du coup tu vas donc recevoir un email, c'est indiqué sur https://support.google.com/mail/answer/7436150?hl=fr et moi j'veux utiliser cette requête SQL pour lister toutes les adresses emails en retirant tous les "." (et "+" pour plus tard) et il faut donc retirer les "." et "+" de l'email à vérifier :
    on T2.email_a_tester="toto@gmailcom" (sans . ni +)

    Et cela bloqueras donc les contourneurs qui s’amusent à s'inscrire sur le site en rajoutant des "." dans n'importe quel endroit devant le "@".


    Cdt

  4. #4
    Expert éminent sénior Avatar de Artemus24
    Homme Profil pro
    Agent secret au service du président Ulysses S. Grant !
    Inscrit en
    février 2011
    Messages
    4 731
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Agent secret au service du président Ulysses S. Grant !
    Secteur : Finance

    Informations forums :
    Inscription : février 2011
    Messages : 4 731
    Points : 13 567
    Points
    13 567
    Par défaut
    Salut à tous.

    Je viens de lire ce document :
    --> https://support.google.com/mail/answer/7436150?hl=fr

    et je constate que la syntaxe des adresses email est très particulière chez google.
    Il y a sûrement des petits malins qui ont abusé des adresses emails en jouant sur les point.

    @ escartefigue : votre solution ne peut pas fonctionner car dans la table compte, l'email peut s'écrire de plusieurs façon.
    En fait, vous ne récupérez que l'email sous cette forme "toto@gmail.com" et non ceux avec des points "t.o.t.o@gmail.com".

    @ tous : la solution est de retourner toutes les lignes répondant aux critères, sans faire de produit cartésien.
    Pour ce faire, il faut passer par une vue qui va extraire et mettre en forme toutes les lignes, sans les regrouper.
    Puis faire une jointure entre la vue afin de donner les identifiant, et la même vue avec regroupement.
    Ce qui donne :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112
    113
    114
    115
    116
    117
    118
    119
    120
    121
    122
    123
    --------------
    START TRANSACTION
    --------------
     
    --------------
    set session collation_connection = "latin1_general_ci"
    --------------
     
    --------------
    DROP DATABASE IF EXISTS `base`
    --------------
     
    --------------
    CREATE DATABASE IF NOT EXISTS `base`
            DEFAULT CHARACTER SET `latin1`
            DEFAULT COLLATE       `latin1_general_ci`
    --------------
     
    --------------
    DROP TABLE IF EXISTS `test`
    --------------
     
    --------------
    CREATE TABLE `test`
    (  `id`      integer unsigned  NOT NULL auto_increment Primary key,
       `email`   varchar(255)      NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
    --------------
     
    --------------
    INSERT INTO `test` (`email`) VALUES
      ('jean.du.pont@gmail.com'),
      ('jean.du.pont@outlook.com'),
      ('j.e.a.n.d.u.p.o.n.t@gmail.com'),
      ('je.an.du.po.nt@gmail.com'),
      ('jean-du-pont@gmail.com'),
     
      ('jean.du.pond@gmail.com'),
      ('jean.du.pond@outlook.com'),
      ('j.e.a.n.d.u.p.o.n.d@gmail.com'),
      ('jea.ndu.po.n.d@gmail.com'),
      ('je.an.du.po.nd@gmail.com'),
      ('jean-du-pond@gmail.com')
    --------------
     
    --------------
    select * from  `test`
    --------------
     
    +----+-------------------------------+
    | id | email                         |
    +----+-------------------------------+
    |  1 | jean.du.pont@gmail.com        |
    |  2 | jean.du.pont@outlook.com      |
    |  3 | j.e.a.n.d.u.p.o.n.t@gmail.com |
    |  4 | je.an.du.po.nt@gmail.com      |
    |  5 | jean-du-pont@gmail.com        |
    |  6 | jean.du.pond@gmail.com        |
    |  7 | jean.du.pond@outlook.com      |
    |  8 | j.e.a.n.d.u.p.o.n.d@gmail.com |
    |  9 | jea.ndu.po.n.d@gmail.com      |
    | 10 | je.an.du.po.nd@gmail.com      |
    | 11 | jean-du-pond@gmail.com        |
    +----+-------------------------------+
    --------------
    drop view if exists `vue`
    --------------
     
    --------------
    create view `vue` as
       select  id,
               replace(`email`,'.','') as test
     
         from  `test`
        where  email like '%@gmail.com'
    --------------
     
    --------------
    select * from `vue`
    --------------
     
    +----+-----------------------+
    | id | test                  |
    +----+-----------------------+
    |  1 | jeandupont@gmailcom   |
    |  3 | jeandupont@gmailcom   |
    |  4 | jeandupont@gmailcom   |
    |  5 | jean-du-pont@gmailcom |
    |  6 | jeandupond@gmailcom   |
    |  8 | jeandupond@gmailcom   |
    |  9 | jeandupond@gmailcom   |
    | 10 | jeandupond@gmailcom   |
    | 11 | jean-du-pond@gmailcom |
    +----+-----------------------+
    --------------
    select      t1.id,
                t1.test
          from  `vue` as t1
     
    inner join (     select  test
                       from  `vue`
                   group by  test
                     having  count(test) > 1
               ) as t2
            on  t2.test = t1.test
    --------------
     
    +----+---------------------+
    | id | test                |
    +----+---------------------+
    |  1 | jeandupont@gmailcom |
    |  3 | jeandupont@gmailcom |
    |  4 | jeandupont@gmailcom |
    |  6 | jeandupond@gmailcom |
    |  8 | jeandupond@gmailcom |
    |  9 | jeandupond@gmailcom |
    | 10 | jeandupond@gmailcom |
    +----+---------------------+
    --------------
    COMMIT
    --------------
     
    Appuyez sur une touche pour continuer...
    Attention à dupont et dupond.

    @+
    Si vous êtes de mon aide, vous pouvez cliquer sur .
    Mon site : http://www.jcz.fr

Discussions similaires

  1. faire appel à une liste sur une autre feuille
    Par vatsyayana dans le forum Excel
    Réponses: 2
    Dernier message: 16/11/2007, 21h23
  2. Réponses: 5
    Dernier message: 06/12/2006, 00h57
  3. Réponses: 23
    Dernier message: 19/09/2006, 12h33
  4. [Sécurité] Faire de l'upload sur un autre serveur
    Par Oberown dans le forum Langage
    Réponses: 5
    Dernier message: 05/05/2006, 16h36
  5. comment faire fonctionner l'exe sur une autre machine
    Par brian79 dans le forum C++Builder
    Réponses: 8
    Dernier message: 28/05/2004, 15h00

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo