Bonjour,
J'aimerais savoir s'il existe une écriture plus optimisée et plus lisible de la requête suivante ?
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
SELECT MAX(ind0), MAX(ind1), MAX(ind2), MAX(ind3) FROM(
(SELECT `ind` AS 'ind0', 0 AS 'ind1', 0 AS 'ind2', 0 AS 'ind3'
  FROM `table` 
 WHERE cond1
   AND cond2 
   AND cond3=0 
 ORDER BY Field1 DESC
 LIMIT 1)
UNION
(SELECT 0 AS 'ind0', `ind` AS 'ind1', 0 AS 'ind2', 0 AS 'ind3'
  FROM `table` 
 WHERE cond1
   AND cond2 
   AND cond3=1 
 ORDER BY Field1 DESC
 LIMIT 1)
UNION
(SELECT 0 AS 'ind0', 0 AS 'ind1', `ind` AS 'ind2', 0 AS 'ind3'
  FROM `table` 
 WHERE cond1
   AND cond2 
   AND cond3=2 
 ORDER BY Field1 DESC
 LIMIT 1)
UNION
(SELECT 0 AS 'ind0', 0 AS 'ind1', 0 AS 'ind2', `ind` AS 'ind3'
  FROM `table` 
 WHERE cond1
   AND cond2 
   AND cond3=3 
 ORDER BY Field1 DESC
 LIMIT 1)
) AS temp;
cond1 et cond2 étant redondants.

J'ai tenté avec
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
CREATE TEMPORARY TABLE tmp SELECT * FROM `table` WHERE cond1 AND cond2;
SELECT MAX(ind0), MAX(ind1), MAX(ind2), MAX(ind3) FROM(
(SELECT `ind` AS 'ind0', 0 AS 'ind1', 0 AS 'ind2', 0 AS 'ind3'
  FROM tmp
 WHERE cond3=0
 ORDER BY Field1 DESC
 LIMIT 1)
UNION
(SELECT 0 AS 'ind0', `ind` AS 'ind1', 0 AS 'ind2', 0 AS 'ind3'
  FROM tmp
 WHERE cond3=1
 ORDER BY Field1 DESC
 LIMIT 1)
UNION
(SELECT 0 AS 'ind0', 0 AS 'ind1', `ind` AS 'ind2', 0 AS 'ind3'
  FROM tmp
 WHERE cond3=2
 ORDER BY Field1 DESC
 LIMIT 1)
UNION
(SELECT 0 AS 'ind0', 0 AS 'ind1', 0 AS 'ind2', `ind` AS 'ind3'
  FROM tmp
 WHERE cond3=3
 ORDER BY Field1 DESC
 LIMIT 1)
) AS temp;
mais comme je tente d"utiliser plusieurs fois la table temporaire, j'ai en retour
debug : #1137 - Can't reopen table: 'tmp'{"success":false,"error":"#1137 - Can't reopen table: 'tmp'<\/div>"}
Il y a-t-il une alternative ?

Merci de vos avis.