我有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] 删除。
我来说两句