SQL：如何获取最后的项目？
|
我有一个日志，记录了通过我的应用程序的文档。日志如下所示：
TABLE: log
==================================================
| log_id | document_id | status_code | timestamp |
==================================================
| 1 | 10 | 100 | 12345 |
--------------------------------------------------
| 2 | 10 | 200 | 23456 |
--------------------------------------------------
now
没有找到相关结果
已邀请:
3 个回复
贸会
SELECT log.document_id , (UNIX_TIMESTAMP() - log.timestamp) / 60 AS MinutesSinceLastChange FROM log JOIN ( SELECT document_id , MAX(timestamp) AS last_change FROM log GROUP BY document_id HAVING (last_change < (UNIX_TIMESTAMP() - 60 * 10)) <-- that is 10 minutes ) AS grp ON grp.document_id = log.document_id AND grp.last_change = log.timestamp WHERE log.status_code = \"200\" <-- code you want to check钨蜡唤喉晤
SELECT `document_id`, SUBSTR(GROUP_CONCAT(RPAD(`status_code`,5) ORDER BY `timestamp` DESC), 1, 5) AS `last_status`, SUBSTR(GROUP_CONCAT(RPAD(`status_code`,5) ORDER BY `timestamp` DESC), 7, 5) AS `prev_status`, UNIX_TIMESTAMP(SUBSTR(GROUP_CONCAT(FROM_UNIXTIME(`timestamp`) ORDER BY `timestamp` DESC), 1, 19)) AS `last_timestamp`, UNIX_TIMESTAMP(SUBSTR(GROUP_CONCAT(FROM_UNIXTIME(`timestamp`) ORDER BY `timestamp` DESC), 21, 19)) AS `prev_timestamp` FROM `log` GROUP BY `document_id` HAVING `last_timestamp` - `prev_timestamp` > 60*10 AND `last_status` IN (100,200);，X），其中X是status_code长度的最大数目 60 * 10-是10分钟IN（100,200）-状态码您只想获取。迪擅哨乘傅
SELECT log.document_id, log.status_code, max(log.timestamp) - min(log.timestamp) FROM ( SELECT MAX(log_id) as log_id FROM log GROUP BY document_id) latestLog INNER JOIN log latestStatus ON latestStatus.log_id = latestLog.log_id INNER JOIN log on latestStatus.status_code = log.status_code GROUP BY log.document_id, log.status_code HAVING (max(log.timestamp) - min(log.timestamp)) > 600