mysql - 有人可以帮我找到问题吗？ MySQL/子查询

Question

我正在尝试插入由用户点导出的“排名”数据。但是每次我使用子查询插入时，它都会返回NULL值。

create table rank_info(
rank_num INT,
min_point INT,
max_point INT,
primary key(rank_num)
);



create table user(
    num INT,
    name varchar(45),
    point INT,
    user_rank INT,
    primary key(num),
    foreign key(user_rank) references rank_info(rank_num)
);

    insert into rank_info values (1, 200, 300);
insert into rank_info values (2, 100, 199);
insert into rank_info values (3, 0, 99);
insert into user values(1, 'jake', 150, 
(select rank_num from (
(select rank_num from rank_info, user
 where  user.point between min_point and max_point))
 a ));

<< 这就是我被困住的地方。永远谢谢你。

Answer 1

您应该使用带有 join 的更新(而不是插入)

 update  user  u
 inner join  rank_info  r on u.point between r.min_point and r.max_point
 set u.rank_num = = r.rank_num