✔ SELECT drei jeder ID

[Tommy57]

Hallo,

ich muss gerade ein Statement schreiben, wo ich von allen verkauften Autos, immer genau die drei beliebtesten Baujahre brauche. Das folgende Statement würde mir alle zurückgeben, also müsste es noch irgendwie modifiziert werden.

SELECT
    autoid,
    baujahr,
    count(baujahr) as summary
FROM
    (
        SELECT 1 AS id, 1 AS autoid, 1972 AS baujahr
        UNION SELECT 2 AS id, 1 AS autoid, 1973 AS baujahr
        UNION SELECT 3 AS id, 1 AS autoid, 1973 AS baujahr
        UNION SELECT 4 AS id, 1 AS autoid, 1973 AS baujahr
        UNION SELECT 5 AS id, 1 AS autoid, 1974 AS baujahr
        UNION SELECT 6 AS id, 1 AS autoid, 1974 AS baujahr
        UNION SELECT 7 AS id, 1 AS autoid, 1974 AS baujahr
        UNION SELECT 8 AS id, 1 AS autoid, 1974 AS baujahr
        UNION SELECT 9 AS id, 1 AS autoid, 1976 AS baujahr
        UNION SELECT 10 AS id, 2 AS autoid, 1974 AS baujahr
        UNION SELECT 11 AS id, 2 AS autoid, 1974 AS baujahr
        UNION SELECT 12 AS id, 2 AS autoid, 1974 AS baujahr
        UNION SELECT 13 AS id, 2 AS autoid, 1977 AS baujahr
        UNION SELECT 14 AS id, 2 AS autoid, 1977 AS baujahr
        UNION SELECT 15 AS id, 2 AS autoid, 1978 AS baujahr
    ) AS t1
GROUP BY
    autoid,
    baujahr

Gruß, Tommy

 

[Yaslaw]

Ist ein wenig Tricki, aber mit Variablen durchaus lösbar.

Zuerst das ganze noch nach der autoid und dem summary sortieren. Dann jede Resultatzeile mit der Vorzeile vergleichen und hochzählen solange die autoid gleich bleibt. Wenn die sich ändert, den Zähler zurücksetzen. Am Schluss das ganze noch filtern

SELECT
	autoid,
	baujahr,
	summary
FROM
	(
		SELECT
			@counter := IF(autoid = @last_autoid, @counter+1, 1) AS counter,
			-- Hier ist die Anzahl der auszugebenden Resultate. In deinem Beispiel 3
			(@counter <= 3) AS flag,
			@last_autoid := autoid AS autoid,
			baujahr,
			summary
		FROM
			-- Die Variablen initialisieren
			(SELECT @last_autoid := 0, @counter :=0) AS vars,
			(
				SELECT
				    autoid,
				    baujahr,
				    COUNT(*) AS summary
				FROM
				    (
				        SELECT 1 AS id, 1 AS autoid, 1972 AS baujahr
				        UNION SELECT 2 AS id, 1 AS autoid, 1973 AS baujahr
				        UNION SELECT 3 AS id, 1 AS autoid, 1973 AS baujahr
				        UNION SELECT 4 AS id, 1 AS autoid, 1973 AS baujahr
				        UNION SELECT 5 AS id, 1 AS autoid, 1974 AS baujahr
				        UNION SELECT 6 AS id, 1 AS autoid, 1974 AS baujahr
				        UNION SELECT 7 AS id, 1 AS autoid, 1974 AS baujahr
				        UNION SELECT 8 AS id, 1 AS autoid, 1974 AS baujahr
				        UNION SELECT 9 AS id, 1 AS autoid, 1976 AS baujahr
				        UNION SELECT 10 AS id, 2 AS autoid, 1974 AS baujahr
				        UNION SELECT 11 AS id, 2 AS autoid, 1974 AS baujahr
				        UNION SELECT 12 AS id, 2 AS autoid, 1974 AS baujahr
				        UNION SELECT 13 AS id, 2 AS autoid, 1977 AS baujahr
				        UNION SELECT 14 AS id, 2 AS autoid, 1977 AS baujahr
				        UNION SELECT 15 AS id, 2 AS autoid, 1978 AS baujahr
				    ) AS t1
				GROUP BY
				    autoid,
				    baujahr
				ORDER BY
					autoid,
					COUNT(*) DESC
			) AS myData
	) AS flagged_fata
WHERE
	flag = 1

 

[Tommy57]

Wooow. Danke, das werde ich gleich mal testen und umschreiben.

 

[Yaslaw]

Achjam noch so als wichtige Info zum Umbau. Beim folgenden Teil ist die Reihenfolge wichtig. Ja nicht verdrehen

            @counter := IF(autoid = @last_autoid, @counter+1, 1) AS counter,
            -- Hier ist die Anzahl der auszugebenden Resultate. In deinem Beispiel 3
            (@counter <= 3) AS flag,
            @last_autoid := autoid AS autoid,

Naja, mindestesn muss der counter als erstes kommen. flag und autoid kannst du miteinander vertauschen