MySQL 从表中的用户列表中选择每个用户的最后一行

Question

我试图从 Moodle 表中选择列表中每个用户的最后一行。我的查询是

SELECT *
FROM mdl_logstore_standard_log
WHERE eventname='\\core\\event\\user_enrolment_created'
AND courseid=34
AND relateduserid IN(120,128)
GROUP BY relateduserid;`

我使用的表格是:

MariaDB [****_*****]> 描述 mdl_logstore_standard_log;

+-------------------+--------------+------+-----+---------+----------------+
| Field             | Type         | Null | Key | Default | Extra          |
+-------------------+--------------+------+-----+---------+----------------+
| id                | bigint(10)   | NO   | PRI | NULL    | auto_increment |
| eventname         | varchar(255) | NO   |     |         |                |
| component         | varchar(100) | NO   |     |         |                |
| action            | varchar(100) | NO   |     |         |                |
| target            | varchar(100) | NO   |     |         |                |
| objecttable       | varchar(50)  | YES  |     | NULL    |                |
| objectid          | bigint(10)   | YES  |     | NULL    |                |
| crud              | varchar(1)   | NO   |     |         |                |
| edulevel          | tinyint(1)   | NO   |     | NULL    |                |
| contextid         | bigint(10)   | NO   | MUL | NULL    |                |
| contextlevel      | bigint(10)   | NO   |     | NULL    |                |
| contextinstanceid | bigint(10)   | NO   |     | NULL    |                |
| userid            | bigint(10)   | NO   | MUL | NULL    |                |
| courseid          | bigint(10)   | YES  | MUL | NULL    |                |
| relateduserid     | bigint(10)   | YES  |     | NULL    |                |
| anonymous         | tinyint(1)   | NO   |     | 0       |                |
| other             | longtext     | YES  |     | NULL    |                |
| timecreated       | bigint(10)   | NO   | MUL | NULL    |                |
| origin            | varchar(10)  | YES  |     | NULL    |                |
| ip                | varchar(45)  | YES  |     | NULL    |                |
| realuserid        | bigint(10)   | YES  |     | NULL    |                |
+-------------------+--------------+------+-----+---------+----------------+

这个查询的问题是它为我提供了列表中每个用户 ID 的第一行，而我想要最后一行。我尝试按 id desc 订购，但没有任何改变。

Answer 1

你可以试试这个:

SELECT 
L.*
FROM mdl_logstore_standard_log L
INNER JOIN 
(

 SELECT 
  relateduserid,
  MAX(id) AS max_id
 FROM mdl_logstore_standard_log
 WHERE eventname='\\core\\event\\user_enrolment_created'
 AND courseid=34
 AND relateduserid IN(120,128)
 GROUP BY relateduserid
)AS t 
ON L.id = t.max_id

首先获取这些相关用户ID的最大自动增量ID，然后在mdl_logstore_standard_log和t表之间进行内部联接将返回您预期的结果。