Hilfe bei einer MySQL Query (Joins, Group By, Order By)

[Frank]

Hallo zusammen,

Ich möchte ein häufiges Problem, welches ich bisher immer umständlich mit PHP gelöst habe, mit MySQL-Bordmitteln lösen. Und zwar gibt es zwei Tabellen.

  
-- ----------------------------  
-- Table structure for gruppen  
-- ----------------------------  
CREATE TABLE `gruppen` (  
  `group_id` int(10) NOT NULL auto_increment,  
  `group_name` varchar(100) collate latin1_general_ci default NULL,  
  PRIMARY KEY  (`group_id`)  
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;  
  
-- ----------------------------  
-- Records  
-- ----------------------------  
INSERT INTO `gruppen` VALUES ('1', 'Fernseher');  
INSERT INTO `gruppen` VALUES ('2', 'Radio');  
INSERT INTO `gruppen` VALUES ('3', 'Toaster');  
  
-- ----------------------------  
-- Table structure for artikel  
-- ----------------------------  
CREATE TABLE `artikel` (  
  `artikel_id` int(10) NOT NULL auto_increment,  
  `group_id` int(10) default NULL,  
  `artikel_name` varchar(100) collate latin1_general_ci default NULL,  
  `artikel_preis` varchar(5) collate latin1_general_ci default NULL,  
  PRIMARY KEY  (`artikel_id`)  
) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;  
  
-- ----------------------------  
-- Records  
-- ----------------------------  
INSERT INTO `artikel` VALUES ('1', '1', 'Fernseher A', '100');  
INSERT INTO `artikel` VALUES ('2', '1', 'Fernseher B', '200');  
INSERT INTO `artikel` VALUES ('3', '1', 'Fernseher C', '150');  
INSERT INTO `artikel` VALUES ('4', '2', 'Radio A', '25');  
INSERT INTO `artikel` VALUES ('5', '2', 'Radio B', '30');  
INSERT INTO `artikel` VALUES ('6', '2', 'Radio C', '40');  
INSERT INTO `artikel` VALUES ('7', '2', 'Radio D', '20');  
INSERT INTO `artikel` VALUES ('8', '3', 'Toaster A', '30');  
INSERT INTO `artikel` VALUES ('9', '3', 'Toaster B', '15');  
INSERT INTO `artikel` VALUES ('10', '3', 'Toaster C', '12');  
INSERT INTO `artikel` VALUES ('11', '3', 'Toaster D', '20');  

Ich suche jetzt eine Query für eine Liste mit den günstigsten Produkten. In der Liste darf aber jede Gruppe nur mit maximal 2 Artikeln aufgeführt sein.

Meine bisherige Lösung ist eine riesige mit Union zusammengeflickte Abfrage die von der Performance her gesehen absolut inakzeptabel ist. Bei 100-200 Datensätzen mag das noch gehen. In einem konkreten Projekt geht es aber um Tabellen mit 3Mio+ Datensätzen. Da kann so eine Query schon mal 15 Min dauern.

Daran beiße ich mir jetzt schon seit Wochen die Zähne aus, bekomme es aber ohne PHP einfach nicht hin. Ich hoffe Ihr könnt mir helfen bzw. einen Stoß in die richtige Richtung geben.

Viele Grüße
Frank

[Vinzenz Mai]

Hallo

---

-- Records

---

INSERT INTO artikel VALUES ('1', '1', 'Fernseher A', '100');
INSERT INTO artikel VALUES ('2', '1', 'Fernseher B', '200');
INSERT INTO artikel VALUES ('3', '1', 'Fernseher C', '150');
INSERT INTO artikel VALUES ('4', '2', 'Radio A', '25');
INSERT INTO artikel VALUES ('5', '2', 'Radio B', '30');
INSERT INTO artikel VALUES ('6', '2', 'Radio C', '40');
INSERT INTO artikel VALUES ('7', '2', 'Radio D', '20');
INSERT INTO artikel VALUES ('8', '3', 'Toaster A', '30');
INSERT INTO artikel VALUES ('9', '3', 'Toaster B', '15');
INSERT INTO artikel VALUES ('10', '3', 'Toaster C', '12');
INSERT INTO artikel VALUES ('11', '3', 'Toaster D', '20');
[/code]

Ich suche jetzt eine Query für eine Liste mit den günstigsten Produkten.

das ist ein simples Problem für eine korrelierte Unterabfrage (deren Performanz Du überprüfen müsstest). Wie das geht, kannst Du in diesem Archivposting nachlesen.

In der Liste darf aber jede Gruppe nur mit maximal 2 Artikeln aufgeführt sein.

Diesen Satz habe ich nicht verstanden. Was ist, wenn drei Artikel den gleichen günstigsten Preis haben?

Freundliche Grüße

Vinzenz

[Frank]

Hallo Vinzenz,

das ist ein simples Problem für eine korrelierte Unterabfrage (deren Performanz Du überprüfen müsstest). Wie das geht, kannst Du in diesem Archivposting nachlesen.

Dieses Posting habe ich auch eben gefunden und versuche es gerade umzusetzen. Mein Hauptproblem sind die zwei (oder mehr, das würde ich gern per Variable aus einem Formular übergeben) Artikel pro Gruppe. In dem von Dir genannten Posting wird ja nur ein Buch pro thema angezeigt.

In der Liste darf aber jede Gruppe nur mit maximal 2 Artikeln aufgeführt sein.

Diesen Satz habe ich nicht verstanden. Was ist, wenn drei Artikel den gleichen günstigsten Preis haben?

Das ist in diesem Fall nicht schlimm. Dann kommen die restlichen Artikel mit gleichem Preis nicht in die Liste. Die Tabellen und Werte sind auch frei erfunden. Spiegeln aber die Problematik am besten wieder und es lässt sich leichter erklären und probieren mit kleineren Tabellen.

Viele Grüße
Frank

[Frank]

Hier mein Versuch zwei Artikel zu bekommen:

  
SELECT  
 a1.artikel_id,  
 a1.group_id,  
 g1.group_name,  
 a1.artikel_name,  
 a1.artikel_preis  
FROM  
 artikel a1  
 INNER JOIN gruppen g1 ON (a1.group_id = g1.group_id)  
WHERE  
 artikel_preis = (  
  SELECT  
   a2.artikel_preis  
  FROM  
   artikel a2  
  WHERE  
   a1.group_id = a2.group_id  
  ORDER BY  
   a2.artikel_preis DESC  
  LIMIT 2  
 )  
ORDER BY  
 artikel_preis DESC  

Liefert aber eine Fehlermeldung: "Subquery returns more than 1 row".

[Vinzenz Mai]

Hallo

Hier mein Versuch zwei Artikel zu bekommen:

da Dich gleichpreisige Artikel ja nicht interessieren, ist das fast richtig:

SELECT
a1.artikel_id,

[...]

WHERE
artikel_preis = (

  
Ersetze = durch [IN()](http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_in).  
  
  
Freundliche Grüße  
  
Vinzenz

[Frank]

Ersetze = durch IN().

Das geht leider auch nicht: Subquery Errors
gleich der erste Punkt in der Liste.

[Vinzenz Mai]

Hallo

Das geht leider auch nicht: Subquery Errors

Verzichte auf LIMIT (was ich fast immer mache :-)).
Wie bekommst Du den zweitgrößten Wert?
Das ist der größte Wert, der kleiner ist als der größte Wert insgesamt.

Freundliche Grüße

Vinzenz

[Frank]

Hallo,

tausendfacher Dank an Dich. Ich habe es jetzt fast zusammen. Lediglich die letzte Sortierung will noch nicht. Ich habe jetzt pro Gruppe die beiden günstigsten Artikel/Preise. Nun muss die Liste nur noch nach dem Preis sortiert werden. Hier der Code:

  
SELECT  artikel_id,  
        group_id,  
        artikel_name,  
        artikel_preis  
FROM    artikel a1  
WHERE   artikel_preis IN  
        (SELECT a2.artikel_preis  
        FROM    artikel a2  
        WHERE   a1.group_id = a2.group_id  
        )  
    AND (artikel_preis =  
        (SELECT MIN(a3.artikel_preis)  
        FROM    artikel a3  
        WHERE   a1.group_id = a3.group_id  
        )  
     OR artikel_preis =  
        (SELECT MIN(a2.artikel_preis)  
        FROM    artikel a2  
        WHERE   artikel_preis >  
                (SELECT MIN(a3.artikel_preis)  
                FROM    artikel a3  
                WHERE   a1.group_id = a3.group_id  
                )  
            AND a1.group_id = a2.group_id  
        ) )  
ORDER BY artikel_preis ASC  

Viele Grüße
Frank

[Frank]

Hat sich erledigt, Vielen Dank nochmal an alle die geholfen haben. Hier hat mir der Tipp von EKKi geholfen. Ich habe den Datentyp für den Preis auf Float geändert. Jetzt funktioniert alles perfekt. Für die Vollständigkeit und andere mit dem selben Problem hier nochmal der komplette Code.

  
-- ----------------------------  
-- Table structure for artikel  
-- ----------------------------  
CREATE TABLE `artikel` (  
  `artikel_id` int(10) NOT NULL auto_increment,  
  `group_id` int(10) default NULL,  
  `artikel_name` varchar(100) collate latin1_general_ci default NULL,  
  `artikel_preis` float(10,2) default NULL,  
  PRIMARY KEY  (`artikel_id`)  
) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;  
  
-- ----------------------------  
-- Records  
-- ----------------------------  
INSERT INTO `artikel` VALUES ('1', '1', 'Fernseher A', '100.00');  
INSERT INTO `artikel` VALUES ('2', '1', 'Fernseher B', '200.00');  
INSERT INTO `artikel` VALUES ('3', '1', 'Fernseher C', '150.00');  
INSERT INTO `artikel` VALUES ('4', '2', 'Radio A', '25.00');  
INSERT INTO `artikel` VALUES ('5', '2', 'Radio B', '30.00');  
INSERT INTO `artikel` VALUES ('6', '2', 'Radio C', '40.00');  
INSERT INTO `artikel` VALUES ('7', '2', 'Radio D', '20.00');  
INSERT INTO `artikel` VALUES ('8', '3', 'Toaster A', '30.00');  
INSERT INTO `artikel` VALUES ('9', '3', 'Toaster B', '15.00');  
INSERT INTO `artikel` VALUES ('10', '3', 'Toaster C', '12.00');  
INSERT INTO `artikel` VALUES ('11', '3', 'Toaster D', '20.00');  
  
-- ----------------------------  
-- Table structure for gruppen  
-- ----------------------------  
CREATE TABLE `gruppen` (  
  `group_id` int(10) NOT NULL auto_increment,  
  `group_name` varchar(100) collate latin1_general_ci default NULL,  
  PRIMARY KEY  (`group_id`)  
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;  
  
-- ----------------------------  
-- Records  
-- ----------------------------  
INSERT INTO `gruppen` VALUES ('1', 'Fernseher');  
INSERT INTO `gruppen` VALUES ('2', 'Radio');  
INSERT INTO `gruppen` VALUES ('3', 'Toaster');  
  
SELECT  artikel_id,  
        group_id,  
        artikel_name,  
        artikel_preis  
FROM    artikel a1  
WHERE   artikel_preis IN  
        (SELECT a2.artikel_preis  
        FROM    artikel a2  
        WHERE   a1.group_id = a2.group_id  
        )  
    AND (artikel_preis =  
        (SELECT MIN(a3.artikel_preis)  
        FROM    artikel a3  
        WHERE   a1.group_id = a3.group_id  
        )  
     OR artikel_preis =  
        (SELECT MIN(a2.artikel_preis)  
        FROM    artikel a2  
        WHERE   artikel_preis >  
                (SELECT MIN(a3.artikel_preis)  
                FROM    artikel a3  
                WHERE   a1.group_id = a3.group_id  
                )  
            AND a1.group_id = a2.group_id  
        ) )  
ORDER BY artikel_preis ASC  

[Frank]

Subquery Errors

Hier habe ich noch ein Workaround für das LIMIT Problem gefunden. Funktioniert leider auch nicht. Damit bekomme ich nur noch ein einziges Ergebnis:

  
SET @i = 0;  
  
SELECT  
 a1.artikel_id,  
 a1.group_id,  
 a1.artikel_name,  
 a1.artikel_preis  
FROM  
 artikel a1  
WHERE  
 artikel_preis IN (  
  SELECT  
   a2.artikel_preis  
  FROM  
   artikel a2  
  WHERE  
   a1.group_id = a2.group_id AND  
   (@i:=(@i+1)) <= 2  
  ORDER BY  
   a2.artikel_preis ASC  
  )  
ORDER BY  
 artikel_preis ASC;  

Und auch hier habe ich noch einen Fehler gefunden:

  
SELECT  
 a1.artikel_id,  
 a1.group_id,  
 a1.artikel_name,  
 a1.artikel_preis  
FROM  
 artikel a1  
WHERE  
 artikel_preis = (  
  SELECT  
   MIN(a2.artikel_preis)  
  FROM  
   artikel a2  
  WHERE  
   a1.group_id = a2.group_id  
 )  
ORDER BY  
 artikel_preis DESC  

Hier stimmt die Sortierung nach artikel_preis nicht:

artikel_id | group_id | artikel_name | artikel_preis
-----------+----------+--------------+--------------
7          | 2        | Radio D      | 20
10         | 3        | Toaster C    | 12
1          | 1        | Fernseher A  | 100

[Frank]

Hie mein Versuch einer korrelierten Unterabfrage:

  
SELECT  
 a1.artikel_id,  
 a1.group_id,  
 a1.artikel_name,  
 a1.artikel_preis  
FROM  
 artikel a1  
WHERE  
 artikel_preis = (  
  SELECT  
   MIN(a2.artikel_preis)  
  FROM  
   artikel a2  
  WHERE  
   a1.group_id = a2.group_id  
 )  
ORDER BY  
 artikel_preis DESC  

Aber wie bekomme ich den zweiten Artikel mit in die Liste.

[EKKi]

Mahlzeit,

CREATE TABLE artikel (

artikel_id int(10) NOT NULL auto_increment,
  group_id int(10) default NULL,
  artikel_name varchar(100) collate latin1_general_ci default NULL,
  artikel_preis varchar(5) collate latin1_general_ci default NULL,
  PRIMARY KEY  (artikel_id)
) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

  
Das hat zwar jetzt nichts mit Deinem Problem zu tun, aber hältst Du es für eine gute Idee, einen Betrag als Text zu speichern? Wäre ein numerischer Wert (mit entsprechenden Nachkommastellen) nicht erheblich sinnvoller?  
  
  
MfG,  
EKKi  

-- 
sh:( fo:| ch:? rl:( br:> n4:~ ie:% mo:} va:) de:] zu:) fl:{ ss:) ls:& js:|

[Frank]

Hallo,

Das hat zwar jetzt nichts mit Deinem Problem zu tun, aber hältst Du es für eine gute Idee, einen Betrag als Text zu speichern? Wäre ein numerischer Wert (mit entsprechenden Nachkommastellen) nicht erheblich sinnvoller?

Da hast Du natürlich recht. Da es sich hierbei aber um ein Beispiel handelt ist das, glaub ich, nicht ganz so wichtig.

Aber trotzdem Danke für den Hinweis.

Viele Grüße
Frank