e8wtxPvq

1drop table if exists users;
2
3create table users
4(
5user_id int unsigned not null auto_increment primary key,
6username varchar(16)
7);
8
9insert into users (username) values ('f00'),('bar'),('ted'),('novag'),('spam'),('raz');
10
11select * from users;
12
13drop table if exists blog;
14
15create table blog
16(
17blog_id int unsigned not null auto_increment primary key,
18blog_user_id int unsigned not null,
19subject varchar(256) not null
20);
21
22insert into blog (blog_user_id, subject) values (1,'f00 blog'), (2,'bar blog'), (3,'ted blog');
23
24select * from blog;
25
26drop table if exists blog_post;
27
28create table blog_post
29(
30post_id int unsigned not null auto_increment primary key,
31blog_id int unsigned not null,
32post_user_id int unsigned not null,
33post mediumtext not null
34);
35
36
37insert into blog_post (blog_id, post_user_id, post) values
38 (1,1,'blog 1 post 1'),
39 (1,2,'blog 1 post 2'),
40 (1,4,'blog 1 post 3'),
41 (1,2,'blog 1 post 4'),
42 (1,5,'blog 1 post 5'),
43 
44 (2,4,'blog 2 post 1'),
45 (2,1,'blog 2 post 2'),
46 (2,6,'blog 2 post 3'),
47 (2,2,'blog 2 post 4'),
48 
49 (3,6,'blog 3 post 1'),
50 (3,6,'blog 3 post 2'),
51 (3,3,'blog 3 post 3');
52 
53select * from blog_post;
54 
55
56select
57 b.blog_id,
58 b.subject,
59 b.blog_user_id,
60 bp.post_id,
61 bp.post_user_id,
62 u.username as post_username,
63 bp.post
64from
65 blog_post bp
66inner join
67(
68select
69 blog_id,
70 max(post_id) as post_id,
71 post_user_id
72from
73 blog_post 
74group by
75 blog_id,
76 post_user_id
77) latest_bp on bp.blog_id = latest_bp.blog_id and bp.post_id = latest_bp.post_id
78inner join blog b on bp.blog_id = b.blog_id
79inner join users u on bp.post_user_id = u.user_id
80where
81b.blog_id = 1
82order by
83 b.blog_id, bp.post_id desc;
84
85 
86/*
87blog_id *	subject *	blog_user_id *	post_id *	post_user_id *	post_username	post *
88-------------------------------------------------------------------------------------------------
891			f00 blog	1				5			5				spam			blog 1 post 5
901			f00 blog	1				4			2				bar				blog 1 post 4
911			f00 blog	1				3			4				novag			blog 1 post 3
921			f00 blog	1				1			1				f00				blog 1 post 1
93*/