mysql - SQL:连接另一个表中的值

Question

我有 1 个名为 accounts 的表，另一个名为 level_points

基本上，这个想法是确定达到 X 级别所需的最低点数。

帐户结构

id、name..等点

Level_Points 结构

等级，点数

此处的值例如

(1, 5)(2, 10)(3, 15)

I'm able to calculate the level using this query

SELECT `level` 
FROM `level_points` 
WHERE `points` <= (SELECT `points` 
                   FROM `accounts` 
                   WHERE `id` = 'x') 
ORDER BY `level` 
DESC LIMIT 1

我的问题是，现在我正在尝试加入表以获得类似的内容(对于帐户表中的每个用户)

结果:

For user 1: `id`, `name` etc... `points`, `level`For user 2: `id`, `name` etc... `points`, `level`For user 3: `id`, `name` etc... `points`, `level`

我不太确定如何使用连接来做到这一点，而且我似乎无法在这里找到对我有帮助的答案。

Answer 1

尝试这样的事情:

SELECT ac.id,
    ac.name,
    ac.points,
    le.level
FROM account ac,
    level le
WHERE ac.id = 'x'
    AND le.level= (
        SELECT level
        FROM level_points
        WHERE points <= (
                SELECT points
                FROM accounts
                WHERE id = 'x'
                )
        ORDER BY level DESC LIMIT 1
        );

要为所有玩家获取它，您可以执行以下操作:

SELECT ac.id,
    ac.name,
    ac.points,
    le.level
FROM accounts ac,
    level_points le
WHERE le.level= (
        SELECT level
        FROM level_points
        WHERE points <= (
                SELECT points
                FROM accounts ac2
                WHERE ac2.id = ac.id
                )
        ORDER BY level DESC LIMIT 1
        );

检查this fiddle看看它的工作原理