MySQL查询 - 每个组最近的条目 [英] MySQL Query - recent entries per group
问题描述
假设我有一个表blog_posts,其中有一个id列, (所有唯一的,自动递增的),可以是'category1'或'category2'或'category3'值的post_cat,以及可以是'online'或'offline'值的publish_status列。 b
$ b
如何为每个类别选择最新的条目?
我现在有以下几种,但它几乎感觉像是随机选择:
select * FROM`blog_posts` WHERE(publish_status ='online')GROUP BY post_cat ORDER BY id DESC LIMIT 10
我会保持真实简单并使用触发器在类别表中保留last_post_id,以便您可以轻松地重新加入posts表 - 如下所示:
简单查询
select
pc.cat_id,
pc.name,
u.username,
bp。*
from
post_category pc
内部连接blog_post bp,pc.last_post_id = bp.post_id
bp.user_id内部加入用户u = u.user_id
order by
pc.cat_id;
+ -------- + ------ + ---------- + --------- + ---- ----- + --------------------- +
| cat_id |名称|用户名| post_id | user_id | post_date |
+ -------- + ------ + ---------- + --------- + -------- - + --------------------- +
| 1 | cat1 | bar | 3 | 2 | 2011-02-09 12:45:33 |
| 2 | cat2 | BAR | 5 | 3 | 2011-02-09 12:45:33 |
| 3 | cat3 | f00 | 4 | 1 | 2011-02-09 12:45:33 |
+ -------- + ------ + ---------- + --------- + -------- - + --------------------- +
表格
drop table if exists post_category;
create table post_category
(
cat_id smallint unsigned not null auto_increment主键,
name varchar(255)unique不为null,
last_post_id int unsigned null,
key(last_post_id)
)
engine = innodb;如果存在用户,
drop table;
创建表用户
(
user_id int unsigned not null auto_increment主键,
username varbinary(32)unique不为空
)
engine = innodb;
drop table if exists blog_post;
create table blog_post
(
post_id int unsigned not null auto_increment主键,
user_id int unsigned not null,
post_date datetime not null,
key( post_date,user_id)
)
engine = innodb;
drop table if exists blog_post_category;
create table blog_post_category
(
cat_id smallint unsigned not null,
post_id int unsigned not null,
主键(cat_id,post_id)
)
引擎= innodb;
触发器
定界符#
在插入blog_post之前创建触发器blog_post_before_ins_trig
为每行
begin
set new.post_date = now();
结束#
创建触发器blog_post_category_before_ins_trig插入blog_post_category之前插入每行
begin
update post_category set last_post_id = new.post_id其中cat_id = new。 CAT_ID;
结束#
分隔符;
测试数据
插入到post_category(name)值('cat1'),('cat2'),('cat3'),('cat4');
插入用户(用户名)值('f00'),('bar'),('BAR'),('alpha'),('beta');
插入到blog_post(user_id)值(1),(1),(2),(1),(3);
insert into blog_post_category(cat_id,post_id)values
(1,1),(1,3),
(2,1),(2,5),
( 3,1),(3,3),(3,4);
希望这有助于:)
I'm trying to select the most recent entries per group in a table.
Say I have a table "blog_posts" which has a column for "id" (all unique, auto incremented), "post_cat" which can be values 'category1' or 'category2' or 'category3', and a "publish_status" column which can be values 'online' or 'offline'.
How can I select the most recent entries for each category?
I have the following right now, but it almost feels like it's selecting randomly:
select * FROM `blog_posts` WHERE (publish_status = 'online') GROUP BY post_cat ORDER BY id DESC LIMIT 10
I'd keep it real simple and use a trigger to maintain a last_post_id in the category table so you can easily join back on the posts table - something like this:
Simple Query
select
pc.cat_id,
pc.name,
u.username,
bp.*
from
post_category pc
inner join blog_post bp on pc.last_post_id = bp.post_id
inner join users u on bp.user_id = u.user_id
order by
pc.cat_id;
+--------+------+----------+---------+---------+---------------------+
| cat_id | name | username | post_id | user_id | post_date |
+--------+------+----------+---------+---------+---------------------+
| 1 | cat1 | bar | 3 | 2 | 2011-02-09 12:45:33 |
| 2 | cat2 | BAR | 5 | 3 | 2011-02-09 12:45:33 |
| 3 | cat3 | f00 | 4 | 1 | 2011-02-09 12:45:33 |
+--------+------+----------+---------+---------+---------------------+
Tables
drop table if exists post_category;
create table post_category
(
cat_id smallint unsigned not null auto_increment primary key,
name varchar(255) unique not null,
last_post_id int unsigned null,
key (last_post_id)
)
engine=innodb;
drop table if exists users;
create table users
(
user_id int unsigned not null auto_increment primary key,
username varbinary(32) unique not null
)
engine=innodb;
drop table if exists blog_post;
create table blog_post
(
post_id int unsigned not null auto_increment primary key,
user_id int unsigned not null,
post_date datetime not null,
key (post_date, user_id)
)
engine=innodb;
drop table if exists blog_post_category;
create table blog_post_category
(
cat_id smallint unsigned not null,
post_id int unsigned not null,
primary key (cat_id, post_id)
)
engine=innodb;
Triggers
delimiter #
create trigger blog_post_before_ins_trig before insert on blog_post
for each row
begin
set new.post_date = now();
end#
create trigger blog_post_category_before_ins_trig before insert on blog_post_category
for each row
begin
update post_category set last_post_id = new.post_id where cat_id = new.cat_id;
end#
delimiter ;
Test Data
insert into post_category (name) values ('cat1'),('cat2'),('cat3'),('cat4');
insert into users (username) values ('f00'),('bar'),('BAR'),('alpha'),('beta');
insert into blog_post (user_id) values (1),(1),(2),(1),(3);
insert into blog_post_category (cat_id, post_id) values
(1,1),(1,3),
(2,1),(2,5),
(3,1),(3,3),(3,4);
Hope this helps :)
这篇关于MySQL查询 - 每个组最近的条目的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!