Utiliser des tables temporaires décrites dans une requête

**xounet** · 27/06/2021, 11h11

Bonjour,
Essayant d'éviter de créer des lignes ou tables qui me semblent pas nécessaires, j'ai choisi de créer des tables temporaires directement dans une requête en utilisant WITH (Mysql8).
Cela fonctionne bien :

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
 
SET @num=0;SET @num2=0;
                                                            WITH t_a_c as (SELECT (SELECT @num2:=@num2+1) as num1,T_e,a_i,a_s FROM 
                                                            								(SELECT * FROM 
                                                            									(SELECT -2 as T_e 
                                                            										UNION ALL SELECT -4 
 
                                                            									) A,
                                                            									(SELECT 0 as a_i,100 as a_s 
                                                            										UNION ALL SELECT 100,1000 
                                                                                                 	UNION ALL SELECT 1000,5000 
                                                                                                 	UNION ALL SELECT 5000,20000 
 
                                                            									) B ORDER BY T_e DESC ) TEMP),
                                                            corresp as (SELECT (SELECT @num:=@num+1) as num,-9 as valeur 
                                                            			UNION ALL SELECT (SELECT @num:=@num+1),10 
                                                            			UNION ALL SELECT (SELECT @num:=@num+1),65 
                                                            			UNION ALL SELECT (SELECT @num:=@num+1) ,2 
 
 
                                                            			UNION ALL SELECT (SELECT @num:=@num+1) ,-5
                                                            			UNION ALL SELECT (SELECT @num:=@num+1) ,78
                                                            			UNION ALL SELECT (SELECT @num:=@num+1) ,6
                                                            			UNION ALL SELECT (SELECT @num:=@num+1) ,0
 
 
                                                            			),
                                                            T_finale as (SELECT * FROM t_a_c,corresp WHERE corresp.num=t_a_c.num1 ORDER BY T_e)
                                                            SELECT * FROM T_finale

'jobtiens :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
+------+-----+------+-------+------+--------+
| num1 | T_e | a_i  | a_s   | num  | valeur |
+------+-----+------+-------+------+--------+
|    5 |  -4 |    0 |   100 |    5 |     -5 |
|    6 |  -4 |  100 |  1000 |    6 |     78 |
|    7 |  -4 | 1000 |  5000 |    7 |      6 |
|    8 |  -4 | 5000 | 20000 |    8 |      0 |
|    1 |  -2 |    0 |   100 |    1 |     -9 |
|    2 |  -2 |  100 |  1000 |    2 |     10 |
|    3 |  -2 | 1000 |  5000 |    3 |     65 |
|    4 |  -2 | 5000 | 20000 |    4 |      2 |
+------+-----+------+-------+------+--------+
8 rows in set, 9 warnings (0.00 sec)

Très bien

Sur cette dernière table T_finale, je souhaite faire une sélection, je modifie donc la dernière ligne logiquement :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
 SELECT * FROM T_finale WHERE a_i<=4500 AND a_s>=4500

Et cela donne :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
+------+-----+------+------+------+--------+
| num1 | T_e | a_i  | a_s  | num  | valeur |
+------+-----+------+------+------+--------+
|    2 |  -4 | 1000 | 5000 |    2 |     10 |
|    1 |  -2 | 1000 | 5000 |    1 |     -9 |
+------+-----+------+------+------+--------+

alors que d'après le résulat précédent je souhaiterais obtenir les résultats de la ligne num1=7 et num1=3
Ce qui ne me convient pas c'est que num a changé de valeur en ajoutant une condition sur a_i et a_s

Par contre si la condition concerne num :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT * FROM T_finale WHERE num=7;

Le résultat est bon :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
+------+-----+------+------+------+--------+
| num1 | T_e | a_i  | a_s  | num  | valeur |
+------+-----+------+------+------+--------+
|    7 |  -4 | 1000 | 5000 |    7 |      6 |
+------+-----+------+------+------+--------+

Comment rendre num non variable dans la requête ?

Précision j'ai vu qu'"apparemment ma déclaration de variable utilisateur est obsolète peut être que c'est cela l'origine du résultat inattendu, comment faire autrement ?

**xounet** · 27/06/2021, 14h15

Finalement après diverses recherches, j'en conclus que MYSQL 8 veut inciter les utilisateurs à utiliser des variables définies dans les requêtes et propose de nouvelles fonctions comme ROW_NUMBER() qui permettent de réaliser de façon efficace des numérotations de résultats, en appliquant ces méthodes :

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
SET @num=0;SET @num2=0;
                                                            WITH t_a_c as (SELECT ROW_NUMBER() OVER (ORDER BY T_e DESC) as num1,T_e,a_i,a_s FROM 
                                                            								(SELECT * FROM 
                                                            									(SELECT -2 as T_e 
                                                            										UNION ALL SELECT -4 
 
                                                            									) A,
                                                            									(SELECT 0 as a_i,100 as a_s 
                                                            										UNION ALL SELECT 100,1000 
                                                                                                 	UNION ALL SELECT 1000,5000 
                                                                                                 	UNION ALL SELECT 5000,20000 
 
                                                            									) B ORDER BY T_e DESC ) TEMP),
                                                            corresp as (SELECT (SELECT @num:=@num+1) as num,-9 as valeur 
                                                            			UNION ALL SELECT (SELECT @num:=@num+1),10 
                                                            			UNION ALL SELECT (SELECT @num:=@num+1),65 
                                                            			UNION ALL SELECT (SELECT @num:=@num+1) ,2 
 
 
                                                            			UNION ALL SELECT (SELECT @num:=@num+1) ,-5
                                                            			UNION ALL SELECT (SELECT @num:=@num+1) ,78
                                                            			UNION ALL SELECT (SELECT @num:=@num+1) ,6
                                                            			UNION ALL SELECT (SELECT @num:=@num+1) ,0
 
 
                                                            			),
                                                            T_finale as (SELECT * FROM t_a_c,corresp WHERE corresp.num=t_a_c.num1)
                                                            /*SELECT * FROM T_finale*/
                                                            SELECT * FROM T_finale WHERE a_i<=4500 AND a_s>=4500;

Ce qui me donne le bon résultat.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
+------+-----+------+------+------+--------+
| num1 | T_e | a_i  | a_s  | num  | valeur |
+------+-----+------+------+------+--------+
|    7 |  -4 | 1000 | 5000 |    7 |      6 |
|    3 |  -2 | 1000 | 5000 |    3 |     65 |
+------+-----+------+------+------+--------+
2 rows in set, 8 warnings (0.00 sec)

Mais je me demande tout de même comment remplacer l'indispensable

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT @ma_variable:=@ma_variable+xx

que l'on pouvait caser partout dans une requête longue de plusieurs kilomètres..., puisque cela ne sera plus supporté dans les prochaines versions de MYSQL 8 ... ?

**Artemus24** · 28/06/2021, 01h01

Salut xounet.

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
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
drop view if exists `vue`
--------------
 
--------------
create view `vue` as
with t1 as (            SELECT -2 as T_e
              UNION ALL SELECT -4
           ),
 
     t2 as (            SELECT    0 as a_i,  100 as a_s
              UNION ALL SELECT  100,        1000
              UNION ALL SELECT 1000,        5000
              UNION ALL SELECT 5000,       20000
           ),
 
     t3 as (            SELECT  ROW_NUMBER() OVER(order by T_e desc, a_i) as num,
                                T_e, a_i, a_s
                          FROM  t1
                    CROSS JOIN  t2
           ),
 
     t4 as (            SELECT 1 as num2,  -9 as valeur
              UNION ALL SELECT 2,          10
              UNION ALL SELECT 3,          65
              UNION ALL SELECT 4,           2
              UNION ALL SELECT 5,          -5
              UNION ALL SELECT 6,          78
              UNION ALL SELECT 7,           6
              UNION ALL SELECT 8,           0
           ),
 
     t5 as (            SELECT x.num,
                               x.T_e,
                               x.a_i,
                               x.a_s,
                               y.valeur
                          FROM t3 as x
                    INNER JOIN t4 as y
                            ON y.num2 = x.num
           )
 
select * from t5
--------------
 
--------------
select * from `vue`
--------------
 
+-----+-----+------+-------+--------+
| num | T_e | a_i  | a_s   | valeur |
+-----+-----+------+-------+--------+
|   1 |  -2 |    0 |   100 |     -9 |
|   2 |  -2 |  100 |  1000 |     10 |
|   3 |  -2 | 1000 |  5000 |     65 |
|   4 |  -2 | 5000 | 20000 |      2 |
|   5 |  -4 |    0 |   100 |     -5 |
|   6 |  -4 |  100 |  1000 |     78 |
|   7 |  -4 | 1000 |  5000 |      6 |
|   8 |  -4 | 5000 | 20000 |      0 |
+-----+-----+------+-------+--------+
--------------
select * from `vue` where a_i<=4500 AND a_s>=4500
--------------
 
+-----+-----+------+------+--------+
| num | T_e | a_i  | a_s  | valeur |
+-----+-----+------+------+--------+
|   3 |  -2 | 1000 | 5000 |     65 |
|   7 |  -4 | 1000 | 5000 |      6 |
+-----+-----+------+------+--------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

Je pense que cela doit correspondre à ce que vous attendez comme résultat.

@+

**escartefigue** · 28/06/2021, 08h54

Bonjour,

Envoyé par xounet

Bonjour,
Essayant d'éviter de créer des lignes ou tables qui me semblent pas nécessaires, j'ai choisi de créer des tables temporaires directement dans une requête en utilisant WITH (Mysql8).

Une table temporaire se crée avec l'ordre DDL CREATE TEMPORARY TABLE.
La clause WITH permet de définir une Common Table Expression (CTE) qui est une table dérivée externalisée. Ce n'est pas une table temporaire.

**xounet** · 28/06/2021, 14h56

Envoyé par Artemus24

Salut xounet.

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
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
drop view if exists `vue`
--------------
 
--------------
create view `vue` as
with t1 as (            SELECT -2 as T_e
              UNION ALL SELECT -4
           ),
 
     t2 as (            SELECT    0 as a_i,  100 as a_s
              UNION ALL SELECT  100,        1000
              UNION ALL SELECT 1000,        5000
              UNION ALL SELECT 5000,       20000
           ),
 
     t3 as (            SELECT  ROW_NUMBER() OVER(order by T_e desc, a_i) as num,
                                T_e, a_i, a_s
                          FROM  t1
                    CROSS JOIN  t2
           ),
 
     t4 as (            SELECT 1 as num2,  -9 as valeur
              UNION ALL SELECT 2,          10
              UNION ALL SELECT 3,          65
              UNION ALL SELECT 4,           2
              UNION ALL SELECT 5,          -5
              UNION ALL SELECT 6,          78
              UNION ALL SELECT 7,           6
              UNION ALL SELECT 8,           0
           ),
 
     t5 as (            SELECT x.num,
                               x.T_e,
                               x.a_i,
                               x.a_s,
                               y.valeur
                          FROM t3 as x
                    INNER JOIN t4 as y
                            ON y.num2 = x.num
           )
 
select * from t5
--------------
 
--------------
select * from `vue`
--------------
 
+-----+-----+------+-------+--------+
| num | T_e | a_i  | a_s   | valeur |
+-----+-----+------+-------+--------+
|   1 |  -2 |    0 |   100 |     -9 |
|   2 |  -2 |  100 |  1000 |     10 |
|   3 |  -2 | 1000 |  5000 |     65 |
|   4 |  -2 | 5000 | 20000 |      2 |
|   5 |  -4 |    0 |   100 |     -5 |
|   6 |  -4 |  100 |  1000 |     78 |
|   7 |  -4 | 1000 |  5000 |      6 |
|   8 |  -4 | 5000 | 20000 |      0 |
+-----+-----+------+-------+--------+
--------------
select * from `vue` where a_i<=4500 AND a_s>=4500
--------------
 
+-----+-----+------+------+--------+
| num | T_e | a_i  | a_s  | valeur |
+-----+-----+------+------+--------+
|   3 |  -2 | 1000 | 5000 |     65 |
|   7 |  -4 | 1000 | 5000 |      6 |
+-----+-----+------+------+--------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

Je pense que cela doit correspondre à ce que vous attendez comme résultat.

@+

Oui ça donne bien le résultat. Est ce que ta version donnerait de meilleures performances pour des centaines de lignes ?

**Artemus24** · 08/07/2021, 10h04

Salut à tous.

@ xounet : je ne sais pas, il faut tester.

@+

Utiliser des tables temporaires décrites dans une requête

Requêtes MySQL

Discussions similaires

Partager

Partager