目前正试图在某种形式的循环中找到一种方法来完成以下操作（最好是在不影响数据库性能的情况下）。
我有三个表user_hours、user_calendar和hours_statistics。我需要先做：
SELECT user_calendar.date_start,
user_calendar.opportunity_id,
user_hours.user_id,
user_hours.agreed_hours,
user_hours.completed_hours,
user_hours.hours_committed
FROM user_calendar
JOIN user_hours
ON user_calendar.user_calendar_id = user_hours.user_calendar_id
WHERE user_calendar.date_start = CURRENT_DATE()
AND user_hours.completed_hours IS NULL
AND user_hours.hours_committed = 'accepted'
此查询可能返回如下：
https://i.stack.imgur.com/FAV61.png
因此，对于每个返回的opportunity_id和user_id，我想做的是：
UPDATE user_hours
SET completed_hours = agreed_hours,
hours_committed = 'completed'
WHERE opportunity_id = {opportunity_id}
AND user_id = {user_id}
AND hours_committed = 'accepted'
AND completed_hours IS NULL
请注意，此时需要循环使用{opportunity_id}和{user_id}（请参见屏幕截图），因为我们需要查看每个机会上的每个用户。
然后，对于每个更新的记录，我需要获得总小时数，如：
// Get hours they have done to send to statistics data table
SELECT sum(completed_hours) FROM user_hours WHERE user_id = {user_id} AND opportunity_id = {opportunity_id}
// Get the completed hours total somehow as a variable
$completed_hours = (from result above)
// Commit stats
UPDATE hours_statistics SET completed_hours = (completed_hours+$completed_hours)
WHERE user_id = {user_id} AND opportunity_id = {opportunity_id}
有人能帮我把它写成一个过程或某种触发器吗？或者帮助我朝着正确的方向来获得循环这些东西的起点吗？手动进行查询，只需要循环/自动进行统计更新即可运行。
无论何时更新user_hours，您都可以创建一个触发器来更新hours_statistics（根据您的应用程序逻辑，您可能还想为INSERT和DELETE操作添加类似的触发器）。
假设在hours_statistics.(user_id, opportunity_id)上定义了UNIQUE密钥，则可以在触发器中使用INSERT ... ON DUPLICATE KEY UPDATE：
CREATE TRIGGER foo AFTER UPDATE ON user_hours FOR EACH ROW
INSERT INTO hours_statistics (user_id, opportunity_id, completed_hours) VALUES
(OLD.user_id, OLD.opportunity_id, -OLD.completed_hours),
(NEW.user_id, NEW.opportunity_id, +NEW.completed_hours)
ON DUPLICATE KEY UPDATE
completed_hours = completed_hours + VALUES(completed_hours);
然后，您可以使用单个UPDATE语句（使用多表语法将user_hours与user_calendar连接起来）一次性执行对user_hours的所有更新，这将导致上述触发器根据需要更新hours_statistics：
UPDATE user_hours JOIN user_calendar USING (user_calendar_id, opportunity_id)
SET user_hours.completed_hours = agreed_hours,
user_hours.hours_committed = 'completed'
WHERE user_hours.hours_committed = 'accepted'
AND user_hours.completed_hours IS NULL
AND user_calendar.date_start = CURRENT_DATE();