我有2个牌桌玩家:
| ID | PLAYER | WARNINGS |
|----|--------|----------|
| 1 | a | 35 |
| 2 | b | 70 |
| 3 | c | 65 |
警告:
| ID | PLAYER | POWER | ACTIVE | TIMEEND |
|----|--------|-------|--------|---------------|
| 1 | a | 5 | 1 | 1388051312120 |
| 2 | a | 10 | 1 | 1388051312120 |
| 3 | a | 20 | 1 | 1388051312120 |
| 4 | b | 30 | 1 | 1388051312120 |
| 5 | b | 40 | 1 | 1388051312120 |
| 6 | c | 10 | 1 | 1388051312120 |
| 7 | c | 55 | 1 | 1388051312120 |
我想从检查每一行warns
那里warns.active=1
,然后,如果warns.timeend <= NOW()
删除(减)一个warns.power
从players.warnings
的player
和变化warns.active=2
因此,此后的表必须如下所示:
玩家:
| ID | PLAYER | WARNINGS |
|----|--------|----------|
| 1 | a | 0 |
| 2 | b | 0 |
| 3 | c | 0 |
警告:
| ID | PLAYER | POWER | ACTIVE | TIMEEND |
|----|--------|-------|--------|---------------|
| 1 | a | 5 | 2 | 1388051312120 |
| 2 | a | 10 | 2 | 1388051312120 |
| 3 | a | 20 | 2 | 1388051312120 |
| 4 | b | 30 | 2 | 1388051312120 |
| 5 | b | 40 | 2 | 1388051312120 |
| 6 | c | 10 | 2 | 1388051312120 |
| 7 | c | 55 | 2 | 1388051312120 |
我只有那个:UPDATE players,warns SET warns.Active=2, players.Warnings=players.Warnings-warns.Power WHERE (warns.Active=1) AND (warns.TimeEnd <= NOW()) AND (warns.Player=players.Player);
但这给了我:
玩家:
| ID | PLAYER | WARNINGS |
|----|--------|----------|
| 1 | a | 30 |
| 2 | b | 40 |
| 3 | c | 55 |
警告:
| ID | PLAYER | POWER | ACTIVE | TIMEEND |
|----|--------|-------|--------|---------------|
| 1 | a | 5 | 2 | 1388051312120 |
| 2 | a | 10 | 2 | 1388051312120 |
| 3 | a | 20 | 2 | 1388051312120 |
| 4 | b | 30 | 2 | 1388051312120 |
| 5 | b | 40 | 2 | 1388051312120 |
| 6 | c | 10 | 2 | 1388051312120 |
| 7 | c | 55 | 2 | 1388051312120 |
因此,只减去每个玩家的第一行...有什么办法解决该问题吗?对不起,我的英语不好!我希望你能理解我 :)
试试这个:
UPDATE players P
INNER JOIN (SELECT W.PLAYER, SUM(W.POWER) WARNING
FROM WARNS W WHERE W.active = 1 AND W.timeend <= NOW()
GROUP BY W.PLAYER
) A ON P.PLAYER = A.PLAYER
SET P.WARNINGS = P.WARNINGS - A.WARNING ;
UPDATE WARNS
SET active = 2
WHERE active = 1 AND timeend <= NOW();
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句