MySQL:如何为基于范围而不是外键的关系编写查询？

Question

我在 MySQL 中有三个相关的表，但在技术上没有通过外键相互链接。它们是:用户、级别和类(class)。

users 表有一个 karma 列，一个数字类型。根据这个 karma 数字，我想知道用户的 level，这是我从 levels 表中检索到的。这不是一种硬性关系，因为级别与业力值的范围相关联，如下所示:

• 1 级(最低业力:0)
• 2 级(最低业力值:100)
• 3 级(最低业力值:500)
• ...

因此，如果用户的 karma 为 400，则返回值应为 2。为了使事情稍微复杂一些，级别编号表示用户的 class，其定义存储在类表。这又是一个范围关系:

• Ant 类(最低级别 1)
• 猫头鹰类(最低 5 级)
• 狮子类(最低等级 100)
• ...

总而言之，我们讨论的是三个基于范围值彼此具有隐式关系的表。我的问题涉及如何有效地查询这些表。我一个很常见的需求是根据条件获取一个或多个用户的信息，结果集应该包含用户详细信息，以及用户的级别和类别。

对于单个用户，我设法编写了这个工作正常的查询:

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;

但是，如果我通过离开 WHERE u.id = ?并删除 LIMIT 1，从而查询用户的列表，我得到了所有三个表的组合。通常你会通过对键进行内部连接来减少行，但由于这是一个范围检查，那行不通。我尝试在内部连接条件中使用范围检查，但结果相同。即使使用分组我也无法得到我想要的结果。

在绝望的尝试中，我提出了这个有效的查询:

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 ?,?

但是，对我来说这似乎非常低效。基本上我在这里所做的是为级别和类表中我需要的每个 column(!) 编写一个子查询。如果我在一个子查询中查询级别表或类表的多列，它会再次返回所有值的组合。我觉得我的 DB 技能存在差距，我明显缺少一些东西，一个我不知道的功能......

你能帮我吗？如何针对一组组合了三个表中的列但未通过键(而是范围)链接的行编写高效查询？

PS:我知道如果我将此模式非规范化以将级别和类组合到用户表中，我可以大大简化场景，但我需要这个有一个特定的原因，相信我.

Answer 1

如果您可以修改 Level 和 Class 表以在行中包含实际范围(即 Level 表中有 minkarma 和 maxkarma，Class 表中有 minlevel 和 maxlevel)，您应该能够在您的加入条件，它只会为每个用户返回一行。

例子:

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

您获得多行的原因是您通过仅过滤最小值来匹配多个级别和类别。