mySQL 更新但也未更新
我正在用 mySQL/PHP 编写,但是遇到这个问题我无法理解。这一 PHP 脚本包含两条 SQL 语句。我想要做的是更新一个体育联赛表(一个名为 tblrank 的 SQL 表 - 其中包含许多联赛表,由 TableID 分隔),并指示自上次更新以来球队的排名是上升还是下降。第一段代码有点笨拙,可能可以写得更好(我可以在 MSSQL 中用大约 6 行来完成),它的工作原理是计算排名低于它的团队数量,然后添加一个。它似乎有效……有点,我稍后会解释。
update tblrank AS r
set Rank = 1 + (select count(*) from
(select r2.teamID
from tblrank r2
inner join tblrank r3
where r3.TableID = r2.TableID and r3.TableID = $tableid
and (r3.Points > r2.Points
or (r3.Points = r2.Points and r3.TieBreaker > r2.TieBreaker))) as duh
where duh.teamID = r.teamID
and duh.TableID = r.TableID
and r.TableID = $tableid
然后,运行这段代码来选择要显示的图像。
update tblrank
set image = case when Rank < LastRank then 'up.png'
when Rank > LastRank then 'down.png'
else 'nomove.png' end
where TableID = $tableid
现在,如果我对 $tableid = 1 运行此命令,它就可以正常工作。但是,如果我为 $tableid = 2 运行它,则整个 tblRank 中的所有排名都设置为 1。(在为 $tableid = 2 运行它之前,所有排名均为 1,除了 TableID =1 的记录)。这显然不是我想要的。
这两个语句都在 if(mysql_query($sql)) 条件内,因此我可以验证它们是否已执行。
我使用 MS SQL 比 mySQL 更多,所以我不是专家 - 任何人都可以帮助我解决这个问题,因为我很困惑!我已经检查过这两段代码都执行了。两者之间没有执行其他SQL。
I am writing in mySQL/PHP, but have this problem I cannot get my head around. This one PHP script contains two SQL statements. What I am trying to do is update a sports league table (a SQL table called tblrank - which contains many league tables, separated by TableID), and to indicate if the team has gone up or down since the last time it was updated. This first bit of code is bit clunky and probably could've been written better (I could do it in about 6 lines in MSSQL), it works by counting the number of teams that are ranked lower than it, then adding one. It appears to work... sort of, as I will explain later.
update tblrank AS r
set Rank = 1 + (select count(*) from
(select r2.teamID
from tblrank r2
inner join tblrank r3
where r3.TableID = r2.TableID and r3.TableID = $tableid
and (r3.Points > r2.Points
or (r3.Points = r2.Points and r3.TieBreaker > r2.TieBreaker))) as duh
where duh.teamID = r.teamID
and duh.TableID = r.TableID
and r.TableID = $tableid
Then, this bit of code is run to pick the image to display.
update tblrank
set image = case when Rank < LastRank then 'up.png'
when Rank > LastRank then 'down.png'
else 'nomove.png' end
where TableID = $tableid
Now if I run this for $tableid = 1, it works just fine. But, if I run it for $tableid = 2, then all Ranks in the whole of tblRank are set to 1. (Before running it for $tableid = 2, all the ranks are 1, except for records where TableID =1 ). This is obviously not what I want.
Both statements are inside a if(mysql_query($sql)) conditional, so I can verify if they have executed or not.
I use MS SQL a lot more than mySQL so I'm not an expert - can anyone help me with this as I'm baffled! I have checked that both chunks of code do execute. There is no other SQL executed between the two.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
好吧,我已经修复了它,我不确定出了什么问题,但我已经将更新语句分开,首先创建并填充一个名为Rankcount 的临时表。我这样做是为了更容易了解发生了什么。
然后这行得通...
:)
Well, I've fixed it, I'm not sure what was wrong, but I've split the update statement up, with a temporary table called rankcount being created and populated first. I did this to make it easier to see what was going on.
Then this works...
:)
我就是这样做的。首先将所有
Rank
值初始化为零。连接自然地将每行
r1
与具有相同表和团队且得分较低的行集r2
相匹配。然后,它会将每个匹配行的Rank
加 1。Here's how I'd do it. First initialize all the
Rank
values to zero.The join naturally matches each row
r1
to the set of rowsr2
with the same table and team, and a lower score. Then it incrementsRank
by 1 for each of these matching rows.