MySQL查询排序数据有两种方式(MySQL query ordering data two ways)

MySQL查询排序数据有两种方式(MySQL query ordering data two ways)

我正在使用下表跟踪模块中的页面视图：

id | page_id | user_id | session_id | created

每个页面对于某个模块都是唯一的; 因此，第14页仅存在于模块2中。

我正在尝试编写一个查询，告诉我何时开始查看会话（GROUP BY session_id）特定模块，但我的订购时遇到问题。 基本上，对于该分组会话，我想返回最早的“已创建”（查看会话的开始时间）和最新的page_id（它们在模块中获得的距离）。

完成此任务的最佳方法是什么？

I am tracking page views within modules using the following table:

id | page_id | user_id | session_id | created

Every page is unique to a certain module; so page 14 only exists in module 2, for instance.

I am trying to write a query that tells me when a viewing session began (GROUP BY session_id) for a particular module, but I am having trouble with my ordering. Basically, for that grouped session, I want to return the earliest 'created' (the starting time of the viewing session) and the latest page_id (how far they got within the module).

What is the best way to accomplish this?

最满意答案

我想你需要在select中使用子查询：

SELECT a.session_id, MIN(a.created) as start_time, b.page_id AS last_page, b.end_time FROM table a INNER JOIN (SELECT b.session_id, MAX(b.created) as end_time, MAX(b.page_id) FROM table b GROUP BY b.session_id) ON a.session_id = b.session_id GROUP BY a.session_id

I think you'll need to utilise sub-queries in the select:

SELECT a.session_id, MIN(a.created) as start_time, b.page_id AS last_page, b.end_time FROM table a INNER JOIN (SELECT b.session_id, MAX(b.created) as end_time, MAX(b.page_id) FROM table b GROUP BY b.session_id) ON a.session_id = b.session_id GROUP BY a.session_id