MySQL Query Design for Latest Post per Forum
i have this 3 tables
forums_forum
+-----+--------+-------------+-------+-----+
| fid | name | description | index | cid |
+-----+--------+-------------+-------+-----+
| 36 | gdghdf | hjghj | 54 | 5 |
| 45 | yutuy | iuyi 开发者_StackOverflow中文版| 99 | 6 |
+-----+--------+-------------+-------+-----+
forums_threads
+----+-----+-----+-------+-------+------+-----------+------+
| id | tid | fid | moved | mfrom | view | important | lock |
+----+-----+-----+-------+-------+------+-----------+------+
| 1 | 4 | 36 | 0 | NULL | 0 | 0 | 0 |
| 2 | 12 | 36 | 0 | NULL | 7 | 0 | 0 |
| 3 | 9 | 15 | 0 | NULL | 0 | 0 | 0 |
+----+-----+-----+-------+-------+------+-----------+------+
forums_posts
+----+-------+--------+--------+---------------------+--------+--------+-----+
| id | title | detail | author | date | edited | editby | tid |
+----+-------+--------+--------+---------------------+--------+--------+-----+
| 1 | asfsd | sdfsd | 1 | 2010-07-01 21:31:29 | 0 | NULL | 4 |
+----+-------+--------+--------+---------------------+--------+--------+-----+
I'm trying to create query which return result -> for every unique 'fid', one row from 'forums_posts' (ORDER BY 'date').
forums_forum
.fid
= forums_threads
.fid
forums_threads
.tid
= forums_posts
.tid
Thanks
This is the venerable greatest-n-per-group problem that comes up frequently on Stack Overflow. Here's a solution given your tables:
SELECT p.* FROM forums_posts p JOIN forums_threads t ON p.tid = t.tid
WHERE NOT EXISTS (
SELECT * FROM forums_posts p2 JOIN forums_threads t2 ON p2.tid = t2.tid
WHERE t.fid = t2.fid AND p.date < p2.date
);
Well i suggest some JOINs for you.
SELECT C.date, C.title, A.name
FROM forums_forum A
JOIN forums_threads B ON A.fid=B.fid
JOIN forums_posts C ON B.tid=C.tid
ORDER BY C.date DESC LIMIT 1
or .. not tested:
SELECT MAX(c.date), C.date, C.title, A.name
FROM forums_forum A
JOIN forums_threads B ON A.fid=B.fid
JOIN forums_posts C ON B.tid=C.tid
LIMIT 1
;-)
p.s. you might get troubles by naming a column "date" as well as "index" and "view", better use other names.
p.p.s. are there fids (i assume this is your primary key and should be unique) whiche occure more then once?
Alternative to a JOIN
, you can add a column to your forums_forums
table with a name like last_updated
. On every posting to a thread, simply run an additional UPDATE
like.
UPDATE forums SET last_updated = NOW()
Then, to get the forums by the order of forums, your SELECT
becomes much simpler, and performant.
SELECT * FROM forums_forum ORDER BY last_updated DESC
精彩评论