时间戳

首页 > 解决方案 > 如何在子查询中选择多个值，但将每个值分配给匹配的父选择值？

mysql - 如何在子查询中选择多个值，但将每个值分配给匹配的父选择值？

问题描述

抱歉，问题的罗嗦混乱。我有一个从User表中提取数据的查询，我想从course_modules_completion表中为每个用户和课程中的每个活动提取完成状态。我无法将来自不同表的多个值链接到每行的用户。

我能够从User表中获得我想要的信息。然后我加入多个表，以便能够从 course_modules_completion 表中提取 completionstate 字段。我需要做的是为每个用户提取与课程中每个活动相对应的完成状态的值。

SELECT u.username AS 'ID'
,u.firstname AS 'Names'
,u.lastname AS 'Lastnames'

,(SELECT completionstate FROM prefix_course_modules_completion cmc
  INNER JOIN prefix_user u ON cmc.userid = u.id
  INNER JOIN prefix_course_modules cm ON cmc.coursemoduleid = cm.id
  INNER JOIN prefix_course c ON cm.course = c.id
  INNER JOIN prefix_resource r ON c.id = r.course
  WHERE (r.id = 6) AND (c.id = 5)
) AS 'Activity 1'

FROM prefix_user u LEFT JOIN prefix_grade_grades g ON u.id = g.userid
INNER JOIN prefix_course_modules_completion cmc ON cmc.userid = u.id
INNER JOIN prefix_course_modules cm ON cmc.coursemoduleid = cm.id
INNER JOIN prefix_course c ON cm.course = c.id
INNER JOIN prefix_resource r ON c.id = r.course

WHERE (u.id > 7) AND (u.firstaccess > 0) AND (c.id = 5)                                     

GROUP BY u.username
ORDER BY u.firstname ASC

我所期望的是这样的：

ID  - Name - Last name - Email   -  Activity 1 - Activity 2 - Test
382 - John - Johnson   - email@e.com  - Seen  -  Not seen  -  Failed
17  - Mark - Markson   - email2@e.com - Seen  -    Seen    -  Passed

相反，我得到的是同一用户的多行，它们具有所需的值或所有用户所需的相同值，就好像它对用户没有任何区别一样。正如您在上面的代码中看到的那样，我尝试使用仅针对完成状态值的子查询，但它返回多行，因此出现错误。

此外，我想将completionstate列中的值显示为文本。该列中有四个值：0、1、2、3，我想做的是将每个值分别转换为“未看到”、“已看到”、“完成并通过”和“完成并失败”。

因为我是 SQL 的初学者，所以我无法确定我的问题是由于逻辑错误、编码错误还是两者兼而有之。

编辑：稍微修剪一下代码以使这个问题更简单。

标签： mysqlsqlmoodle

解决方案

你想要做的是一种枢轴，所以你可以在没有相关子查询的情况下做到这一点。

SELECT u.username AS 'ID'
        ,u.firstname AS 'Names'
        ,u.lastname AS 'Lastnames'
        ,u.email AS 'Email'
        ,c.fullname AS 'Course'
        ,(LEFT(g.finalgrade,2)) AS 'Final grade'
        ,ELT(MAX(IF(r.id = 6, completion_state, NULL))+1,
            'Not seen', 'Seen', 'Completed and passed', 'Completed and failed') AS `Activity 1`
        ,ELT(MAX(IF(r.id = 7, completion_state, NULL))+1,
            'Not seen', 'Seen', 'Completed and passed', 'Completed and failed') AS `Activity 2`
        ,ELT(MAX(IF(r.id = 8, completion_state, NULL))+1,
            'Not seen', 'Seen', 'Completed and passed', 'Completed and failed') AS `Activity 3`
FROM prefix_user u LEFT JOIN prefix_grade_grades g ON u.id = g.userid
INNER JOIN prefix_course_modules_completion cmc ON cmc.userid = u.id
INNER JOIN prefix_course_modules cm ON cmc.coursemoduleid = cm.id
INNER JOIN prefix_course c ON cm.course = c.id
INNER JOIN prefix_resource r ON c.id = r.course

WHERE (u.id > 7) AND (u.firstaccess > 0) AND (c.id = 5)                                     

GROUP BY u.id
ORDER BY u.firstname ASC

yu 也应该 group byu.id而不是u.username。这将允许您选择在功能上依赖于此主键的其他列，而无需使用聚合函数。

推荐阅读