SQL查询,在每个组中选择5个最新的 [英] SQL Query, Selecting 5 most recent in each group
本文介绍了SQL查询,在每个组中选择5个最新的的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有这张桌子
CREATE TABLE `codes` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`language_id` int(11) unsigned NOT NULL,
`title` varchar(60) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`time_posted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
language_id是指记录所用的语言. 我想做的是检索每种语言_id 中的五个最新记录(ORDER BY time_posted DESC LIMIT 5)的列表.我可以使用许多不同的SQL查询在PHP中循环执行此操作,但我觉得有一种更简单的方法.
language_id refers to what language the record is in. What I would like to do is retrieve a list of the five most recent (ORDER BY time_posted DESC LIMIT 5) records in each language_id. I could do this in a loop within PHP with a number of different SQL queries but I feel there is a simpler way.
我必须得到一本有关SQL的书,哈哈.
I've got to get a book on SQL, haha.
谢谢.
推荐答案
这是我在MySQL中解决此每组前N个"类型的查询的方法:
Here's how I solve this "top N per group" type of query in MySQL:
SELECT c1.*
FROM codes c1
LEFT OUTER JOIN codes c2
ON (c1.language_id = c2.language_id AND c1.time_posted < c2.time_posted)
GROUP BY c1.id
HAVING COUNT(*) < 5;
另请参阅"
这篇关于SQL查询,在每个组中选择5个最新的的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文