select compliqué sur une table

01 janv. 2007, 02:12

salut
je sollicite votre aide pour faire un select plutôt compliqué sur une table 'temptableau' qui a cette structure (exemple) :

Code : Tout sélectionner

+----+-------+-----+
| id | lotid | cat |
+----+-------+-----+
|  1 |     9 |   0 |
|  2 |     7 |   0 |
|  3 |     9 |   1 |
|  4 |     6 |   1 |
+----+-------+-----+

je voudrai donc sélectionner les 'lotid' qui apparaissent dans toutes les catégories 'cat'
cad ici sélectionner '9' car il apparait dans la catégorie 0 et 1
sachant que le nombre de catégories peut aller jusqu'à 6, il ne faut sélectionner que les lotid présents dans toutes les 'cat'
moi je seche complètement alors si quelqu'un pouvait m'aider... merci d'avance !!

01 janv. 2007, 02:49

Pour ceux qui veulent tester avec une table:

Code : Tout sélectionner

CREATE TABLE `lot` (
  `id` int(11) NOT NULL auto_increment,
  `lotid` int(11) NOT NULL,
  `cat` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
);

INSERT INTO `lot` (`id`, `lotid`, `cat`) VALUES 
(1, 9, 0),
(2, 7, 0),
(3, 9, 1),
(4, 6, 1);

01 janv. 2007, 04:31

merci Xenon_54 (pour l'édit aussi) quoi que j'aurai dû y penser...

01 janv. 2007, 04:55

Je teste à l'instant quelques solutions. La veille du jour de l'an, il y a quelques gens chez moi donc

01 janv. 2007, 05:05

Et je présume que tu connais par avance toutes les valeurs de "cat" ? (sinon c'est plus ou moins impossible, plutôt plus que moins d'ailleurs)

Le plus efficace sera d'utiliser une jointure par valeur de cat. Par exemple

Code : Tout sélectionner

SELECT t1.lotid
  FROM temptableau t1
  JOIN temptableau t2 USING (lotid)
 WHERE t1.cat = 0
   AND t2.cat = 1

Et si tu as une troisième valeur de "cat" :

Code : Tout sélectionner

SELECT t1.lotid
  FROM temptableau t1
  JOIN temptableau t2 USING (lotid)
  JOIN temptableau t3 USING (lotid)
 WHERE t1.cat = 0
   AND t2.cat = 1
   AND t3.cat = 3

Au niveau des indices, je pense que le plus performant est d'avoir un index UNIQUE sur (lotid,cat) (meilleure cardinalité) mais essaie aussi (cat,lotid) pour voir.

01 janv. 2007, 05:13

Bonsoir,
Ceci devrait fonctionner (à tester)

SELECT DISTINCT(lotid) FROM lot
WHERE cat IN (SELECT DISTINCT(cat) FROM lot) 
GROUP BY cat;

01 janv. 2007, 05:19

oui on connait toutes les cat
je teste tout ca et je vous tiens au courant
merci a tous et s il y a d autres solutions n hésitez surtout pas !

ps :

Je teste à l'instant quelques solutions. La veille du jour de l'an, il y a quelques gens chez moi donc

et moi à faire de la programmation ce jour là, à 4h du mat ! c'est à devenir

01 janv. 2007, 05:26

Je crois que cela revient au même problème qu'un autre topic que nous avions, sois retourner tous les éléments classés par catégories. Seulement dans ce cas, il faut en plus comparer avec toutes les catégories possibles afin de voir leur présence.

Je me permet alors de te rediriger avec ta première solution qui était l'utilisation des variables tableaux. Encore une fois, j'ai eu de la Venn:

<?php
function array_intersect_all($array) {
    
    // Vérification du type
    if ('array' != gettype($array)) {
        return array();
    }
    
    if (count($array) < 2) {
        return array();
    }
    
    // Premier élément utilisé pour comparaison
    $results = array_shift($array);
    
    // Tant que c'est pas NULL en retour
    while (NULL != ($subarray = array_shift($array))) {
        
        // Élément bizarre?
        if ('array' != gettype($subarray)) {
            continue;
        }
        
        $results = array_intersect($results, $subarray);
    }
    
    return $results;
}

$array = array();
$array[1][] = 'Valeur1';
$array[1][] = 'Valeur2';
$array[1][] = 'Valeur3';
$array[1][] = 'Valeur4';
$array[2][] = 'Valeur5';
$array[2][] = 'Valeur1';
$array[2][] = 'Valeur6';
$array[2][] = 'Valeur3';
$array[3][] = 'Valeur7';
$array[3][] = 'Valeur2';
$array[3][] = 'Valeur3';
$array[3][] = 'Valeur1';
$array[4][] = 'Valeur8';
$array[4][] = 'Valeur9';
$array[4][] = 'Valeur1';
$array[4][] = 'Valeur3';

$results = array_intersect_all($array);

print_r($results);
?>

01 janv. 2007, 05:32

j'ai eu de la Venn

=D>
je teste et je répond merci !

01 janv. 2007, 05:40

Heu plus j'y pense et plus ça n'a aucun sens.

Est-ce que tu veux récupérer les lotid qui sont dans le plus de catégories possibles ou alors qui sont dans toutes les catégories? (1 à 6 dans ton cas)

J'ai ajouté quelques éléments à la table afin d'améliorer les tests.

Code : Tout sélectionner

CREATE TABLE `lot` (
  `id` int(11) NOT NULL auto_increment,
  `lotid` int(11) NOT NULL,
  `cat` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM;

INSERT INTO `lot` (`id`, `lotid`, `cat`) VALUES 
(1, 10, 0),
(2, 70, 0),
(3, 10, 1),
(4, 20, 1),
(5, 70, 2),
(6, 70, 1),
(7, 70, 3),
(8, 20, 2);

Code : Tout sélectionner

+----+-------+-----+
| id | lotid | cat |
+----+-------+-----+
|  1 |    10 |   0 |
|  2 |    70 |   0 |
|  3 |    10 |   1 |
|  4 |    20 |   1 |
|  5 |    70 |   2 |
|  6 |    70 |   1 |
|  7 |    70 |   3 |
|  8 |    20 |   2 |
+----+-------+-----+

Dans ce cas, seul 70 devrait être retourné non?

01 janv. 2007, 05:41

@Ajoloca: je déconseille cette méthode dans la mesure où elle crée une table temporaire et potentiellement 2 (une pour la table dérivée et une pour évaluer le DISTINCT de la requête extérieur). La solution que je propose offrira systématiquement de meilleures performances dans la mesure où le résultat est intégralement calculé à partir de l'index.

Si je devais établir une échelle de performances, je dirais que la solution des jointures multiples est excellente, la version en sous-requête mauvaise et la version PHP catastrophique (je dis ça en toute amitié hein

). Maintenant, si tu as 10 à 100 enregistrements dans ta table on s'en fiche, mais si tu en as plusieurs milliers alors tu vas rapidement comprendre ce que je veux dire

Edit: j'avais lu le post d'Ajoloca en diagonale, à priori ce que tu veux faire ressemble plutôt à (que je continue à déconseiller mais dont les performances pourraient passer de "mauvaises" à "pas bonnes"

)

Code : Tout sélectionner

SELECT lotid, COUNT(DISTINCT cat) AS cnt
FROM lot 
GROUP BY lotid
HAVING cnt = (SELECT COUNT(DISTINCT cat) FROM lot)

01 janv. 2007, 05:47

Oui, tu as tout à fait raison Hubert Roksor.

Cependant ta solution ne semble pas fonctionner...
Sur la table de test que j'ai posté il y a quelques minutes. Ceci:

Code : Tout sélectionner

SELECT t1.lotid
  FROM lot t1
  JOIN lot t2 USING (lotid, cat)
  JOIN lot t3 USING (lotid, cat)
  JOIN lot t4 USING (lotid, cat)
 WHERE t1.cat = 0
   AND t2.cat = 1
   AND t3.cat = 2
   AND t4.cat = 3

ne retourne rien. Alors que ça devrait être 70.

01 janv. 2007, 05:51

C'est normal, c'est parce que j'ai foiré la condition de jointure

J'ai corrigé mon post précédent, la condition doit être (lotid), pas (lotid,cat).

01 janv. 2007, 05:53

Est-ce que tu veux récupérer les lotid qui sont dans le plus de catégories possibles ou alors qui sont dans toutes les catégories? (1 à 6 dans ton cas)

selon les cas 'formulaire de recherche en amont), on peut avoir 2 ou plusieurs catégories
on cherche ainsi les lotid figurant dans toutes les catégories !

01 janv. 2007, 06:00

Je viens de tester la solution corrigée d'Ajoloca et MySQL 5.0 s'en sort carrément bien. Si on n'utilise pas de sous-requête on obtient même de meilleures solutions qu'avec des auto-jointures pour peu que les données soient réparties un tant soit peu uniformément.

Donc à priori la solution ultime serait

Code : Tout sélectionner

SELECT COUNT(DISTINCT cat) AS cnt
FROM lot

suivi de

Code : Tout sélectionner

SELECT lotid, COUNT(DISTINCT cat) AS cnt
FROM lot
GROUP BY lotid
HAVING cnt = 4

(où le 4 provient de la requête précédente)

select compliqué sur une table

Qui est en ligne