Anfänger SQL-Abfrage

[matze9999]

Hallo,
ich bin gerade dabei SQL zu lernen und möchte nun eine Abfrage erstellen, bei der mir die Mannschaft (und zwar soll nur die Mannschaft ohne die Anzahl der Spieler) ausgegeben wird mit den meisten ausländischen Spielern.

Hier mein Datenbankschema:

CREATE TABLE team(
team id INTEGER PRIMARY KEY,
name TEXT,
sponsor TEXT
) ;
CREATE TABLE s p i e l e r (
s p i e l e r i d INTEGER PRIMARY KEY,
vorname TEXT,
nachname TEXT,
g eha l t INTEGER,
n a t i o n a l i t a e t TEXT,
geburtsdatum DATE,
team id INTEGER,
FOREIGN KEY ( team id ) REFERENCES team( team id )
) ;

Mit den folgenden Datensätzen:

INSERT INTO "team" VALUES (NULL, "Werder Bremen", "Wiesenhof");
INSERT INTO "team" VALUES (NULL, "Borussia Dortmund", "Evonik");
INSERT INTO "team" VALUES (NULL, "Bayern Muenchen", "T-Mobile");
INSERT INTO "team" VALUES (NULL, "Bayer Leverkusen", "SunPower");
INSERT INTO "team" VALUES (NULL, "Hamburger SV", "Fly Emirates");

INSERT INTO "spieler" VALUES(NULL, "Robert", "Lewandowski", 125000, "polnisch", "1988-08-21", 2);
INSERT INTO "spieler" VALUES(NULL, "Marco", "Reus", 333000, "deutsch", "1989-05-31", 2);
INSERT INTO "spieler" VALUES(NULL, "Aaron", "Hunt", 368000, "deutsch", "1986-09-04", 1);
INSERT INTO "spieler" VALUES(NULL, "Rene", "Adler", 223000, "deutsch", "1985-01-15", 5);
INSERT INTO "spieler" VALUES(NULL, "Dennis", "Aogo", 210000, "deutsch", "1987-01-14", 5);
INSERT INTO "spieler" VALUES(NULL, "Franck", "Ribery", 1080123, "franzoesisch", "1983-04-07", 3);
INSERT INTO "spieler" VALUES(NULL, "Javi", "Martinez", 500000, "spanisch", "1988-09-02", 3);
INSERT INTO "spieler" VALUES(NULL, "Son", "Heung-min", 500000, "suedkoreanisch", "1992-07-08", 5);


Mein erster Vorschlag wäre:
select name1 from(
select name1, max(anzahl) from (
select name as name1, count(spieler_id) as anzahl from team t1
join spieler s1 on t1.team_id = s1.team_id
where nationalitaet !='deutsch'
group by t1.name));

Allerdings wird mir hier komischerweise, was ich überhaupt nicht verstehe, Hamburger SV ausgegeben obwohl der Subselect, den ich fett markiert habe, Bayern München und 2 für die Count-Fkt. ausgibt.
Wäre super, wenn mir jemand eine funktionierende Anweisung sagen könnte und mir verraten könnte warum meine Abfrage so arbeitet, wie sie arbeitet.

Vielen Dank

 

[akretschmer]

Hallo,
ich bin gerade dabei SQL zu lernen und möchte nun eine Abfrage erstellen, bei der mir die Mannschaft (und zwar soll nur die Mannschaft ohne die Anzahl der Spieler) ausgegeben wird mit den meisten ausländischen Spielern.

Hier mein Datenbankschema:

CREATE TABLE team(
team id INTEGER PRIMARY KEY,
name TEXT,
sponsor TEXT
) ;
CREATE TABLE s p i e l e r (
s p i e l e r i d INTEGER PRIMARY KEY,
vorname TEXT,
nachname TEXT,
g eha l t INTEGER,
n a t i o n a l i t a e t TEXT,
geburtsdatum DATE,
team id INTEGER,
FOREIGN KEY ( team id ) REFERENCES team( team id )
) ;

Mit den folgenden Datensätzen:

INSERT INTO "team" VALUES (NULL, "Werder Bremen", "Wiesenhof");
INSERT INTO "team" VALUES (NULL, "Borussia Dortmund", "Evonik");
INSERT INTO "team" VALUES (NULL, "Bayern Muenchen", "T-Mobile");
INSERT INTO "team" VALUES (NULL, "Bayer Leverkusen", "SunPower");
INSERT INTO "team" VALUES (NULL, "Hamburger SV", "Fly Emirates");

INSERT INTO "spieler" VALUES(NULL, "Robert", "Lewandowski", 125000, "polnisch", "1988-08-21", 2);
INSERT INTO "spieler" VALUES(NULL, "Marco", "Reus", 333000, "deutsch", "1989-05-31", 2);
INSERT INTO "spieler" VALUES(NULL, "Aaron", "Hunt", 368000, "deutsch", "1986-09-04", 1);
INSERT INTO "spieler" VALUES(NULL, "Rene", "Adler", 223000, "deutsch", "1985-01-15", 5);
INSERT INTO "spieler" VALUES(NULL, "Dennis", "Aogo", 210000, "deutsch", "1987-01-14", 5);
INSERT INTO "spieler" VALUES(NULL, "Franck", "Ribery", 1080123, "franzoesisch", "1983-04-07", 3);
INSERT INTO "spieler" VALUES(NULL, "Javi", "Martinez", 500000, "spanisch", "1988-09-02", 3);
INSERT INTO "spieler" VALUES(NULL, "Son", "Heung-min", 500000, "suedkoreanisch", "1992-07-08", 5);


Mein erster Vorschlag wäre:
select name1 from(
select name1, max(anzahl) from (
select name as name1, count(spieler_id) as anzahl from team t1
join spieler s1 on t1.team_id = s1.team_id
where nationalitaet !='deutsch'
group by t1.name));

Allerdings wird mir hier komischerweise, was ich überhaupt nicht verstehe, Hamburger SV ausgegeben obwohl der Subselect, den ich fett markiert habe, Bayern München und 2 für die Count-Fkt. ausgibt.
Wäre super, wenn mir jemand eine funktionierende Anweisung sagen könnte und mir verraten könnte warum meine Abfrage so arbeitet, wie sie arbeitet.

Vielen Dank

Ich verwende ja kein MySQL, aber warum setzt Du teamid und spielerid auf NULL?

test=*# select * from team;
 teamid |       name        |   sponsor
--------+-------------------+--------------
      1 | Werder Bremen     | Wiesenhof
      2 | Borussia Dortmund | Evonik
      3 | Bayern Muenchen   | T-Mobile
      4 | Bayer Leverkusen  | SunPower
      5 | Hamburger SV      | Fly Emirates
(5 rows)

Time: 0,187 ms
test=*# select * from spieler;
 spielerid | vorname |  nachname   | gehalt  | nationalitaet  | geburtsdatum | teamid
-----------+---------+-------------+---------+----------------+--------------+--------
         1 | Robert  | Lewandowski |  125000 | polnisch       | 1988-08-21   |      2
         2 | Marco   | Reus        |  333000 | deutsch        | 1989-05-31   |      2
         3 | Aaron   | Hunt        |  368000 | deutsch        | 1986-09-04   |      1
         4 | Rene    | Adler       |  223000 | deutsch        | 1985-01-15   |      5
         5 | Dennis  | Aogo        |  210000 | deutsch        | 1987-01-14   |      5
         6 | Franck  | Ribery      | 1080123 | franzoesisch   | 1983-04-07   |      3
         7 | Javi    | Martinez    |  500000 | spanisch       | 1988-09-02   |      3
         8 | Son     | Heung-min   |  500000 | suedkoreanisch | 1992-07-08   |      5
(8 rows)

Time: 0,220 ms
test=*# select * from team right join (select teamid, count(nationalitaet) from spieler where nationalitaet != 'deutsch' group by teamid order by 2 desc limit1) x on team.teamid=x.teamid;
 teamid |      name       | sponsor  | teamid | count
--------+-----------------+----------+--------+-------
      3 | Bayern Muenchen | T-Mobile |      3 |     2
(1 row)

Mit PostgreSQL gemacht ;-)

Andreas

 

[Tommi]

Hallo matze9999,

ich habe einen ähnlichen Ansatz wie Andreas, nur dass ich auf ein Sub-Select verzichte.
Die Syntax ist zwar vom MS SQL Server, aber ich glaube, dass dich das ganz leicht auch auf MySQL umsetzen läßt:

SELECT TOP 1 T.[team id], T.name as Team,
COUNT(DISTINCT S.[spielerid]) as Anzahl
FROM #team T
LEFT OUTER JOIN #spieler S
   ON S.[team id]=T.[team id]
   AND S.[nationalitaet]='deutsch'
GROUP BY T.[team id], T.name
ORDER BY Anzahl DESC

(mich irritiert übrigens auch der Wert NULL für die ID beim INSERT. Ein PRIMARY KEY darf in der Regel nicht NULL sein! Evtl. ist das by MySQL anders? )

Viele Grüße,
Tommi