MySQL: Wie schreibt man eine Abfrage für eine Beziehung, die auf einem Bereich und nicht auf einem Fremdschlüssel basiert?

Question

Ich habe drei Tabellen in MySQL, die zwar zusammenhängen, aber technisch nicht durch einen Fremdschlüssel miteinander verbunden sind. Sie sind: Benutzer , Ebenen et Klassen .

Die Tabelle Benutzer hat eine Karma-Spalte, einen numerischen Typ. Basierend auf dieser Karma Nummer, möchte ich den Namen des Benutzers wissen Ebene die ich aus der Tabelle der Ebenen abrufe. Dies ist keine harte Beziehung, da eine Ebene mit einer Stufe verbunden ist. Bereich von Karmawerten, etwa so:

• Stufe 1 (Mindestkarma: 0)
• Stufe 2 (Mindestkarma: 100)
• Stufe 3 (Mindestkarma: 500)
• ...

Wenn also ein Benutzer ein Karma von 400 hat, sollte der Rückgabewert 2 sein. Um die Dinge etwas komplizierter zu machen, gibt die Levelnummer die Stufe des Benutzers an Klasse , deren Definitionen in der Tabelle Klassen gespeichert sind. Auch dies ist eine Bereich Beziehung:

• Klasse Ameise (mindestens Stufe 1)
• Klasse Eule (mindestens Stufe 5)
• Klasse Löwe (mindestens Stufe 100)
• ...

Zusammengefasst geht es um drei Tabellen, die auf der Grundlage von Bereichswerten eine implizite Beziehung zueinander haben. Meine Frage bezieht sich darauf, wie man diese Tabellen effektiv abfragen kann. Ich benötige häufig Informationen für einen oder mehrere Benutzer auf der Grundlage einer Bedingung. Die Ergebnismenge sollte die Benutzerdetails, aber auch die Stufe und Klasse des Benutzers enthalten.

Für einen einzelnen Benutzer ist es mir gelungen, diese Abfrage zu schreiben, die gut funktioniert:

SELECT u.*,  lv.num as level, lvc.title as class, lvc.id as classid
FROM user as u, level as lv, levelclass as lvc
WHERE u.id = ? AND lv.min_karma <= u.karma AND lv.num <= lvc.minlevel_num
ORDER BY lv.num DESC LIMIT 1;

Wenn ich jedoch die Ergebnismenge erweitern würde, indem ich WHERE u.id = ? belasse und LIMIT 1 entferne, so dass ich nach einer Liste von Benutzern, erhalte ich die Kombination aller drei Tabellen. Normalerweise würden Sie die Zeilen durch einen inneren Join auf Schlüssel reduzieren, aber da dies eine Bereichsprüfung ist, funktioniert das nicht. Ich habe versucht, die Bereichsprüfung in einer inneren Verknüpfungsbedingung zu verwenden, aber das führt zum gleichen Ergebnis. Auch mit der Gruppierung erhalte ich nicht das gewünschte Ergebnis.

In einem verzweifelten Versuch habe ich mir diese Abfrage ausgedacht, die funktioniert:

SELECT usr.*, 
(SELECT lv.num as level
FROM user as u, level as lv, levelclass as lvc
WHERE u.id = usr.id AND lv.min_karma <= u.karma AND lv.num <= lvc.minlevel_num
ORDER BY lv.num DESC LIMIT 1) as level,
(SELECT lvc.title as class
FROM user as u, level as lv, levelclass as lvc
WHERE u.id = usr.id AND lv.min_karma <= u.karma AND lv.num <= lvc.minlevel_num
ORDER BY lv.num DESC LIMIT 1) as class,
(SELECT lvc.image as class_image
FROM user as u, level as lv, levelclass as lvc
WHERE u.id = usr.id AND lv.min_karma <= u.karma AND lv.num <= lvc.minlevel_num
ORDER BY lv.num DESC LIMIT 1) as class_image,
(SELECT lvc.id as classid
FROM user as u, level as lv, levelclass as lvc
WHERE u.id = usr.id AND lv.min_karma <= u.karma AND lv.num <= lvc.minlevel_num
ORDER BY lv.num DESC LIMIT 1) as classid
FROM user as usr
ORDER BY usr.$sortby $direction LIMIT ?,?

Das scheint mir jedoch sehr ineffizient zu sein. Im Grunde schreibe ich hier eine Unterabfrage für jede Spalte(!) die ich aus den Tabellen der Stufen und Klassen benötige. Wenn ich mehrere Spalten der Ebenen- oder Klassentabellen in einer Unterabfrage abfrage, werden wieder die Kombinationen aller Werte zurückgegeben. Ich habe das Gefühl, dass es eine Lücke in meinen DB-Kenntnissen gibt, etwas Offensichtliches, das mir fehlt, eine Funktion, die ich nicht kenne...

Können Sie mir helfen? Wie schreibt man eine effiziente Abfrage für einen Satz von Zeilen, der Spalten aus drei Tabellen kombiniert, aber nicht durch Schlüssel (stattdessen Bereiche) verknüpft ist?

PS: Ich weiß, dass ich das Szenario stark vereinfachen könnte, wenn ich dieses Schema denormalisieren würde, um Ebenen und Klassen in der Tabelle "Benutzer" zusammenzufassen, aber es gibt einen bestimmten Grund, warum ich das brauche, glauben Sie mir.

Answer 1

Wenn Sie die Tabellen "Level" und "Class" so ändern können, dass der tatsächliche Bereich in der Zeile enthalten ist (d. h. "minkarma" und "maxkarma" in der Tabelle "Level", "minlevel" und "maxlevel" in der Tabelle "Class"), sollten Sie das in Ihrer Verknüpfungsbedingung verwenden können, und es wird nur eine einzige Zeile für jeden Benutzer zurückgegeben.

Exemple :

SELECT * FROM user as u
INNER JOIN level as lv on
    u.karma >= lv.min_karma AND u.karma <= lv.max_karma
INNER JOIN levelclass as lvc on
    lv.num >= lvc.min_level AND lv.num <= lvc.max_level

Der Grund, warum Sie mehrere Zeilen erhalten, ist, dass Sie mehrere Ebenen und Klassen abgleichen, indem Sie nur nach dem Mindestwert filtern.

Answer 2

Ich müsste Ihre genaue Datenstruktur sehen, um sicher zu sein, aber wie wäre es mit etwas wie diesem?

SELECT
    *
FROM
    [User] u
LEFT OUTER JOIN
    [Level] lv
ON
    lv.num =
(
    SELECT
        MAX(lv2.num)
    FROM
        [Level] lv2
    WHERE
        lv2.min_karma <= u.karma
)
LEFT OUTER JOIN
    [LevelClass] lvc
ON
    lvc.id =
(
    SELECT
        MAX(lvc2.id)
    FROM
        [LevelClass] lvc2
    WHERE
        lvc2.minlevel_num <= lv.num
)

Answer 3

Sie möchten eine Verknüpfung auf ein Fallstatement anwenden

SELECT 
 * 
FROM 
 users u
JOIN 
 levels l
ON 
 l.id = 
CASE 
    WHEN u.karma <= 10
    THEN 1
    WHEN u.karma > 11 & u.karma <= 100
    THEN 2
    WHEN u.karma > 100 & u.karma <= 1000
    THEN 3
    WHEN u.karma > 1000 
    THEN 4
END
JOIN 
 class c
ON 
 c.id is
CASE 
    WHEN l.id <= 5
    THEN 'owl'
    WHEN l.id > 5 & u.karma <= 10
    THEN 'lion'
END

Nach Geschmack würzen.