Übersetzung einer TSQL Abfrage in MYSQL

spikaner · 6. März 2013

Ich breuchte dringend hilfe bei einer wie im Titel beschrieben Übersetzung von einer TSQL Abfrage in eine MYSQL abfrage in PHP, ich bin seit Wochen am bastelln, versuchen und verzweifeln.
Darum der hilfeschrei an euch.
Hier mal die abfrage (orginal).

Code:

    -- Get all required skills of everything
    WITH allSkills AS(
    SELECT G.categoryID, G.groupName, G.groupID, T.typeName, T.typeID, skillName = T2.typeName,
           skillID = T2.typeID, skillLevel = ISNULL(TAL.valueInt, CONVERT(int, TAL.valueFloat))
      FROM invGroups G
        INNER JOIN invTypes T ON T.groupID = G.groupID
          INNER JOIN dgmTypeAttributes TA ON TA.typeID = T.typeID AND
                                             TA.attributeID IN (182, 183, 184, 1285, 1289, 1290)
              INNER JOIN dgmTypeAttributes TAL ON TAL.typeID = TA.typeID AND -- I hate this join
                                                  ((TAL.attributeID = 277 AND TA.attributeID = 182) OR
                                                  (TAL.attributeID = 278 AND TA.attributeID = 183) OR
                                                  (TAL.attributeID = 279 AND TA.attributeID = 184) OR
                                                  (TAL.attributeID = 1286 AND TA.attributeID = 1285) OR
                                                  (TAL.attributeID = 1287 AND TA.attributeID = 1289) OR
                                                  (TAL.attributeID = 1288 AND TA.attributeID = 1290))
            INNER JOIN invTypes T2 ON T2.typeID = ISNULL(TA.valueInt, CONVERT(int, TA.valueFloat))
      WHERE T.typeID NOT IN (19430, 9955)),
     -- NOT IN: Lets not include Omnipotent and Polaris skill
     -- as they are self referencing and create infinite recursion.
    allSkillSkills AS(
    SELECT recursionLevel = 0, S.categoryID, S.groupID, S.groupName, S.typeID, S.typeName,
           S.skillName, S.skillID, S.skillLevel
      FROM allSkills S -- Select all skills
     UNION ALL
     SELECT recursionLevel = CTE.recursionLevel + 1, CTE.categoryID, CTE.groupID, CTE.groupName,
            CTE.typeID, CTE.typeName, CTE2.skillName, CTE2.skillID, CTE2.skillLevel
       FROM allSkillSkills CTE -- Union recursive table with itself
         INNER JOIN allSkills CTE2 ON CTE2.typeID = CTE.skillID -- Join on all skills to get skill data.
    )
    SELECT recursionLevel, groupName, typeName, skillName, skillLevel
      FROM allSkillSkills
     WHERE categoryID = 6 -- We only care about ships in your example
                          -- you can substitute whatever categoryID you want or narrow it on a groupID.
     ORDER BY groupName ASC, typeName ASC, recursionLevel ASC, skillName ASC
    /*
    Substitute this for query above to only get max level.
     
    SELECT groupName, typeName, skillName, skillLevel = MAX(skillLevel)
      FROM allSkillSkills
     WHERE categoryID = 6
     GROUP BY groupName, typeName, skillName
     ORDER BY groupName ASC, typeName ASC, skillName ASC
    */

mfg Spikaner

Yaslaw · 6. März 2013

Aua. Das wird lustig.
Gut, ich kenne TSQL nicht. Aber wirklich fremd ist mir wenig davon

Da ist am Anfang mit WITH eine Quelle definiert. 'allSkills'

SQL:

-- Quelle [allSkills]
SELECT 
	G.categoryID, 
	G.groupName, 
	G.groupID, 
	T.typeName, 
	T.typeID, 
	skillName = T2.typeName,
	skillID = T2.typeID, 
	skillLevel = ISNULL(TAL.valueInt, AST(TA.valueFloat AS SIGNED))
FROM 
	invGroups G
	INNER JOIN invTypes T 
		ON T.groupID = G.groupID
	INNER JOIN dgmTypeAttributes TA 
		ON TA.typeID = T.typeID 
		AND TA.attributeID IN (182, 183, 184, 1285, 1289, 1290)
	INNER JOIN dgmTypeAttributes TAL 
		ON TAL.typeID = TA.typeID 
		AND (
			(TAL.attributeID = 277 AND TA.attributeID = 182) 
			OR (TAL.attributeID = 278 AND TA.attributeID = 183) 
			OR (TAL.attributeID = 279 AND TA.attributeID = 184)
			OR (TAL.attributeID = 1286 AND TA.attributeID = 1285)
			OR (TAL.attributeID = 1287 AND TA.attributeID = 1289)
			OR (TAL.attributeID = 1288 AND TA.attributeID = 1290)
		)
	INNER JOIN invTypes T2 
		ON T2.typeID = ISNULL(TA.valueInt, CAST(TA.valueFloat AS SIGNED))
WHERE 
	T.typeID NOT IN (19430, 9955)

Die Zweite Quelle die auf der ersten Quelle basiert.
Da diese Quelle aber auf sich selber referenziert glaube ich nicht, dass sich das mit MySQL 1 zu 1 umsetzen lässt

SQL:

-- Quell [allSkillSkills]
SELECT 
	recursionLevel = 0, 
	S.categoryID, 
	S.groupID, 
	S.groupName, 
	S.typeID, 
	S.typeName,
	S.skillName, 
	S.skillID, S.skillLevel
FROM 
	-- Die Quelle von weiter oben
	allSkills S
UNION ALL
SELECT 
	recursionLevel = CTE.recursionLevel + 1, 
	CTE.categoryID, 
	CTE.groupID, 
	CTE.groupName,
	CTE.typeID, 
	CTE.typeName, 
	CTE2.skillName, 
	CTE2.skillID, 
	CTE2.skillLevel
FROM 
-- Hier wird auf sich selber referenziert. 	
	allSkillSkills CTE -- Union recursive table with itself
	INNER JOIN allSkills CTE2 
		ON CTE2.typeID = CTE.skillID

Und das SQL dass dann auf der letzten Quelle basiert

SQL:

SELECT
	recursionLevel, 
	groupName, 
	typeName, 
	skillName, 
	skillLevel
FROM 
	allSkillSkills
WHERE 
	categoryID = 6
ORDER BY 
	groupName ASC, 
	typeName ASC, 
	recursionLevel ASC, 
	skillName ASC

Würde die Zweite Quelle nicht auf sich selber zugreiffen, könnte man einfach alles zusammensetzen. Das würde so aussehen. Aber eben, die Selbstreferenz....

SQL:

SELECT
	recursionLevel, 
	groupName, 
	typeName, 
	skillName, 
	skillLevel
FROM 
	(
		SELECT 
			recursionLevel = 0, 
			S.categoryID, 
			S.groupID, 
			S.groupName, 
			S.typeID, 
			S.typeName,
			S.skillName, 
			S.skillID, S.skillLevel
		FROM 
			-- Die Quelle von weiter oben
			(
				SELECT 
					G.categoryID, 
					G.groupName, 
					G.groupID, 
					T.typeName, 
					T.typeID, 
					skillName = T2.typeName,
					skillID = T2.typeID, 
					skillLevel = ISNULL(TAL.valueInt, AST(TA.valueFloat AS SIGNED))
				FROM 
					invGroups G
					INNER JOIN invTypes T 
						ON T.groupID = G.groupID
					INNER JOIN dgmTypeAttributes TA 
						ON TA.typeID = T.typeID 
						AND TA.attributeID IN (182, 183, 184, 1285, 1289, 1290)
					INNER JOIN dgmTypeAttributes TAL 
						ON TAL.typeID = TA.typeID 
						AND (
							(TAL.attributeID = 277 AND TA.attributeID = 182) 
							OR (TAL.attributeID = 278 AND TA.attributeID = 183) 
							OR (TAL.attributeID = 279 AND TA.attributeID = 184)
							OR (TAL.attributeID = 1286 AND TA.attributeID = 1285)
							OR (TAL.attributeID = 1287 AND TA.attributeID = 1289)
							OR (TAL.attributeID = 1288 AND TA.attributeID = 1290)
						)
					INNER JOIN invTypes T2 
						ON T2.typeID = ISNULL(TA.valueInt, CAST(TA.valueFloat AS SIGNED))
				WHERE 
					T.typeID NOT IN (19430, 9955)
			) AS s
		UNION ALL
		SELECT 
			recursionLevel = CTE.recursionLevel + 1, 
			CTE.categoryID, 
			CTE.groupID, 
			CTE.groupName,
			CTE.typeID, 
			CTE.typeName, 
			CTE2.skillName, 
			CTE2.skillID, 
			CTE2.skillLevel
		FROM 
		-- Hier wird auf sich selber referenziert. 	
			allSkillSkills CTE -- Union recursive table with itself
			INNER JOIN allSkills CTE2 
				ON CTE2.typeID = CTE.skillID
	) AS s1
WHERE 
	categoryID = 6
ORDER BY 
	groupName ASC, 
	typeName ASC, 
	recursionLevel ASC, 
	skillName ASC

[allSkillSkills]

spikaner · 9. März 2013

Danke erstmnal für die schnelle Antwort, ich bin jetzt auf meiner Suche auf das hier gestoßen.
http://wiki.eve-id.net/Prerequisite_Skilltree
Problem dabei wie bekomme ich so eine Funktion zum laufen und würde es auf einen MySQL-Client-Version: 5.1.66 System auch laufen?

mfg Spikaner

Yaslaw · 9. März 2013

Du meinst sowas?
http://wiki.yaslaw.info/wikka/MySQLTree
Ich denke das Beispiel 3 auf meiner Seite entspricht am ehesten dem Resultat deiner gelinkten Seite

Übersetzung einer TSQL Abfrage in MYSQL

spikaner

Quereinsteiger @ php

Yaslaw

alter Rempler

spikaner

Quereinsteiger @ php

Yaslaw

alter Rempler

Neue Beiträge