我在Postgres 9.6数据库中有两个表，分别是person和grade:
人员:
| id | name | score |
|---|---|---|
| 1 | apple | 8 |
| 4 | berry | 9 |
| 6 | cat | 5 |
| 7 | dog | 2 |
职等:
| grade | min_score | max_score |
|---|---|---|
| D | 0 | 2 |
| C | 3 | 5 |
| B | 6 | 8 |
| A | 9 | 10 |
DDL DML(&A):
create table Person (ID int, name varchar, score int);
create table Grade (grade varchar, min_score int, max_score int);
insert into Person values
(1, 'apple', 8), (4, 'berry', 9), (6, 'cat', 5), (7, 'dog', 2);
insert into Grade values
('D', 0, 2), ('C', 3, 5), ('B', 6, 8), ('A', 9, 10);
I want to construct a query without CASE or JOIN (cannot join, no common key) to list name and grade.
This query does not work:
select * from Grade g
where (select score from Person) between g.min_score and g.max_score;
理想的输出应该是:
| name | grade |
|---|---|
| apple | B |
| berry | A |
| cat | C |
| dog | D |