英文:
SELECT an additional column from another table based on a value between 2 other values
问题
以下是翻译好的部分：
我有两个相当基本的表格：
表格：user
id|姓名|经验
1|测试|270
2|foo |510
表格：level
级别|经验
1|0
2|100
3|250
4|500
我想要一个相当简单的查询，根据提供的经验值给我我的级别。显然，我从类似以下的东西开始：
SELECT * FROM user WHERE id = 1
但然后我在构建一个带有连接的查询上失败了，它可以比较我的值并找到应该返回的“下一个”级别。所以在我的示例中，我的`id = 1`的行应该返回3，而`id = 2`应该返回4。
我的预期输出将是这样的表格：
id|姓名|经验|级别
1|测试|270|3
2|foo |510|4
我甚至需要在这里进行连接吗，还是有类似的东西？我可以使用子查询吗？我是否需要以某种方式使用`BETWEEN`进行检查？
英文:
I have 2 pretty basic tables:
Table: user
id|name|exp
1|test|270
2|foo |510
Table: level
level|exp
1|0
2|100
3|250
4|500
I want to have a pretty simple query, which gives me my level, according to my provided exp-value. Obviously I started with something like:
SELECT * FROM user WHERE id = 1
But then I failed on building a query with a join, which compared my value and finds the "next" level it should return. So in my example, my row with id = 1 should return 3, while id = 2 should return 4.
My expected output would be a table like this:
id|name|exp|level
1|test|270|3
2|foo |510|4
Do I even have to join here or is there anything similar? Can I have a subselect? Do I have to check somehow with BETWEEN?
答案1
得分: 1
以下是已翻译的内容：
最简单的方法是使用相关子查询：
SELECT u.*,
(SELECT l.level FROM level l WHERE u.exp >= l.exp ORDER BY l.exp DESC LIMIT 1) AS level
FROM user u;
查看演示。<br/>
英文:
The simplest way to do this would be with a correlated subquery:
SELECT u.*,
(SELECT l.level FROM level l WHERE u.exp >= l.exp ORDER BY l.exp DESC LIMIT 1) AS level
FROM user u;
See the demo.<br/>
答案2
得分: 1
这是一种使用 group by 和 inner join 的方法来实现的：
SELECT u.*, max(l.level) as level
from users u
inner join levels l on u.exp >= l.exp
group by u.id
英文:
This is a way to do it using group by and inner join :
SELECT u.*, max(l.level) as level
from users u
inner join levels l on u.exp >= l.exp
group by u.id
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论