如何一次调用存储过程并更改数据库表
我现在确实为此苦苦挣扎了一段时间。 我有一个MySQL数据库和很多数据。这是我必须为大学创建的一个一级方程式网站。
现在 j_tracks_rounds_results 表已填充数据，但有一列未填充。这是排名栏。
我创建了一个存储过程，如下所示：
DROP PROCEDURE `sp_rank`//
delimiter @@
PROCEDURE `sp_rank`(sid INT)
begin
set @i = 0;
SELECT `performance`, subround_id, @i:=@i+1 as rank
FROM `j_tracks_rounds_results`
where subround_id = sid
order by `subround_id`,`performance`;
end
delimiter ;
输出如下：
rec.ID PERFORMANCE SUBROUND_ID RANK
87766 100,349114516829 1 1
93040 101,075635087628 1 2
88851 101,664302543497 1 3
它获取结果并向其广告排名，按性能排序，以便最低的性能获得排名1等...
我想要实现的是把排名回到表中。就像针对“rank”列的 ALTER 命令。
我怎样才能做到这一点？
I'm really struggeling with this for some time now.
I have a MySQL database and a lot of data. It is a formula1 website i have to create for college.
Right now the j_tracks_rounds_results table is filled with data but one column is not filled out. It's the rank column.
I created a stored procedure as the following:
DROP PROCEDURE `sp_rank`//
delimiter @@
PROCEDURE `sp_rank`(sid INT)
begin
set @i = 0;
SELECT `performance`, subround_id, @i:=@i+1 as rank
FROM `j_tracks_rounds_results`
where subround_id = sid
order by `subround_id`,`performance`;
end
delimiter ;
The output is like the following:
rec.ID PERFORMANCE SUBROUND_ID RANK
87766 100,349114516829 1 1
93040 101,075635087628 1 2
88851 101,664302543497 1 3
It gets the results and ads a rank to it, sorted on performance so the lowest performance gets rank1 etc...
What i am trying to achieve is to put the rank back into the table. Like an ALTER command for the column "rank".
How would i be able to accomplish this?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
基本上不会...
创建表来保存键（rec.id？）和排名。截断它以删除以前的结果，然后使用 insert into ... 与您的查询，然后加入它。
您确实不想在正常运行中更改表，保证有人会在该列不存在时使用该列，然后当您查看错误时，它会......
人们只是不寻找如果表结构在应用程序的生命周期中发生变化，那么就会出现混乱。
Basically don't...
Create table to hold the key (rec.id ?) and the rank. Truncate it to get rid of the previous results then use insert into ... with your query and then join to it.
You really don't want to be altering tables in your normal running, guaranteed some one will use the column when it isn't there, and then when you look at the fault it will be...
People just don't look for table structures changing through the application lifetime, it's a screw up waiting to happen.
您错误地应用了 SQL 语句。您需要 UPDATE 命令，而不是 ALTER。
例如。
更新表 SETrank=@i WHERE subround_id=@idYou are misapplying your SQL statements. You want the UPDATE command, not ALTER.
eg.
UPDATE table SET rank=@i WHERE subround_id=@id