- 下图为用户听歌记录表recordTime:
- 下图为歌曲表music:
- 现在需要计算每首歌被听数,并将其保存到歌曲表中,mysql语句为:
UPDATE music D
LEFT JOIN (SELECT
B.musicId,
count(*) AS s_sum
FROM recordTime B
GROUP BY B.musicId) C
ON (C.musicId = D.musicId)
SET D.listenTimes = c.s_sum
WHERE D.musicId =
(
SELECT
E.id FROM
(
SELECT
DISTINCT a.musicId AS id
FROM recordTime A
) E
WHERE E.id = D.musicId
)
4.效果:
5. 参考:
数据库 UPDATE多条记录不同值,同时UPDATE多个字段