Comment sélectionner des lignes sur un critère calculé ?

Version imprimable

16/01/2017, 19h41
Artemus24

Comment sélectionner des lignes sur un critère calculé ?

Salut à tous.

J'ai un petit problème de faisabilité sous la forme exclusive d'une seul select.
Pas de procédure stockée, ce que je sais faire.
Ni de faire le test en php en lisant la première ligne retournée afin de récupérer le critère et de lire les autres lignes sur ce critère.
Ce que je désire, c'est ajouter ce test dans un select déjà existant afin de ne pas le faire en php.

La colonne en question n'est pas présente dans la table, puisqu'elle est construite dans le select.
La colonne peut prendre deux valeurs possibles, soit '1' ou soit '2'.
La table est triée sur ce critère, ce qui fait que les '1' sont toujours avant les '2'.

Deux cas peuvent se présenter :
--> soit j'ai des lignes ayant ce critère à '1' et à '2'.
--> soit j'ai que des lignes ayant ce critère à '2'.

Je désire obtenir les lignes à '1' exclusivement s'il existe des lignes à '1', et donc pas de lignes à '2'.
Et inversement, si je n'ai pas de lignes à '1', je veux exclusivement les lignes à '2'.

J'ai essayé avec la clause "having" mais je ne suis pas arrivé à la construire.

@+

Quelque chose comme ça :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 SELECT * FROM matable WHERE indicateur = 1 UNION ALL SELECT * FROM matable WHERE indicateur = 2 AND NOT EXISTS ( SELECT NULL FROM matable WHERE indicateur = 1 )
S'il y a des lignes à 1, seule la première partie est retournée
S'il n'y a pas de lignes à 1, seule la seconde retourne des lignes

Salut Al1_24.

Non, vous n'avez pas compris la problématique de ce que j'ai exposé. Ce n'est pas aussi simple que votre exemple.
Voici un exemple :

Code:

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
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
SET collation_connection = latin1_general_ci
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `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,
  `val`  smallint unsigned not null,
  `lib`  varchar(255)      not null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `test` (`val`,`lib`) values
  (6,'six'),(9,'neuf'),(8,'huit'),(16,'seize'),(11,'onze'),(13,'treize')
--------------
 
--------------
select * from test
--------------
 
+----+-----+--------+
| id | val | lib    |
+----+-----+--------+
|  1 |   6 | six    |
|  2 |   9 | neuf   |
|  3 |   8 | huit   |
|  4 |  16 | seize  |
|  5 |  11 | onze   |
|  6 |  13 | treize |
+----+-----+--------+
--------------
set @car='i'
--------------
 
--------------
select    *,
          mod(val,2) as pair,
          locate(@car, lib) as lettre,
          case when (mod(val,2) + locate(@car,lib)) = 0 then 1 else 2 end as flag
    from  test
order by  flag, pair, lettre
--------------
 
+----+-----+--------+------+--------+------+
| id | val | lib    | pair | lettre | flag |
+----+-----+--------+------+--------+------+
|  1 |   6 | six    |    0 |      2 |    2 |
|  3 |   8 | huit   |    0 |      3 |    2 |
|  4 |  16 | seize  |    0 |      3 |    2 |
|  2 |   9 | neuf   |    1 |      0 |    2 |
|  5 |  11 | onze   |    1 |      0 |    2 |
|  6 |  13 | treize |    1 |      4 |    2 |
+----+-----+--------+------+--------+------+
--------------
set @car='z'
--------------
 
--------------
select    *,
          mod(val,2) as pair,
          locate(@car, lib) as lettre,
          case when (mod(val,2) + locate(@car,lib)) = 0 then 1 else 2 end as flag
    from  test
order by  flag, pair, lettre
--------------
 
+----+-----+--------+------+--------+------+
| id | val | lib    | pair | lettre | flag |
+----+-----+--------+------+--------+------+
|  1 |   6 | six    |    0 |      0 |    1 |
|  3 |   8 | huit   |    0 |      0 |    1 |
|  4 |  16 | seize  |    0 |      4 |    2 |
|  2 |   9 | neuf   |    1 |      0 |    2 |
|  5 |  11 | onze   |    1 |      3 |    2 |
|  6 |  13 | treize |    1 |      5 |    2 |
+----+-----+--------+------+--------+------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

Dans la première requête, la colonne flag est toujours à '2'. C'est ce que je veux car il n'y a pas de '1'.
Dans la seconde requête, il y a des '1' et des '2', or je veux que des '1'.

Comment modifier les deux requêtes (elles sont identiques), pour avoir uniquement les lignes ayant flag='1' quand il y a des '1' et uniquement les lignes ayant flag = '2' quand il y a des '2' ?

Et je tiens à préciser à nouveau que la colonne flag n'est pas présente dans la table.

@+

16/01/2017, 23h15
al1_24

On serait avec n'importe quel SGBD qui propose les expressions de table commune et/ou les fonctions de regroupement fenêtrées, ce serait assez simple à mettre en œuvre.
Mais avec MySQL qui n'a toujours pas implémenté ces fonctionnalités normalisées depuis le siècle dernier, c'est bien moins facile.

Salut al1_24.

Si j'utilise le SGBDR FireBird, je trouve cette solution :

Code:

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
CREATE DATABASE '..\..\Data\Base.fdb' page_size 4096 DEFAULT CHARACTER SET WIN1252;
 
-- =====================
-- création table "test"
-- =====================
 
create table test (
id   smallint  not null primary key,
val  smallint  not null,
lib  char(10)  not null
);
 
insert into test (id,val,lib) values (1, 6,'six');
insert into test (id,val,lib) values (2, 9,'neuf');
insert into test (id,val,lib) values (3, 8,'huit');
insert into test (id,val,lib) values (4,16,'seize');
insert into test (id,val,lib) values (5,11,'onze');
insert into test (id,val,lib) values (6,13,'treize');
 
select * from test;
 
     ID     VAL LIB
======= ======= ==========
      1       6 six
      2       9 neuf
      3       8 huit
      4      16 seize
      5      11 onze
      6      13 treize
 
 
commit;
 
-- ===========
-- Requête N°1
-- ===========
 
  with tab as (select  id,
                       val,
                       lib,
                       mod(val,2) as pair,
                       position('i' in lib) as lettre,
                       case when (mod(val,2) + position('i' in lib)) = 0 then 1 else 2 end as flag
                 from  test
             order by  flag, pair, lettre)
  select * from tab where flag = (select first 1 flag from tab order by flag);
 
     ID     VAL LIB           PAIR       LETTRE         FLAG
======= ======= ========== ======= ============ ============
      1       6 six              0            2            2
      3       8 huit             0            3            2
      4      16 seize            0            3            2
      2       9 neuf             1            0            2
      5      11 onze             1            0            2
      6      13 treize           1            4            2
 
 
-- ===========
-- Requête N°2
-- ===========
 
  with tab as (select  id,
                       val,
                       lib,
                       mod(val,2) as pair,
                       position('z' in lib) as lettre,
                       case when (mod(val,2) + position('z' in lib)) = 0 then 1 else 2 end as flag
                 from  test
             order by  flag, pair, lettre)
  select * from tab where flag = (select first 1 flag from tab order by flag);
 
     ID     VAL LIB           PAIR       LETTRE         FLAG
======= ======= ========== ======= ============ ============
      3       8 huit             0            0            1
      1       6 six              0            0            1
 
 
exit;
 
Appuyez sur une touche pour continuer...

J'ai repris le même exemple que dans mon deuxième message.
C'est la même requête dans les deux cas, sauf que je teste la présence de la lettre 'i' puis ensuite la lettre 'z'.
D'où l'utilisation du CTE pour parvenir à cette solution.

Comme vous pouvez le voir, je n'ai pas utilisé "EXISTS" pour parvenir à tester la colonne flag.

A priori, dans un select même complexe sous MySql, il n'existe pas de solution. Est-ce bien cela ?

@+

Bonjour,

Je n'ai pas MySQL sous la main, donc à tester, l'idée est de vérifier l'existence de flag=1 par une jointure outer puis d'utiliser un CASE dans le where comme suit :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
select  T1.*
     ,  mod(T1.val, 2)        as  pair
     ,  locate(@car, T1.lib)  as  lettre
     ,  case when (mod(val,2)+locate(@car,lib)) = 0 then 1 else 2 end as flag
from  test as T1
left outer join
    (select val from test where(mod(val,2)+locate(@car,lib))=0) as T2
  on 1=1
where case 
      when T2.val is null     and (mod(T1.val,2)+locate(@car,T1.lib)=0) then 1
      when t2.val is not null and (mod(T1.val,2)+locate(@car,T1.lib)>0) then 1
      end=1
order by  T1.flag, T1.pair, T1.lettre

Salut Escartefigue.

La colonne "flag" est une colonne calculée qui peut prendre les valeurs 1, 2, 3, 4, 5, ...
Je me suis arrêté dans mon exemple à 1 et 2 mais on peut imaginer que la liste des valeurs autorisées soit très longue.

Vu que le résultat du select est trié sur la colonne calculée "flag", j'aurai toujours en premier la valeur que je cherche à extraire.
Avec un CTE, comme dans l'exemple que j'ai donné en utilisant le SGBDR FireBird, je sais le faire facilement.
Sauf que MySql ne connait pas le CTE et il faut trouver une solution de remplacement.

Ton exemple ne fonctionne pas car le résultat doit extraire les lignes ayant le flag le plus petit.
Or ce que tu extrais, ce sont uniquement que les lignes ayant "flag=1".
Du coup, si je n'ai pas de lignes ayant "flag=1" mais tout au contraire des lignes ayant "flag=2", je n'aurai rien à l'affichage.

J'ai une solution qui nécessite de dupliquer deux fois l'extraction des lignes :
Code:

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 -------------- set @car='z' -------------- -------------- select *, mod(val,2) as pair, locate(@car, lib) as lettre, case when (mod(val,2) + locate(@car,lib)) = 0 then 1 else 2 end as flag from test having flag = (select min(flag) from ( select *, mod(val,2) as pair, locate(@car, lib) as lettre, case when (mod(val,2) + locate(@car,lib)) = 0 then 1 else 2 end as flag from test order by flag, pair, lettre ) as x) order by flag, pair, lettre -------------- +----+-----+------+------+--------+------+ | id | val | lib | pair | lettre | flag | +----+-----+------+------+--------+------+ | 1 | 6 | six | 0 | 0 | 1 | | 3 | 8 | huit | 0 | 0 | 1 | +----+-----+------+------+--------+------+
Ne peut-on pas faire plus simple ?

@+

la CTE que tu as donnée est équivalentes à :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT *
FROM
  (SELECT id,
          val,
          lib,
          mod(val,2) AS pair,
          position('i' IN lib) AS lettre,
          CASE
              WHEN (mod(val,2) + position('i' IN lib)) = 0 THEN 1
              ELSE 2
          END AS flag
   FROM test
   ORDER BY flag,
            pair,
            lettre) tab
WHERE flag =
    (SELECT FIRST 1 flag
     FROM tab
     ORDER BY flag);

Que dit MySQmerde de ça ?

Pour informations, tranformation des fonctions en Transact SQL à part, ça marche dans SQL Server

A +

Salut SQLPRO.

"First 1", c'est pour FireBird. En Mysql; il faut mettre "Limit 1".
J'ai corrigé et je l'ai exécuté :

Code:

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
--------------
SELECT *
FROM
  (SELECT id,
          val,
          lib,
          mod(val,2) AS pair,
          position('i' IN lib) AS lettre,
          CASE
              WHEN (mod(val,2) + position('i' IN lib)) = 0 THEN 1
              ELSE 2
          END AS flag
   FROM test
   ORDER BY flag,
            pair,
            lettre) tab
WHERE flag =
    (SELECT flag
     FROM tab
     ORDER BY flag
     LIMIT 1)
--------------
 
ERROR 1146 (42S02) at line 101: La table 'base.tab' n'existe pas
--------------

MySql ne sait pas où trouver la table "tab".

@+

19/01/2017, 20h49
escartefigue

Citation:

Envoyé par Artemus24

Salut Escartefigue.
La colonne "flag" est une colonne calculée qui peut prendre les valeurs 1, 2, 3, 4, 5, ...
Je me suis arrêté dans mon exemple à 1 et 2 mais on peut imaginer que la liste des valeurs autorisées soit très longue.

Arf
J'ai testé sur un tout autre SGBD et j'avais bien le résultat attendu, mais il est vrai avec seulement 2 valeurs possibles du flag
pour l'instant je n'ai pas de déplacement prévu sur un site MySQL

Bon je confirme MySQL est une grosse merde... Solution de contournement, pas testée sur MySQMerde (je ne pollue plus mes ordi professionnelle avec cette daube depuis qu'il m'a planté 3 portables;..)

Code:

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
 
DECLARE @car CHAR(1);
 
set @car='i';
 
select    *,
          val % 2 as pair,
          CHARINDEX(@car, lib) as lettre,
          case when val % 2 + CHARINDEX(@car,lib) = 0 then 1 else 2 end as flag
    from  test
WHERE NOT EXISTS(SELECT * FROM test WHERE case when val % 2 + CHARINDEX(@car,lib) = 0 then 1 else 2 end = 1)
UNION ALL
select    *,
          val % 2 as pair,
          CHARINDEX(@car, lib) as lettre,
          case when val % 2 + CHARINDEX(@car,lib) = 0 then 1 else 2 end as flag
    from  test
WHERE     case when val % 2 + CHARINDEX(@car,lib) = 0 then 1 else 2 end = 2
AND EXISTS(SELECT * FROM test WHERE case when val % 2 + CHARINDEX(@car,lib) = 0 then 1 else 2 end = 1)
order by  flag, pair, lettre
 
set @car='z'
 
select    *,
          val % 2 as pair,
          CHARINDEX(@car, lib) as lettre,
          case when val % 2 + CHARINDEX(@car,lib) = 0 then 1 else 2 end as flag
    from  test
WHERE NOT EXISTS(SELECT * FROM test WHERE case when val % 2 + CHARINDEX(@car,lib) = 0 then 1 else 2 end = 1)
UNION ALL
select    *,
          val % 2 as pair,
          CHARINDEX(@car, lib) as lettre,
          case when val % 2 + CHARINDEX(@car,lib) = 0 then 1 else 2 end as flag
    from  test
WHERE     case when val % 2 + CHARINDEX(@car,lib) = 0 then 1 else 2 end = 1
AND EXISTS(SELECT * FROM test WHERE case when val % 2 + CHARINDEX(@car,lib) = 0 then 1 else 2 end = 1)
order by  flag, pair, lettre

Adapte là à MySQmerde sachant que % c'est modulo et CHARINDEX c'est LOCATE

Résultats sous Microsoft SQL Server :

Code:

1
2
3
4
5
6
7
8
9
10
id          val    lib     pair        lettre      flag
----------- ------ ------- ----------- ----------- -----------
1           6      six     0           2           2
3           8      huit    0           3           2
4           16     seize   0           3           2
5           11     onze    1           0           2
2           9      neuf    1           0           2
6           13     treize  1           4           2
 
(6*ligne(s) affectée(s))

Code:

1
2
3
4
5
6
id          val    lib     pair        lettre      flag
----------- ------ ------- ----------- ----------- -----------
1           6      six     0           0           1
3           8      huit    0           0           1
 
(2*ligne(s) affectée(s))

A +

Salut à tous.

Citation:

Envoyé par SQLPRO

la CTE que tu as donnée est équivalentes à :

Sous Microsoft SQL Server, voici ce que j'aurai écrit :

Code:

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
-- ===========
-- Paramétrage
-- ===========
 
SET NOCOUNT ON
 
-- ==================
-- Lien vers Database
-- ==================
 
use tempdb
 
Le contexte de la base de données a changé*; il est maintenant 'tempdb'.
 
-- =====================
-- Création Table 'test'
-- =====================
 
IF OBJECT_ID(N'dbo.test', N'U') IS NOT NULL
    DROP TABLE dbo.test
 
create table dbo.test (
  id   smallint  identity(1, 1) not null,
  val  smallint                 not null,
  lib  varchar(10)              not null,
  constraint pk_test_id   primary key clustered (id)
)
 
insert into test (val,lib) values  (6,'six'),(9,'neuf'),(8,'huit'),(6,'seize'),(11,'onze'),(13,'treize')
 
select * from dbo.test
 
id     val    lib
------ ------ ----------
     1      6 six
     2      9 neuf
     3      8 huit
     4      6 seize
     5     11 onze
     6     13 treize
 
-- ============
-- Requête N° 1
-- ============
 
with temp as ( select  *,
                       val % 2 as pair,
                       CHARINDEX('i', lib) as lettre,
                       case when val % 2 + CHARINDEX('i',lib) = 0 then 1 else 2 end as flag
                 from  test)
select    *
    from  temp
   where  flag = (select min(flag) from temp)
order by  flag, pair, lettre
 
id     val    lib        pair        lettre      flag
------ ------ ---------- ----------- ----------- -----------
     1      6 six                  0           2           2
     3      8 huit                 0           3           2
     4      6 seize                0           3           2
     5     11 onze                 1           0           2
     2      9 neuf                 1           0           2
     6     13 treize               1           4           2
 
-- ============
-- Requête N° 2
-- ============
 
with temp as ( select  *,
                       val % 2 as pair,
                       CHARINDEX('z', lib) as lettre,
                       case when val % 2 + CHARINDEX('z',lib) = 0 then 1 else 2 end as flag
                 from  test)
select    *
    from  temp
   where  flag = (select min(flag) from temp)
order by  flag, pair, lettre
 
id     val    lib        pair        lettre      flag
------ ------ ---------- ----------- ----------- -----------
     1      6 six                  0           0           1
     3      8 huit                 0           0           1
 
Appuyez sur une touche pour continuer...

C'est très similaire à ce que j'ai fait sous FireBird.

@+

21/01/2017, 11h09
SQLpro

L'as tu essayée ? Il n'y a pas de CTE ni de sous requête qui se mord la queue.

A +
21/01/2017, 17h04
Artemus24

Salut SQLPRO.

Oui, je l'ai essayé, mais je trouve cela lourd.
Je préfère encore la solution que j'ai donné dans mon message #7.
Ce qui ne me plait pas dans ma solution, c'est la duplication du bloc d'extraction des données.

Je pensais qu'il existait un truc, une astuce sous MySql, pouvait facilement résoudre ce problème.
J'entends par là, une écriture moins lourde, moins contraignante.

Ce que je recherche, ne concerne pas la faisabilité, mais la simplicité où si vous préférez, réduire la requête en taille.

@+
23/01/2017, 16h32
SQLpro

Dans ce cas, passe par une vue.

A +

Salut SQLPRO.

Citation:

Envoyé par SQLPRO

Dans ce cas, passe par une vue.

Bon sang, c'est bien sûr ! :D

Je n'ai même pas pensé à la vue. Et voilà le résultat :

Code:

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
--------------
create view `vue` as
    select    *,
              mod(val,2) as pair,
              locate('z', lib) as lettre,
              case when (mod(val,2) + locate('z',lib)) = 0 then 1 else 2 end as flag
        from  test
    order by  flag, pair, lettre
--------------
 
--------------
select * from vue
--------------
 
+----+-----+--------+------+--------+------+
| id | val | lib    | pair | lettre | flag |
+----+-----+--------+------+--------+------+
|  1 |   6 | six    |    0 |      0 |    1 |
|  3 |   8 | huit   |    0 |      0 |    1 |
|  4 |  16 | seize  |    0 |      4 |    2 |
|  2 |   9 | neuf   |    1 |      0 |    2 |
|  5 |  11 | onze   |    1 |      3 |    2 |
|  6 |  13 | treize |    1 |      5 |    2 |
+----+-----+--------+------+--------+------+
--------------
select  *
  from  vue
 where  flag = (select min(flag) from vue)
--------------
 
+----+-----+------+------+--------+------+
| id | val | lib  | pair | lettre | flag |
+----+-----+------+------+--------+------+
|  1 |   6 | six  |    0 |      0 |    1 |
|  3 |   8 | huit |    0 |      0 |    1 |
+----+-----+------+------+--------+------+

Merci SQLPRO ! :D
@+