三个表之间的SQL查询-哪个连接？

Question

我正试图在mySQL中创建一个查询，以便从数据库中已有的表中选择数据(Moodle是特定的)。我意识到这个计划不是很好，但这是来自Moodle数据库的‘评级’插件安装。

当前的数据结构如下所示：

mdl_ranking_points

CREATE TABLE `mdl_ranking_points` (
  `id` bigint(10) NOT NULL AUTO_INCREMENT,
  `userid` bigint(10) NOT NULL,
  `courseid` bigint(10) NOT NULL,
  `points` decimal(10,1) NOT NULL,
  `timecreated` bigint(10) NOT NULL,
  `timemodified` bigint(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COMMENT='Points of users'

​

​

mdl_ranking_logs

 CREATE TABLE `mdl_ranking_logs` (
      `id` bigint(10) NOT NULL AUTO_INCREMENT,
      `rankingid` bigint(10) NOT NULL,
      `courseid` bigint(10) NOT NULL,
      `course_modules_completion` bigint(10) NOT NULL,
      `points` decimal(10,1) NOT NULL,
      `timecreated` bigint(10) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8 COMMENT='Points of users'

​

​

mdl_user

 CREATE TABLE `mdl_user` (
      `id` bigint(10) NOT NULL AUTO_INCREMENT,
      `auth` varchar(20) NOT NULL DEFAULT 'manual',
      `confirmed` tinyint(1) NOT NULL DEFAULT '0',
      `username` varchar(100) NOT NULL DEFAULT '',
      `password` varchar(255) NOT NULL DEFAULT '',
      `idnumber` varchar(255) NOT NULL DEFAULT '',
      `firstname` varchar(100) NOT NULL DEFAULT '',
      `lastname` varchar(100) NOT NULL DEFAULT '',
      `email` varchar(100) NOT NULL DEFAULT '',
      PRIMARY KEY (`id`),
      UNIQUE KEY `mdl_user_mneuse_uix` (`mnethostid`,`username`),
      KEY `mdl_user_fir_ix` (`firstname`),
      KEY `mdl_user_las_ix` (`lastname`),
      KEY `mdl_user_idn_ix` (`idnumber`),
    ) ENGINE=InnoDB AUTO_INCREMENT=1045 DEFAULT CHARSET=utf8 COMMENT='One record for each person'

mdl_course

CREATE TABLE `mdl_course` (
  `id` bigint(10) NOT NULL AUTO_INCREMENT,
  `category` bigint(10) NOT NULL DEFAULT '0',
  `sortorder` bigint(10) NOT NULL DEFAULT '0',
  `fullname` varchar(254) NOT NULL DEFAULT '',
  `shortname` varchar(255) NOT NULL DEFAULT '',
  `idnumber` varchar(100) NOT NULL DEFAULT '',
  `summary` longtext,
  `summaryformat` tinyint(2) NOT NULL DEFAULT '0',
  `format` varchar(21) NOT NULL DEFAULT 'topics',
  `showgrades` tinyint(2) NOT NULL DEFAULT '1',
  `newsitems` mediumint(5) NOT NULL DEFAULT '1',
  `startdate` bigint(10) NOT NULL DEFAULT '0',
  `enddate` bigint(10) NOT NULL DEFAULT '0',
  `marker` bigint(10) NOT NULL DEFAULT '0',
  `maxbytes` bigint(10) NOT NULL DEFAULT '0',
  `legacyfiles` smallint(4) NOT NULL DEFAULT '0',
  `showreports` smallint(4) NOT NULL DEFAULT '0',
  `visible` tinyint(1) NOT NULL DEFAULT '1',
  `visibleold` tinyint(1) NOT NULL DEFAULT '1',
  `groupmode` smallint(4) NOT NULL DEFAULT '0',
  `groupmodeforce` smallint(4) NOT NULL DEFAULT '0',
  `defaultgroupingid` bigint(10) NOT NULL DEFAULT '0',
  `lang` varchar(30) NOT NULL DEFAULT '',
  `theme` varchar(50) NOT NULL DEFAULT '',
  `timecreated` bigint(10) NOT NULL DEFAULT '0',
  `timemodified` bigint(10) NOT NULL DEFAULT '0',
  `requested` tinyint(1) NOT NULL DEFAULT '0',
  `enablecompletion` tinyint(1) NOT NULL DEFAULT '0',
  `completionnotify` tinyint(1) NOT NULL DEFAULT '0',
  `cacherev` bigint(10) NOT NULL DEFAULT '0',
  `calendartype` varchar(30) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `mdl_cour_cat_ix` (`category`),
  KEY `mdl_cour_idn_ix` (`idnumber`),
  KEY `mdl_cour_sho_ix` (`shortname`),
  KEY `mdl_cour_sor_ix` (`sortorder`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COMMENT='Central course table';

我需要用以下数据返回一个查询：

lastname _~_

我尝试了以下的查询，但这并不返回我需要的(它似乎只计算用户id的，已经获得的分数。

SELECT lastname AS academy,
    SUM(points) AS score,
    COUNT(*) AS users,
    (SUM(points)/COUNT(*)) AS norm
FROM mdl_ranking_points r
LEFT JOIN mdl_user ON r.userid = mdl_user.id
LEFT JOIN mdl_course ON r.courseid = mdl_course.id
GROUP BY lastname
ORDER BY norm DESC

任何帮助都将不胜感激。我可能完全是走错路了。

Answer 1

I've tried the following query, but this doesnt return what I need (it seems that it only counts user id's that have been awarded points.

这是因为连接中的顺序。这将返回所有用户，即使他们没有奖励一些积分。

FROM mdl_user
    LEFT OUTER JOIN mdl_ranking_points
    LEFT OUTER JOIN mdl_course

或用右外部联接更改查询。