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

I’m trying to create a query in mySQL to select data from pre-existing tables on a database (Moodle to be specific). I realise that the scheme isn’t great, but this has come from the Moodle database with the ‘ratings’ plugin installed.

我正在尝试在mySQL中创建一个查询，以从数据库中的预先存在的表中选择数据（Moodle是特定的）。我意识到这个方案不是很好，但这来自Moodle数据库，并安装了“评级”插件。

The current data structure looks like this:

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

mdl_ranking_points

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

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

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

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';

I need to return a query with the following data:

我需要返回一个包含以下数据的查询：

lastname | points | average

姓氏|点|平均

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.

我已经尝试了以下查询，但这并没有返回我需要的内容（似乎只计算已获得积分的用户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

Any help would be much appreciated. I may be approaching this completely the wrong way.

任何帮助将非常感激。我可能完全以错误的方式接近这一点。

1 个解决方案

#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.

This is because of the order in the joins. This will return all users even if they don't have rewarded some points

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

FROM mdl_user
    LEFT OUTER JOIN mdl_ranking_points
    LEFT OUTER JOIN mdl_course

Or change your query with a right outer join.

或者使用右外连接更改查询。