更新时间:2023-02-08 10:25:48
好的,我找到了我需要做的:
CREATE TEMPORARY TABLE articles2
SELECT max(date)as ma xdate,author_id
FROM文章
GROUP BY author_id;
SELECT authors.name,authors.seo,articles.seo,articles.title FROM articles JOIN articles2 ON(articles2.author_id = articles.author_id AND articles2.maxdate = articles.date)JOIN作者作者.id = articles.author_id WHERE authors.status = 1
我希望这有助于某人。 >
I have a two tables as authors and articles. I want to get list of latest articles for each author. I want only one article for one author. And I want it to be the latest. But, I couldn't even figure out where to start to this sql query.
My table structure can be simplefied like this:
authors:
id
name
status
seo
articles:
author_id
title
text
date
seo
I came up with something like this, is there any obvious mistakes you can see in here:
SELECT authors.*,
(SELECT articles.title FROM articles WHERE author_id = authors.id ORDER BY articles.date DESC LIMIT 1) as title,
(SELECT articles.seo FROM articles WHERE author_id = authors.id ORDER BY articles.date DESC LIMIT 1) as articleseo
FROM authors
WHERE authors.status = 1
Alright, I found out what I needed to do:
CREATE TEMPORARY TABLE articles2
SELECT max(date) as maxdate, author_id
FROM articles
GROUP BY author_id;
SELECT authors.name, authors.seo, articles.seo, articles.title FROM articles JOIN articles2 ON (articles2.author_id = articles.author_id AND articles2.maxdate = articles.date) JOIN authors on authors.id = articles.author_id WHERE authors.status = 1
I hope this helps someone.