mysql - 将此子查询转换为 JOIN？

Question

有谁知道是否可以将此子查询转换为 JOIN？

SELECT DISTINCT

lastname,
c.fullname,

(SELECT COUNT(lg.action) FROM tbl_log AS lg WHERE lg.userid = u.id AND lg.course = c.id) AS 'Total Course Hits Per Student'

FROM tbl_user AS u    
JOIN tbl_user_enrolments AS ents ON ents.userid = u.id    
JOIN tbl_enrol AS en ON ents.enrolid = en.id    
JOIN tbl_course AS C ON c.id = en.courseid    
JOIN tbl_context AS ctx ON c.id = ctx.instanceid    
JOIN tbl_role_assignments AS ra ON ra.contextid = ctx.id AND ra.userid = u.id       

LOG TABLE     

+-------------+---------------------+------+-----+---------+----------------+  
| Field       | Type                | Null | Key | Default | Extra          |  
+-------------+---------------------+------+-----+---------+----------------+  
| id          |                     | NO   | PRI | NULL    | auto_increment |  
| time        |                     | NO   |     | NULL    |                |  
| userid      |                     | NO   |     | NULL    |                |  
| course      |                     | NO   |     | NULL    |                |  
| action      |                     | NO   |     | NULL    |                |  
+-------------+---------------------+------+-----+---------+----------------+  

USER Table

+--------------+---------------------+------+-----+---------+----------------+  
| Field        | Type                | Null | Key | Default | Extra          |  
+--------------+---------------------+------+-----+---------+----------------+  
| id           |                     | NO   | PRI | NULL    | auto_increment |  
| username     |                     | NO   |     | NULL    |                |  
| userpassword |                     | NO   |     | NULL    |                |  
| lastname     |                     | NO   |     | NULL    |                |  
| firstname    |                     | NO   |     | NULL    |                |  
+--------------+---------------------+------+-----+---------+----------------+  

COURSE table

+--------------+---------------------+------+-----+---------+----------------+  
| Field        | Type                | Null | Key | Default | Extra          |  
+--------------+---------------------+------+-----+---------+----------------+  
| id           |                     | NO   | PRI | NULL    | auto_increment |  
| category     |                     | NO   |     | NULL    |                |  
| fullname     |                     | NO   |     | NULL    |                |  
| shortname    |                     | NO   |     | NULL    |                |   
+--------------+---------------------+------+-----+---------+----------------+  

我通过注册表和上下文表将用户链接在一起。

Answer 1

你不能在 log_table 上查询

SELECT 
    COUNT(tbl_log.action)
    lastname,
    c.fullname,
FROM tbl_log
JOIN tbl_user ON tbl_log.userid = tbl_user.id
JOIN tbl_course ON tbl_log.course = tbl_course.id
GROUP BY tbl_log.userid, tbl_log.course

不知道你还需要其他表吗？如果我没记错的话，这将提供计数、学生姓名和类(class)名称。但是，您只能获得实际的日志，因此对于没有做任何事情的学生来说，不会发生任何事情。

否则在日志表上一个 OUTER JOIN 可能就足够了。我现在没有时间检查 SQL fiddle 。希望这对您有所帮助。