碰到一个傻逼需求，还好做出来了

1.创建一个选题

2.选题审核通过后变成加成员入阶段（其他成员可以加入）

3.当加入后进入到投票阶段

4.投票计数，根据票数和机构的评分来确定选题的“钥匙”持有人

5.钥匙持有人可以派发任务

最核心的触发器

CREATE TRIGGER keyHolder_trigger
AFTER INSERT ON topic_vote
FOR EACH ROW
BEGIN
DECLARE total_votes INT;
DECLARE cur_votes INT;
DECLARE max_votes INT;
DECLARE max_voter_id INT;
DECLARE max_grade FLOAT;

-- 查询总的选题人数
SELECT COUNT(*) INTO total_votes FROM topic_join WHERE topic_join.topicId = NEW.topicId;
-- 查询已投票选题人数
SELECT COUNT(*) INTO cur_votes FROM topic_vote WHERE topicId = NEW.topicId;

-- 查询当前选题投票第一的人及其票数和评分
SELECT candidate_id, COUNT(*) AS votes, MAX(grade) AS grade INTO max_voter_id, max_votes, max_grade
FROM topic_vote
LEFT JOIN media_rank as a ON candidate_id = media_rank.userId
WHERE topicId = NEW.topicId and a.create_time >= DATE_SUB(DATE_FORMAT(NOW(), '%Y-%m-01'), INTERVAL 1 MONTH) AND a.create_time < DATE_FORMAT(NOW(), '%Y-%m-01')
GROUP BY candidate_id
ORDER BY votes DESC, grade DESC
LIMIT 1;

-- 更新钥匙持有人
IF total_votes= cur_votes THEN
UPDATE topic SET keyHolderId = max_voter_id WHERE id = NEW.topicId;
END IF;
END;