✔ ROWCOUNT machen auf einen Statement

Paspirgilis

Paspirgilis

Weißer Powerranger
Hi,
Meine Sache ist ein wenig größer xD.
Ich ahbe eine Statement und ich möchte im SELECT noch eine `id` mit einbringen.
Diese soll im Statementwergebnis durchnummeriert sein. 1 dann 2 dann 3 dann 4. Halt die Zeilennummer.
Habe nur komsiche Sachen gefunden und nicht funktionierende Sache für MySQL.
hier das Statement (Ist für den WC3 DotA Ghostbot):
Code: 
SELECT overview.name AS name, count(*) AS gamescount, sum(overview.deaths) AS deaths, sum(overview.assists) AS assists,
            sum(overview.kills) AS kills, sum(overview.creepkills) AS creepkills, sum(overview.creepdenies) AS creepdenies,
            sum(overview.neutralkills) AS neutralkills, sum(overview.towerkills) AS towerkills, sum(overview.raxkills) AS raxkills,
            sum(overview.courierkills) AS courierkills,
statistic.losses AS losses, statistic.wins AS wins, statistic.notended AS notended,
(statistic.wins*200-statistic.losses*200+sum(overview.creepdenies)*2+sum(overview.towerkills)*5+sum(overview.courierkills)*15+
 sum(overview.raxkills)*10-sum(overview.deaths)*10+sum(overview.kills)*5+sum(overview.assists)*10) AS points,
(statistic.wins*40+statistic.losses*20+statistic.notended*10) AS exp
        FROM (

SELECT  gameplayers.name, dotaplayers.deaths, dotaplayers.assists, dotaplayers.kills, dotaplayers.creepkills,
            dotaplayers.creepdenies, dotaplayers.neutralkills, dotaplayers.towerkills, dotaplayers.raxkills,
            dotaplayers.courierkills
            FROM gameplayers
            LEFT JOIN dotaplayers ON dotaplayers.colour = gameplayers.colour
                AND dotaplayers.gameid = gameplayers.gameid
) as overview

LEFT JOIN
(
SELECT gameplayers.name AS name, IFNULL(losstable.losses, 0) AS losses, IFNULL(wontable.wins, 0) AS wins, IFNULL(noendtable.notended, 0) AS notended
FROM gameplayers

LEFT JOIN
(SELECT lost.name AS names, lost.counts AS losses
FROM (
SELECT gameplayers.name as name, COUNT( * ) AS counts
FROM dotagames
LEFT JOIN gameplayers ON gameplayers.gameid = dotagames.gameid
WHERE dotagames.winner = ( 2 - gameplayers.team )
GROUP BY gameplayers.name
) AS lost ) AS losstable
ON losstable.names = gameplayers.name

LEFT JOIN
(SELECT won.name AS names, won.counts AS wins
FROM (
SELECT gameplayers.name AS name, COUNT( * ) AS counts
FROM dotagames
LEFT JOIN gameplayers ON gameplayers.gameid = dotagames.gameid
WHERE dotagames.winner = ( gameplayers.team +1 )
GROUP BY gameplayers.name
) AS won) AS wontable
ON wontable.names = gameplayers.name

LEFT JOIN
(SELECT noend.name AS names, noend.counts AS notended
FROM (
SELECT gameplayers.name AS name, COUNT( * ) AS counts
FROM dotagames
LEFT JOIN gameplayers ON gameplayers.gameid = dotagames.gameid
WHERE dotagames.winner = 0
GROUP BY gameplayers.name
) AS noend ) AS noendtable
ON noendtable.names = gameplayers.name

GROUP BY gameplayers.name
) AS statistic
ON overview.name = statistic.name

        GROUP BY overview.name

MfG
Mark Paspirgilis
 
Ich mag ja komplizierte SQLs. Aber deines ist nicht nur kompliziert, sondern auch unleserlich.

Formatier das Zeugs, sonst kann es keiner lesen.
Ich hab mal versucht aus dem Buchstabensalat ein SQL zu machen indem ich ein wenig mit Zeilenumbrüchen und Tabulatororen um mich geworfen habe.

SQL: 
SELECT 
	overview.name AS name, 
	count(*) AS gamescount, 
	sum(overview.deaths) AS deaths, 
	sum(overview.assists) AS assists,
	sum(overview.kills) AS kills, 
	sum(overview.creepkills) AS creepkills, 
	sum(overview.creepdenies) AS creepdenies,
    sum(overview.neutralkills) AS neutralkills, 
    sum(overview.towerkills) AS towerkills, 
    sum(overview.raxkills) AS raxkills,
    sum(overview.courierkills) AS courierkills,
	statistic.losses AS losses, 
	statistic.wins AS wins, 
	statistic.notended AS notended,
	(statistic.wins*200-statistic.losses*200
		+sum(overview.creepdenies)*2
		+sum(overview.towerkills)*5
		+sum(overview.courierkills)*15
		+sum(overview.raxkills)*10
		-sum(overview.deaths)*10
		+sum(overview.kills)*5
		+sum(overview.assists)*10
	) AS points,
	(statistic.wins*40+statistic.losses*20+statistic.notended*10) AS exp
FROM (SELECT  
		gameplayers.name, 
		dotaplayers.deaths, 
		dotaplayers.assists, 
		dotaplayers.kills, 
		dotaplayers.creepkills,
        dotaplayers.creepdenies, dotaplayers.neutralkills, dotaplayers.towerkills, dotaplayers.raxkills,
        dotaplayers.courierkills
    FROM 
    	gameplayers
    	LEFT JOIN dotaplayers 
    		ON dotaplayers.colour = gameplayers.colour
        	AND dotaplayers.gameid = gameplayers.gameid
	) as overview
	LEFT JOIN
		(SELECT 
			gameplayers.name AS name, 
			IFNULL(losstable.losses, 0) AS losses, 
			IFNULL(wontable.wins, 0) AS wins, 
			IFNULL(noendtable.notended, 0) AS notended
		FROM 
			gameplayers	
			LEFT JOIN
				(SELECT 
					lost.name AS names, 
					lost.counts AS losses
				FROM (SELECT 
						gameplayers.name as name, 
						COUNT( * ) AS counts
					FROM 
						dotagames
						LEFT JOIN gameplayers 
							ON gameplayers.gameid = dotagames.gameid
					WHERE 
						dotagames.winner = ( 2 - gameplayers.team )
					GROUP BY 
						gameplayers.name
					) AS lost 
				) AS losstable
				ON losstable.names = gameplayers.name
			LEFT JOIN
				(SELECT 
					won.name AS names, 
					won.counts AS wins
				FROM (SELECT 
						gameplayers.name AS name, 
						COUNT( * ) AS counts
					FROM 
						dotagames
						LEFT JOIN gameplayers 
							ON gameplayers.gameid = dotagames.gameid
					WHERE 
						dotagames.winner = ( gameplayers.team +1 )
					GROUP BY 
						gameplayers.name
					) AS won
				) AS wontable
				ON wontable.names = gameplayers.name
			LEFT JOIN
				(SELECT 
					noend.name AS names, 
					noend.counts AS notended
				FROM 
					(SELECT 
						gameplayers.name AS name, 
						COUNT( * ) AS counts
					FROM 
						dotagames
						LEFT JOIN gameplayers 
							ON gameplayers.gameid = dotagames.gameid
						WHERE 
							dotagames.winner = 0
						GROUP BY 
							gameplayers.name
						) AS noend 
					) AS noendtable
					ON noendtable.names = gameplayers.name
			GROUP BY gameplayers.name
			) AS statistic
			ON overview.name = statistic.name
GROUP BY overview.name

Du solltest dir das Konzept von Views mal überlegen. Macht die Welt ein wenig einfacher.

Achja, wenn ich dich richtig verstanden habe, suchst du eine Zeilennummerierung. Ich habe dazu ein kleines Beispiel, das du in dein SQL einbauen kannst

SQL: 
SELECT
    @rownum:=@rownum+1 AS rownum,
    t.*
FROM
    (SELECT @rownum:=0) AS vars,
    tabelle AS t
 
Zuletzt bearbeitet von einem Moderator:
Danke, funktioniert.
Ich hab ähnlichen Code gesehen wie dein Ansatz aber deiner war deutlich besser erklärt.
Mein SQL statement war und ist "formatiert". Da im PHP Script das anfängt mit $sql = '...
Und dann ist die große Abfrage für die stats (kills, deaths...) eingerückt und die wins, losses and undecided linksbündig.
Jetzt hab ich da deine Tabelle drum herum gelegt.
Für dich sieht es vielleihct ein wenig unformatiert aus aber ich poste trotzdem das Fertige ergebnis, vielelicht wird dieses SQL-Statement von anderen mal gebrauch, da es für die aktuelleVersion vom ghost hostbot für DotA "DAS" SQL-Statement ist um alle User nach punkte zu sortieren in EINEM Statement.

SQL: 
SELECT
@rownum:=@rownum+1 AS id,
t.*
FROM
(SELECT @rownum:=0) AS vars,
(SELECT overview.name AS name, count(*) AS gamescount, sum(overview.deaths) AS deaths, sum(overview.assists) AS assists,
            sum(overview.kills) AS kills, sum(overview.creepkills) AS creepkills, sum(overview.creepdenies) AS creepdenies,
            sum(overview.neutralkills) AS neutralkills, sum(overview.towerkills) AS towerkills, sum(overview.raxkills) AS raxkills,
            sum(overview.courierkills) AS courierkills,
statistic.losses AS losses, statistic.wins AS wins, statistic.notended AS notended,
(statistic.wins*200-statistic.losses*200+sum(overview.creepdenies)*2+sum(overview.towerkills)*5+sum(overview.courierkills)*15+
 sum(overview.raxkills)*10-sum(overview.deaths)*10+sum(overview.kills)*5+sum(overview.assists)*10) AS points,
(statistic.wins*40+statistic.losses*20+statistic.notended*10) AS exp
        FROM (

SELECT  gameplayers.name, dotaplayers.deaths, dotaplayers.assists, dotaplayers.kills, dotaplayers.creepkills,
            dotaplayers.creepdenies, dotaplayers.neutralkills, dotaplayers.towerkills, dotaplayers.raxkills,
            dotaplayers.courierkills
            FROM gameplayers
            LEFT JOIN dotaplayers ON dotaplayers.colour = gameplayers.colour
                AND dotaplayers.gameid = gameplayers.gameid
) as overview

LEFT JOIN
(
SELECT gameplayers.name AS name, IFNULL(losstable.losses, 0) AS losses, IFNULL(wontable.wins, 0) AS wins, IFNULL(noendtable.notended, 0) AS notended
FROM gameplayers

LEFT JOIN
(SELECT lost.name AS names, lost.counts AS losses
FROM (
SELECT gameplayers.name as name, COUNT( * ) AS counts
FROM dotagames
LEFT JOIN gameplayers ON gameplayers.gameid = dotagames.gameid
WHERE dotagames.winner = ( 2 - gameplayers.team )
GROUP BY gameplayers.name
) AS lost ) AS losstable
ON losstable.names = gameplayers.name

LEFT JOIN
(SELECT won.name AS names, won.counts AS wins
FROM (
SELECT gameplayers.name AS name, COUNT( * ) AS counts
FROM dotagames
LEFT JOIN gameplayers ON gameplayers.gameid = dotagames.gameid
WHERE dotagames.winner = ( gameplayers.team +1 )
GROUP BY gameplayers.name
) AS won) AS wontable
ON wontable.names = gameplayers.name

LEFT JOIN
(SELECT noend.name AS names, noend.counts AS notended
FROM (
SELECT gameplayers.name AS name, COUNT( * ) AS counts
FROM dotagames
LEFT JOIN gameplayers ON gameplayers.gameid = dotagames.gameid
WHERE dotagames.winner = 0
GROUP BY gameplayers.name
) AS noend ) AS noendtable
ON noendtable.names = gameplayers.name

GROUP BY gameplayers.name
) AS statistic
ON overview.name = statistic.name

        GROUP BY overview.name) AS t
 
Zuletzt bearbeitet von einem Moderator:
Um antworten zu können musst du eingeloggt sein.

Neue Beiträge

Zurück