|
我有以下查询：
SELECT
stat.mcq_id,ROUND( stat.total_score / stat.num_taken,2 ) AS avg_score
FROM (
SELECT
user_mcq.mcq_id,SUM( score ) AS total_score,COUNT( user_mcq.id ) AS num_taken
FROM user_mcq
INNER JOIN user ON ( user.id = user_mcq.user_id )
WHERE user.level_id =3
AND user_mcq.is_complete =1
GROUP BY user_mcq.mcq_id
) AS stat
这会产生：
mcq_id avg_score
1 5.75
2 9.22
6 8.81
7 8.94
14 7.00
16 9.46
我想使用它来更新另一个名为mcq的表,使用结果中的mcq_id来匹配mcq.id
我尝试了以下,但没有成功：
UPDATE mcq SET mcq.avg_score = stats.avg_score FROM (
SELECT
stat.mcq_id,COUNT( user_mcq.id ) AS num_taken
FROM user_mcq
INNER JOIN user ON ( user.id = user_mcq.user_id )
WHERE user.level_id =3
AND user_mcq.is_complete =1
GROUP BY user_mcq.mcq_id
) AS stat
) AS stats
WHERE mcq.id = stats.mcq_id;
这给出了：
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM ( SELECT stat.mcq_id,2 ) A' at line 1
最佳答案
我认为您可以使用与表的连接并更新列,如下所示：
UPDATE mcq,(SELECT
stat.mcq_id,2 ) AS avg_score
FROM (SELECT
user_mcq.mcq_id,SUM(score ) AS total_score,COUNT( user_mcq.id ) AS num_taken
FROM user_mcq
INNER JOIN user ON ( user.id = user_mcq.user_id )
WHERE user.level_id =3
AND user_mcq.is_complete =1
GROUP BY user_mcq.mcq_id
) AS stat
) AS stats
SET mcq.avg_score = stats.avg_score
WHERE mcq.mcq_id = stats.mcq_id;
（编辑：淮安站长网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
