PDA

Vollständige Version anzeigen : T SQL Aufgaben


strausto
01.11.2007, 14:47
Wie von mindestens zwei Forum Usern gewünscht gibt es wöchentlich eine SQL Aufgabe mit der entsprechenden Lösung mit dem Ziel den Umgang mit (T)SQL zu üben.

Ein erstes Beispiel, von dem ich behaupten kann, dass ich mir die Lösung einfacher vorgestellt habe.

Szenario:

An einem Wettbewerb im Turmspringen nehmen vier Teilnehmer teil.
Insgesamt haben diese vier Springer die Möglichkeit in vier Runden ihr Können unter Beweis zu stellen. Sieger ist, wer in den vier Sprüngen die höchste Punktzahl erzielt, wobei die beste und die schlechteste Bewertung bzw. Runde herausfällt.

Beispiel


Springer Runde Punkte
A 1 10
A 2 8
A 3 9
A 4 9


In diesem Beispiel erzielte der Springer in der Runde eins und zwei die beste respektive schlechteste Berwertung. Übrig bleiben die Runden drei und vier. In der Summe kann sich der Springer über 18 ( 9 + 9 ) Punkte freuen.


Springer Runde Punkte
B 1 7
B 2 10
B 3 10
B 4 9


Springer B war etwas besser; in der Summe kann er sich 19 Punkte gutschreiben - damit steht Springer B an erste Stelle.

Nun konkret:


USE TEMPDB
GO
IF OBJECT_ID('TABLE1') IS NOT NULL
DROP TABLE TABLE1
CREATE TABLE TABLE1
(
[ID] INT IDENTITY(1,1) PRIMARY KEY,
[NAME] VARCHAR(50) NOT NULL,
[ROUND] TINYINT NOT NULL,
[POINTS] DEC(3,1)
)
INSERT TABLE1 ([NAME],[ROUND],[POINTS]) VALUES( 'A',1,7.8 )
INSERT TABLE1 ([NAME],[ROUND],[POINTS]) VALUES( 'B',1,5.0 )
INSERT TABLE1 ([NAME],[ROUND],[POINTS]) VALUES( 'C',1,7.7 )
INSERT TABLE1 ([NAME],[ROUND],[POINTS]) VALUES( 'D',1,9.7 )

INSERT TABLE1 ([NAME],[ROUND],[POINTS]) VALUES( 'A',2,9.8 )
INSERT TABLE1 ([NAME],[ROUND],[POINTS]) VALUES( 'B',2,0.0 )
INSERT TABLE1 ([NAME],[ROUND],[POINTS]) VALUES( 'C',2,2.5 )
INSERT TABLE1 ([NAME],[ROUND],[POINTS]) VALUES( 'D',2,10.0 )

INSERT TABLE1 ([NAME],[ROUND],[POINTS]) VALUES( 'A',3,7.8 )
INSERT TABLE1 ([NAME],[ROUND],[POINTS]) VALUES( 'B',3,5.8 )
INSERT TABLE1 ([NAME],[ROUND],[POINTS]) VALUES( 'C',3,9.7 )
INSERT TABLE1 ([NAME],[ROUND],[POINTS]) VALUES( 'D',3,9.7 )

INSERT TABLE1 ([NAME],[ROUND],[POINTS]) VALUES( 'A',4,7.8 )
INSERT TABLE1 ([NAME],[ROUND],[POINTS]) VALUES( 'B',4,6.0 )
INSERT TABLE1 ([NAME],[ROUND],[POINTS]) VALUES( 'C',4,8.6 )
INSERT TABLE1 ([NAME],[ROUND],[POINTS]) VALUES( 'D',4,10.0 )

SELECT * FROM TABLE1



Punktestand:


id Name Round Points
1 A 1 7.8
2 B 1 5.0
3 C 1 7.7
4 D 1 9.7
5 A 2 9.8
6 B 2 0.0
7 C 2 2.5
8 D 2 10.0
9 A 3 7.8
10 B 3 5.8
11 C 3 9.7
12 D 3 9.7
13 A 4 7.8
14 B 4 6.0
15 C 4 8.6
16 D 4 10.0


Wie haben sich die Springer platziert?

Das Ergebnis vorweg - ich habe das ganze nach Excel exportiert und sortiert um zu visualisieren:

id Name Round Points Points Summe Rank
1 A 1 7,8 x
9 A 3 7,8 7,8
13 A 4 7,8 7,8 15,6 3
5 A 2 9,8 x
6 B 2 0 x
2 B 1 5 5
10 B 3 5,8 5,8 10,8 4
14 B 4 6 x
7 C 2 2,5 x
3 C 1 7,7 7,7
15 C 4 8,6 8,6 16,3 2
11 C 3 9,7 x
4 D 1 9,7 x
12 D 3 9,7 9,7
8 D 2 10 10 19,7 1
16 D 4 10 x



Das von meinem SQL Statement erzeugte Resultset sieht folgendermassen aus:

NAME POINTS
D 19.7
C 16.3
A 15.6
B 10.8


Wer die Lösung hat kann diese ja posten ...

P.S. Natürlich ein(!) SQL Statement und ohne ohne DML ;)

Antwortet innerhalb einer Woche niemand werde ich meinen Vorschlag einfügen

Josef P.
01.11.2007, 14:58
Bin schon gespannt was da kommt. ;)
Meine SQL-Anweisung hat 81 Zeichen ohne Leerzeichen. :grins:

strausto
01.11.2007, 15:17
Hm! Da Du mir die Lösung bereits mitgeteilt hast, ist mir aufgegangen, dass es bei vier Runden zu einfach ist und das gar nicht in meinem Sinne war ;)

Danke für den indirekten Hinweis!

Daher versuche es doch bitte jetzt noch einmal
ES ZÄHLT JEWEILS NUR DER ZWEIT- UND DRITTBESTE SPRUNG!


USE TEMPDB
GO
IF OBJECT_ID('TABLE1') IS NOT NULL
DROP TABLE TABLE1
CREATE TABLE TABLE1
(
[ID] INT IDENTITY(1,1) PRIMARY KEY,
[NAME] VARCHAR(50) NOT NULL,
[ROUND] TINYINT NOT NULL,
[POINTS] DEC(3,1)
)
INSERT TABLE1 ([NAME],[ROUND],[POINTS]) VALUES( 'A',1,7.8 )
INSERT TABLE1 ([NAME],[ROUND],[POINTS]) VALUES( 'B',1,5.0 )
INSERT TABLE1 ([NAME],[ROUND],[POINTS]) VALUES( 'C',1,7.7 )
INSERT TABLE1 ([NAME],[ROUND],[POINTS]) VALUES( 'D',1,9.7 )

INSERT TABLE1 ([NAME],[ROUND],[POINTS]) VALUES( 'A',2,9.8 )
INSERT TABLE1 ([NAME],[ROUND],[POINTS]) VALUES( 'B',2,0.0 )
INSERT TABLE1 ([NAME],[ROUND],[POINTS]) VALUES( 'C',2,2.5 )
INSERT TABLE1 ([NAME],[ROUND],[POINTS]) VALUES( 'D',2,10.0 )

INSERT TABLE1 ([NAME],[ROUND],[POINTS]) VALUES( 'A',3,7.8 )
INSERT TABLE1 ([NAME],[ROUND],[POINTS]) VALUES( 'B',3,5.8 )
INSERT TABLE1 ([NAME],[ROUND],[POINTS]) VALUES( 'C',3,9.7 )
INSERT TABLE1 ([NAME],[ROUND],[POINTS]) VALUES( 'D',3,9.7 )

INSERT TABLE1 ([NAME],[ROUND],[POINTS]) VALUES( 'A',4,7.8 )
INSERT TABLE1 ([NAME],[ROUND],[POINTS]) VALUES( 'B',4,8.0 )
INSERT TABLE1 ([NAME],[ROUND],[POINTS]) VALUES( 'C',4,8.6 )
INSERT TABLE1 ([NAME],[ROUND],[POINTS]) VALUES( 'D',4,10.0 )

INSERT TABLE1 ([NAME],[ROUND],[POINTS]) VALUES( 'A',5,6.8 )
INSERT TABLE1 ([NAME],[ROUND],[POINTS]) VALUES( 'B',5,7.0 )
INSERT TABLE1 ([NAME],[ROUND],[POINTS]) VALUES( 'C',5,8.6 )
INSERT TABLE1 ([NAME],[ROUND],[POINTS]) VALUES( 'D',5,1.0 )




ID NAME ROUND POINTS
1 A 1 7.8
2 B 1 5.0
3 C 1 7.7
4 D 1 9.7
5 A 2 9.8
6 B 2 0.0
7 C 2 2.5
8 D 2 10.0
9 A 3 7.8
10 B 3 5.8
11 C 3 9.7
12 D 3 9.7
13 A 4 7.8
14 B 4 8.0
15 C 4 8.6
16 D 4 10.0
17 A 5 6.8
18 B 5 7.0
19 C 5 8.6
20 D 5 1.0



Ergebnis (Visualisiert)


ID NAME ROUND POINTS POINTS SUMME RANK
17 A 5 6,8 x
1 A 1 7,8 7,8
9 A 3 7,8 7,8 15,6 3
13 A 4 7,8 x
5 A 2 9,8 x
6 B 2 0 x
2 B 1 5 x
10 B 3 5,8 5,8
18 B 5 7 7 12,8 4
14 B 4 8 x
7 C 2 2,5 x
3 C 1 7,7 x
15 C 4 8,6 8,6
19 C 5 8,6 8,6 17,2 2
11 C 3 9,7 x
20 D 5 1 x
4 D 1 9,7 x
12 D 3 9,7 9,7
8 D 2 10 10 19,7 1
16 D 4 10 x


Ergebnis (TSQL)

NAME POINTS
D 19.7
C 17.2
A 15.6
B 12.8

strausto
01.11.2007, 15:19
Wer Lust hat kann die erste Aufgabe mit einem anderen Ansatz lösen als die zweite. Somit haben wir einen Ansatz der beide Ergebnisse errechnet oder einen einfachen für die erste und einen etwas komplexeren Ansatz für die zweite Aufgabe.

strausto
01.11.2007, 15:24
Nun halte ich eine Woche für gerechtfertigt ;)

strausto
01.11.2007, 16:03
Ok Josef!
(Lösung per PN zugesendet)
Wirklich gut...

Und wenn Du nun noch diese Darstellung ausgeben kannst, bin ich beeindruckt:


NAME POINTS BEST OF NAME
A 9.8 1
A 7.8 2
A 7.8 3
A 7.8 4
A 6.8 5
B 8.0 1
B 7.0 2
B 5.8 3
B 5.0 4
B 0.0 5
C 9.7 1
C 8.6 2
C 8.6 3
C 7.7 4
C 2.5 5
D 10.0 1
D 10.0 2
D 9.7 3
D 9.7 4
D 1.0 5

strausto
01.11.2007, 16:21
und wenn nur der zweit- und viertbeste zählt?

Josef P.
01.11.2007, 16:40
Und wenn Du nun noch diese Darstellung ausgeben kannst, bin ich beeindruckt
...naja, diese Variante kann man schon fast aus der Access-FAQ (www.donkarl.com - entspricht dem Prinzip der fortlaufende Nummer) abschreiben ... daher auch diesmal nicht per PN, sondern direkt:
SELECT
T.Name, T.Points,
(SELECT Count(*) FROM TABLE1 X WHERE X.Name = T.Name AND
(X.Points > T.Points
OR (X.Points = T.Points AND X.Round < T.Round))
) + 1 AS [BEST OF NAME]
FROM
TABLE1 T
Order By
1, 3
... ergänzend zur "laufenden Nummer"-Variante fügte ich nur noch das Weiterzählen bei gleichem Punktestand dazu.

und wenn nur der zweit- und viertbeste zählt?
... dann findest sich bestimmt auch eine Lösung. ;)

Aber jetzt hoffe ich erstmal, dass auch noch andere Spaß mit SQL bekommen. :grins:

strausto
01.11.2007, 16:49
Sehr gut!!

Ich hatte einen etwas anderen Ansatz!

für die ersten Aufgaben:

USE TEMPDB
GO
IF OBJECT_ID('TABLE1') IS NOT NULL
DROP TABLE TABLE1
CREATE TABLE TABLE1
(
[ID] INT IDENTITY(1,1) PRIMARY KEY,
[NAME] VARCHAR(50) NOT NULL,
[ROUND] TINYINT NOT NULL,
[POINTS] DEC(3,1)
)
INSERT TABLE1 ([NAME],[ROUND],[POINTS]) VALUES( 'A',1,7.8 )
INSERT TABLE1 ([NAME],[ROUND],[POINTS]) VALUES( 'B',1,5.0 )
INSERT TABLE1 ([NAME],[ROUND],[POINTS]) VALUES( 'C',1,7.7 )
INSERT TABLE1 ([NAME],[ROUND],[POINTS]) VALUES( 'D',1,9.7 )

INSERT TABLE1 ([NAME],[ROUND],[POINTS]) VALUES( 'A',2,9.8 )
INSERT TABLE1 ([NAME],[ROUND],[POINTS]) VALUES( 'B',2,0.0 )
INSERT TABLE1 ([NAME],[ROUND],[POINTS]) VALUES( 'C',2,2.5 )
INSERT TABLE1 ([NAME],[ROUND],[POINTS]) VALUES( 'D',2,10.0 )

INSERT TABLE1 ([NAME],[ROUND],[POINTS]) VALUES( 'A',3,7.8 )
INSERT TABLE1 ([NAME],[ROUND],[POINTS]) VALUES( 'B',3,5.8 )
INSERT TABLE1 ([NAME],[ROUND],[POINTS]) VALUES( 'C',3,9.7 )
INSERT TABLE1 ([NAME],[ROUND],[POINTS]) VALUES( 'D',3,9.7 )

INSERT TABLE1 ([NAME],[ROUND],[POINTS]) VALUES( 'A',4,7.8 )
INSERT TABLE1 ([NAME],[ROUND],[POINTS]) VALUES( 'B',4,8.0 )
INSERT TABLE1 ([NAME],[ROUND],[POINTS]) VALUES( 'C',4,8.6 )
INSERT TABLE1 ([NAME],[ROUND],[POINTS]) VALUES( 'D',4,10.0 )

SELECT * FROM TABLE1

SELECT
[NAME],
[POINTS] = SUM([POINTS])
FROM
(

SELECT
TABLE1.[NAME],
TABLE1.[POINTS],
TABLE1.[ROUND],
[RANK] = ( SELECT COUNT(*) FROM TABLE1 TEMP WHERE TEMP.NAME = TABLE1.NAME AND TEMP.POINTS >= TABLE1.POINTS )
- ( SELECT COUNT(*) FROM TABLE1 TEMP WHERE TEMP.NAME = TABLE1.NAME AND TEMP.POINTS = TABLE1.POINTS AND TEMP.ID > TABLE1.ID )
FROM
TABLE1 TABLE1
) V
WHERE
RANK BETWEEN 2 AND 3
GROUP BY
NAME
ORDER BY 2 DESC


für die vorletzte:

SELECT
TABLE1.[NAME],
TABLE1.[POINTS],
[BEST OF NAME] = ( SELECT COUNT(*) FROM TABLE1 TEMP WHERE TEMP.NAME = TABLE1.NAME AND TEMP.POINTS >= TABLE1.POINTS )
- ( SELECT COUNT(*) FROM TABLE1 TEMP WHERE TEMP.NAME = TABLE1.NAME AND TEMP.POINTS = TABLE1.POINTS AND TEMP.ID > TABLE1.ID )
FROM
TABLE1 TABLE1
order by
1 asc,2 desc ,3 asc

und die letzte


SELECT
[NAME],
[POINTS] = SUM([POINTS])
FROM
(

SELECT
TABLE1.[NAME],
TABLE1.[POINTS],
TABLE1.[ROUND],
[RANK] = ( SELECT COUNT(*) FROM TABLE1 TEMP WHERE TEMP.NAME = TABLE1.NAME AND TEMP.POINTS >= TABLE1.POINTS )
- ( SELECT COUNT(*) FROM TABLE1 TEMP WHERE TEMP.NAME = TABLE1.NAME AND TEMP.POINTS = TABLE1.POINTS AND TEMP.ID > TABLE1.ID )
FROM
TABLE1 TABLE1
) V
WHERE
RANK IN (2,4) --zweite und vierte
GROUP BY
NAME
ORDER BY 2 DESC

Josef P.
01.11.2007, 16:59
Für die letzte Variante hätte ich vermutlich auch das Ergebnis aus #8 verwendet. Da so eine Syntax aber bei mehreren DS etwas Zeit benötigt, versuchte ich anfangs einen etwas anderen Weg.
... besonders beim Beispiel aus #1 könnte ich mir vorstellen, dass meine Variante ganz gut laufen könnte. ;)

Josef P.
07.11.2007, 20:24
Ich halte meine Ergebnisse auch noch einmal hier fest. ;)

Als Lösung für die Aufgabe in #1:
SELECT
NAME,
Sum(Points) - Min(Points) - Max(Points)
FROM
TABLE1
group By
Name
Order By
2 desc, 1
... das war Torsten dann doch zu "einfach" und er erschwerte die Aufgabenstellung. ;)

meine Lösung für die Aufgabenstellung in #3:
SELECT
T.Name,
Sum(T.Points) - Max(t.Points) AS Points
FROM
TABLE1 T
WHERE
T.Round IN (SELECT TOP 3 Z.Round FROM TABLE1 Z WHERE Z.Name = T.Name Order By Z.points desc, Z.round)
group By
T.Name
Order By
2 desc, 1


die Aufgabe in #6 löste ich dann ähnlich wie Torsten:
SELECT
T.Name, T.Points,
(SELECT Count(*) FROM TABLE1 X WHERE X.Name = T.Name AND
(X.Points > T.Points
OR (X.Points = T.Points AND X.Round < T.Round))
) + 1 AS [BEST OF NAME]
FROM
TABLE1 T
Order By
1, 3

strausto
08.11.2007, 08:12
Ich werde den Beitrag auf die Aufgaben und Lösungem reduzieren und in die FAQ stellen, zusammen mit den noch kommenden Aufgaben.