[MySQL] Requête apparement simple, mais ...

Recherche :

Mot : Pseudo : Filtrer
Bas de page
Auteur	Sujet : [MySQL] Requête apparement simple, mais ...

El_gringo

Bonjour tout le monde,

J'ai une table 'PERSONNES', qui se présente comme suit :

+---------------------------------------------------------------------------------------+
| Nom | Sans logement | Isolement social | Sans emploi | Mucovicidose | Pieds plats |
|-------+---------------+------------------+-------------+----------------+-------------|
|Hector | true | true | false | false | true |
|Roger | false | false | true | false | false |
|Simone | false | true | true | true | false |
+---------------------------------------------------------------------------------------+

je cherche à écrire une requête SQL qui me permettrait la chose suivante :
Sélectionner les noms de personnes ayant au moins 3 problèmes connus.
Donc, dans mon exemple, la requête devrait me sortir Simone et Hector, mais pas Roger.

Vous auriez des idées ?

EDIT : cette table n'est qu'un exemple. En réalité je n'ai pas 5 mais une trentaine de 'problèmes connus' (champs booléens), il est donc exclus d'énumérer toutes les combinaisons possibles à grand coups de 'OR' ...

Message édité par El_gringo le 31-01-2007 à 14:11:24

Publicité

dwogsi

Défaillance cérébrale...

Oui parce que, si je ne me trompe pas, ça ferais ~~2^5 combinaisons possibles, soit plus de 3000...~~ 2^30 combinaisons possibles.

Bref. Et si tu les met en int(1) (par exemle) et en considérant que 1 vaut true et 0 false, tu peux faire la somme de tout les champs et vérifier si elle est supérieur à 3.

Edit: Simple proposition, ya certainement mieux.

Message édité par dwogsi le 31-01-2007 à 14:52:30

El_gringo

Tient, c'est pas con ton truc. Mais ces champs sont des booléens et je ne peux pas me permettre de les changer comme ça. ça impliquerait trop de changements par ailleurs.

anapajari

s/travail/glanding on hfr/gs;

cast les en char, concat les tous et dessus fais un regex ou un str_index (en fonction de ton sgbd)
edit: je tiens à dire que cette methode sera particulièrement lente,

Message cité 1 fois
Message édité par anapajari le 31-01-2007 à 15:05:26

rufo

Pas me confondre avec Lycos!

Requête apparement simple, mais ... avec un MCD mal conçu, ça devient une grosse galère Je trouve que la solution de dwogsi est la plus simple à mettre en oeuvre si tu ne veux pas modifier ton MCD.

Perso, j'aurais fait 3 tables
- table Personnes
- table Problemes
- table liant les personnes à leurs problèmes

Après, y'a pu qu'à faire un COUNT() du nb de pb par personne et ne garder que ceux en ayant >= 3...

Message cité 1 fois

rufo

Pas me confondre avec Lycos!

anapajari a écrit :

cast les en char, concat les tous et dessus fais un regex ou un str_index (en fonction de ton sgbd)
edit: je tiens à dire que cette methode sera particulièrement lente,

et pourquoi pas les caster en int à ce moment là. Comme ça, on retombe sur la 1ère solution proposée...Ca serait plus simple et plus rapide je pense...

Message cité 1 fois

anapajari

s/travail/glanding on hfr/gs;

rufo a écrit :

et pourquoi pas les caster en int à ce moment là. Comme ça, on retombe sur la 1ère solution proposée...Ca serait plus simple et plus rapide je pense...

oui ça marche aussi et c'est plus simple

Message édité par anapajari le 31-01-2007 à 15:10:31

El_gringo

rufo a écrit :

T'as très certainement raison. Ton modèle seraient sans doute beaucoup + souple. Mais c'est une petite base de données que j'ai fait rapidement, avec Base (l'équivalent d'Access, pour OpenOffice). Ta méthode aurait été beaucoup plus compliqué à interfacer avec Writer (pour l'interface de saisie de données). Bref, ...
Merci quand même.

Et caster des champs à la volée (bool => tinyint) dans une requète MySQL, vous croyez que c'est possible ? (si oui, comment ?)

anapajari

s/travail/glanding on hfr/gs;

http://dev.mysql.com/doc/refman/5. [...] tions.html
t'as pas tinyint mais signed fera très bien l'affaire

MagicBuzz

mysql sait pas faire un cast entre un bool et un int ?
même Access sait faire

Publicité

MagicBuzz

sinon, stocker en colonne des données qui devraient être sous forme de lignes, c'est mal.

drop table personnes;

et tu recommences avec un modèle propre

Message cité 1 fois
Message édité par MagicBuzz le 31-01-2007 à 16:40:00

El_gringo

le problème c'est que dès qu'il y a un champ NULL, le total est NULL. J'éspérais que ce cast transformerait les NULL en 0. Apparement c'est pas le cas. vous voyez comment je pourrais faire ? (sachant que je dois garder ces NULL en base)

Message cité 2 fois

El_gringo

MagicBuzz a écrit :

sinon, stocker en colonne des données qui devraient être sous forme de lignes, c'est mal.

drop table personnes;

et tu recommences avec un modèle propre

Justement, je me disais que ce topic manquait d'intégristes... :sarcastic:
Plus sérieusement, regarde qqs posts au dessus, j'explique pourquoi j'avais fait ce choix. J'ai peut être eu tord, mais là, très franchement, j'en suis plus au moment ou je vais refondre tout mon MCD. C'est un petit extra que je fais bénévolement pour une asso. Là, je cherche à faire un truc stable et fonctionnel rapidement. Quitte à refondre tout ça un peu plus proprement après.

Message cité 2 fois

rufo

Pas me confondre avec Lycos!

El_gringo a écrit :

Justement, je me disais que ce topic manquait d'intégristes... :sarcastic:
Plus sérieusement, regarde qqs posts au dessus, j'explique pourquoi j'avais fait ce choix. J'ai peut être eu tord, mais là, très franchement, j'en suis plus au moment ou je vais refondre tout mon MCD. C'est un petit extra que je fais bénévolement pour une asso. Là, je cherche à faire un truc stable et fonctionnel rapidement. Quitte à refondre tout ça un peu plus proprement après.

Si t'es prêt à refondre ton appli après, pourquoi pas le faire maintenant? Avec ton MCD, tu risques de perdre plein de temps à chaque fois que tu vas vouloir extraire telle ou telle info, alors qu'avec un modèle propre, ça te prendrait 2 mins

ps : pas de chance pour toi, MagicBuzz viens de débarquer sur ton topic...t'es cuit :whistle:

Message cité 1 fois

anapajari

s/travail/glanding on hfr/gs;

El_gringo a écrit :

ifnull ou coalesce sur chacune de tes colonnes!

MagicBuzz

El_gringo a écrit :

Avec SQL Server 2005, il y a une option afin d'indiquer comment doivent se comporter les calculs avec NULL.
Peut-être existe-t-il avec MySQL. Il y a de grandes chances.

Sinon, ifnull() est une solution plus propre car explicite, et évite de modifier le comportement du SGBD de façon globale.

Message édité par MagicBuzz le 31-01-2007 à 19:09:50

MagicBuzz

El_gringo a écrit :

J'ai peut être eu tord, mais là, très franchement, j'en suis plus au moment ou je vais refondre tout mon MCD. C'est un petit extra que je fais bénévolement pour une asso. Là, je cherche à faire un truc stable et fonctionnel rapidement.

Si tu as MySQL 5, alors tu peux faire très simplement ceci, en reprenant la doc que j'ai écrit :
http://forum.hardware.fr/forum2.ph [...] 0#t1447148

create view personnes
(
nom, sans_logement, isolement_social, sans_emploi, mucovicidose, pieds_plats
)
as
select pe.nom, a1.affection, a2.affection, a3.affection, a4.affection, a5.affection
from personnes_new pe
left outer join affections a1 on a1.id_personne = pe.id and a1.id_probleme = 1
left outer join affections a2 on a2.id_personne = pe.id and a2.id_probleme = 2
left outer join affections a3 on a3.id_personne = pe.id and a3.id_probleme = 3
left outer join affections a4 on a4.id_personne = pe.id and a4.id_probleme = 4
left outer join affections a5 on a5.id_personne = pe.id and a5.id_probleme = 5;

go

create trigger personnes_ins
on dbo.personnes
instead of insert
as
-- Inserted values
declare @nom as varchar(50);
declare @sans_logement as bit;
declare @isolement_social as bit;
declare @sans_emploi as bit;
declare @mucovicidose as bit;
declare @pieds_plats as bit;

-- Internal values
declare @pers_id as numeric;
begin
declare cur_ins cursor
for
select nom, sans_logement, isolement_social, sans_emploi, mucovicidose, pieds_plats
from inserted;

open cur_ins;

fetch next from cur_ins
into @nom, @sans_logement, @isolement_social, @sans_emploi, @mucovicidose, @pieds_plats;

begin transaction;

while @@FETCH_STATUS = 0
begin
if exists (select null from personnes_new where nom = @nom)
begin
rollback;
raiserror('Can''t duplicate name "%s". This is a limitation from old model.', 16, 1, @nom);
return;
end;

insert into personnes_new (nom) values (@nom);
set @pers_id = scope_identity();

if @sans_logement is not null
begin
insert into affections (id_personne, id_probleme, affection) values (@pers_id, 1, @sans_logement);
end;

if @isolement_social is not null
begin
insert into affections (id_personne, id_probleme, affection) values (@pers_id, 2, @isolement_social);
end;

if @sans_emploi is not null
begin
insert into affections (id_personne, id_probleme, affection) values (@pers_id, 3, @sans_emploi);
end;

if @mucovicidose is not null
begin
insert into affections (id_personne, id_probleme, affection) values (@pers_id, 4, @mucovicidose);
end;

if @pieds_plats is not null
begin
insert into affections (id_personne, id_probleme, affection) values (@pers_id, 5, @pieds_plats);
end;

fetch next from cur_ins
into @nom, @sans_logement, @isolement_social, @sans_emploi, @mucovicidose, @pieds_plats;
end;

commit;

close cur_ins;
deallocate cur_ins;
end;

go

create trigger personnes_upd
on dbo.personnes
instead of update
as
-- Inserted values
declare @i_nom as varchar(50);
declare @i_sans_logement as bit;
declare @i_isolement_social as bit;
declare @i_sans_emploi as bit;
declare @i_mucovicidose as bit;
declare @i_pieds_plats as bit;

-- Deleted values
declare @d_nom as varchar(50);
declare @d_sans_logement as bit;
declare @d_isolement_social as bit;
declare @d_sans_emploi as bit;
declare @d_mucovicidose as bit;
declare @d_pieds_plats as bit;

-- Internal values
declare @pers_id as numeric;
begin
declare cur_ins cursor
for
select nom, sans_logement, isolement_social, sans_emploi, mucovicidose, pieds_plats
from inserted;

declare cur_del cursor
for
select nom, sans_logement, isolement_social, sans_emploi, mucovicidose, pieds_plats
from deleted;

open cur_ins;
open cur_del;

fetch next from cur_ins
into @i_nom, @i_sans_logement, @i_isolement_social, @i_sans_emploi, @i_mucovicidose, @i_pieds_plats;

fetch next from cur_del
into @d_nom, @d_sans_logement, @d_isolement_social, @d_sans_emploi, @d_mucovicidose, @d_pieds_plats;

begin transaction;

while @@FETCH_STATUS = 0
begin
select @pers_id = id
from personnes_new
where nom = @d_nom;

if (@i_nom != @d_nom)
begin
if exists (select null from personnes_new where nom = @i_nom)
begin
rollback;
raiserror('Can''t duplicate name "%s". This is a limitation from old model.', 16, 1, @i_nom);
return;
end;

update personnes_new set nom = @i_nom
where nom = @d_nom;
end;

if @i_sans_logement is not null
begin
if @d_sans_logement is not null
begin
update affections
set affection = @i_sans_logement
where id_personne = @pers_id
and id_probleme = 1;
end;
else
begin
insert into affections (id_personne, id_probleme, affection)
values (@pers_id, 1, @i_sans_logement);
end;
end;
else
begin
delete affections
where id_personne = @pers_id
and id_probleme = 1;
end;

if @i_isolement_social is not null
begin
if @d_isolement_social is not null
begin
update affections
set affection = @i_isolement_social
where id_personne = @pers_id
and id_probleme = 2;
end;
else
begin
insert into affections (id_personne, id_probleme, affection)
values (@pers_id, 2, @i_isolement_social);
end;
end;
else
begin
delete affections
where id_personne = @pers_id
and id_probleme = 2;
end;

if @i_sans_emploi is not null
begin
if @d_sans_emploi is not null
begin
update affections
set affection = @i_sans_emploi
where id_personne = @pers_id
and id_probleme = 3;
end;
else
begin
insert into affections (id_personne, id_probleme, affection)
values (@pers_id, 3, @i_sans_emploi);
end;
end;
else
begin
delete affections
where id_personne = @pers_id
and id_probleme = 3;
end;

if @i_mucovicidose is not null
begin
if @d_mucovicidose is not null
begin
update affections
set affection = @i_mucovicidose
where id_personne = @pers_id
and id_probleme = 4;
end;
else
begin
insert into affections (id_personne, id_probleme, affection)
values (@pers_id, 4, @i_mucovicidose);
end;
end;
else
begin
delete affections
where id_personne = @pers_id
and id_probleme = 4;
end;

if @i_pieds_plats is not null
begin
if @d_pieds_plats is not null
begin
update affections
set affection = @i_pieds_plats
where id_personne = @pers_id
and id_probleme = 5;
end;
else
begin
insert into affections (id_personne, id_probleme, affection)
values (@pers_id, 5, @i_pieds_plats);
end;
end;
else
begin
delete affections
where id_personne = @pers_id
and id_probleme = 5;
end;

fetch next from cur_ins
into @i_nom, @i_sans_logement, @i_isolement_social, @i_sans_emploi, @i_mucovicidose, @i_pieds_plats;

fetch next from cur_del
into @d_nom, @d_sans_logement, @d_isolement_social, @d_sans_emploi, @d_mucovicidose, @d_pieds_plats;
end;

commit;

close cur_ins;
deallocate cur_ins;

close cur_del;
deallocate cur_del;
end;

go

create trigger personnes_del
on dbo.personnes
instead of delete
as
-- Deleted values
declare @nom as varchar(50);

-- Internal values
declare @pers_id as numeric;
begin
declare cur_del cursor
for
select nom
from deleted;

open cur_del;

fetch next from cur_del
into @nom;

begin transaction;

while @@FETCH_STATUS = 0
begin
select @pers_id = id
from personnes_new
where nom = @nom;

delete affections
where id_personne = @pers_id;

delete personnes_new
where id = @pers_id;

fetch next from cur_del
into @nom;
end;

commit;

close cur_del;
deallocate cur_del;
end;

go

Et ça marche :

print('Affichage des anciennes données.');
select * from personnes_old;
go

print('Suppression des éventuelles données de test.');
delete personnes;
go

print('Recopie avec succès les lignes de l''ancienne table dans la nouvelle structure.');
insert into personnes
select * from personnes_old;
go

print('Efface la ligne d''Hector.');
delete personnes
where sans_logement = 1;
go

print('Plante, car Roger, Simone et Test existent déjà. Hector n''est donc pas récupéré.');
insert into personnes
select * from personnes_old;
go

print('Renomme Test, et modifie avec succès les flags en question.');
update personnes
set nom = 'Toto',
sans_logement = 1,
sans_emploi = null,
pieds_plats = 0
where nom = 'Test';
go

print('Duplique Roger sous le nom d''Hector.');
insert into personnes (nom, sans_logement, isolement_social, sans_emploi, mucovicidose, pieds_plats)
select 'Hector', sans_logement, isolement_social, sans_emploi, mucovicidose, pieds_plats
from personnes
where nom = 'Roger';
go

print('Contrôle de la nouvelle structure au final.');
select *
from personnes;
go

print('Tout ça pour obtenir la liste des gens qui ont au moins 3 problèmes.')
select p.id, p.nom, count(a.affection) Problemes
from personnes_new p inner join affections a on a.id_personne = p.id and a.affection = 1
group by p.id, p.nom
having count(a.affection) >= 3;
go

Résultat :

Affichage des anciennes données.
nom sans_logement isolement_social sans_emploi mucovicidose pieds_plats
-------------------------------------------------- ------------- ---------------- ----------- ------------ -----------
Hector 1 1 0 0 1
Roger 0 0 1 0 0
Simone 0 1 1 1 0
Test NULL NULL 0 1 1

(4 row(s) affected)

Suppression des éventuelles données de test.

(5 row(s) affected)

(1 row(s) affected)

(5 row(s) affected)

(1 row(s) affected)

(4 row(s) affected)

(1 row(s) affected)

(5 row(s) affected)

(1 row(s) affected)

(4 row(s) affected)
Recopie avec succès les lignes de l'ancienne table dans la nouvelle structure.

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(4 row(s) affected)
Efface la ligne d'Hector.

(5 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
Plante, car Roger, Simone et Test existent déjà. Hector n'est donc pas récupéré.

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
Msg 50000, Level 16, State 1, Procedure personnes_ins, Line 34
Can't duplicate name "Roger". This is a limitation from old model.
Msg 3609, Level 16, State 1, Line 3
La transaction s'est terminée dans le déclencheur. Le lot a été abandonné.
Renomme Test, et modifie avec succès les flags en question.

(1 row(s) affected)

(1 row(s) affected)

(0 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
Duplique Roger sous le nom d'Hector.

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
Contrôle de la nouvelle structure au final.
nom sans_logement isolement_social sans_emploi mucovicidose pieds_plats
-------------------------------------------------- ------------- ---------------- ----------- ------------ -----------
Roger 0 0 1 0 0
Simone 0 1 1 1 0
Toto 1 NULL NULL 1 0
Hector 0 0 1 0 0

(4 row(s) affected)

Tout ça pour obtenir la liste des gens qui ont au moins 3 problèmes.
id nom Problemes
--------------------------------------- -------------------------------------------------- -----------
43 Simone 3

(1 row(s) affected)

=> Voilà. Simple comme choux, et maintenant, tu as une nouvelle structure "propre" et 100% compatible avec l'ancien modèle...

Message édité par MagicBuzz le 31-01-2007 à 21:12:06

El_gringo

rufo a écrit :

Je peux difficilement le faire maintenant parce qu'on à un 1er impératif de délais pour établir ces stats cette année.

Je peux pas te laisser critiquer MagicBuzz, il a été génial sur ce coup là ! Merci beaucoup MagicBuzz et aux autres aussi. ce topic est une mine d'or pour moi.

Message cité 1 fois

rufo

Pas me confondre avec Lycos!

El_gringo a écrit :

Je peux difficilement le faire maintenant parce qu'on à un 1er impératif de délais pour établir ces stats cette année.

Je peux pas te laisser critiquer MagicBuzz, il a été génial sur ce coup là ! Merci beaucoup MagicBuzz et aux autres aussi. ce topic est une mine d'or pour moi.

T'avais compris que c'était une boutade, hein? Il m'a moi-même grandement aidé y'a pas si longtemps sur un pb de BD...

Message cité 1 fois

MagicBuzz

ceci dit, j'ai jamais été foutu de résoudre ton problème

Message cité 1 fois

El_gringo

rufo a écrit :

T'avais compris que c'était une boutade, hein? Il m'a moi-même grandement aidé y'a pas si longtemps sur un pb de BD...

Oui oui j'avais compris. C'en était une aussi quand j'me suis posé comme son défenseur !

MagicBuzz

y'a des baffes qui se perdent moi je vous dis

El_gringo

rufo

Pas me confondre avec Lycos!

MagicBuzz a écrit :

ceci dit, j'ai jamais été foutu de résoudre ton problème

si, en qq sorte. Tu m'avais dit que ce que je voulais faire n'était pas possible avec le MCD que j'avais. C'était donc pas la peine de chercher plus loin

Publicité

FORUM HardWare.fr

Programmation

SQL/NoSQL

[MySQL] Requête apparement simple, mais ...

Sujets relatifs
Petit probleme sur une requete avec date	requete de recherche dans access
Proteger une ligne d'une table contre la suppression(mysql)	pb sur requete simple :(
[PHP] Classe MYSQL impossible d'accéder aux attributs de la classe	Configuration MySQL
Nombre d'occurences => toujours besoin d'aide	Administration site: CSS/PHP/Mysql,etc...
Application Windows et bdd MySQL sur internet = SOAP ?
Plus de sujets relatifs à : [MySQL] Requête apparement simple, mais ...

Page générée en 0.071 secondes