MySql查询限制和排序连接状态 [英] MySql query limiting and ordering on join statament

问题描述

我有两个作为作者和文章的表格。我想获得每个作者的最新文章列表。我只想为一个作者写一篇文章。我希望它是最新的。但是，我什至不知道从哪里开始这个SQL查询。
$ b $ h1>编辑

我的表结构可以这样简单化：

 作者：
 id 
名字
状态
 seo 
 articles：
 author_id 
 title 
 text 
 date $ b $ seo 
  

编辑2

我想出了这样的事情，是否有任何明显的错误可以在这里看到： / p>

  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 
  

解决方案

好的，我找到了我需要做的：

  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.

Edit

My table structure can be simplefied like this:

authors:
 id
 name
 status
 seo   
articles:
  author_id
  title
  text
  date
  seo

Edit 2

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.

这篇关于MySql查询限制和排序连接状态的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！