我有一个体育得分和排名的数据库。我有这样的排行榜...
Team Overall Record Conference Record
Team1 5-0 2-0
Team2 4-1 1-1
Team3 3-2 0-2
我有比赛结果表...
Team Result1 Result2 Result3 Result4 Result5 Result6
Team1 W W W W W
Team2 W L W W W
Team3 W L L W W
现在,当我在下一场比赛的Result6中添加“ W”时,是否有办法在排行榜中将Team1记录更新为6-0,或者如果它是“ L”然后将其更改为5-1?
最佳答案
您的表格结构不好,我的报价也很差!
SET @teamname := 'team1';
SELECT @myres := concat(Result1,Result2,Result3,Result4,Result5,Result6) -- and other columns
FROM results
WHERE team = @teamname;
UPDATE sports_score
SET overal = concat(
length(@myres)-length(replace(@myres,'W',''))
,'-',
length(@myres)-length(replace(@myres,'L',''))
)
WHERE team = @teamname ;