MySQL Group通过获取具有最小日期值的标题列 [英] MySQL Group By get title column with smallest date value

问题描述

我有一个查询：

  SELECT * 
 FROM表$ b $ GROUP BY sid 
 ORDER BY datestart desc 
 LIMIT 10 
  

它返回最后10个sid组。 / p>

对于这些组中的每个组，我需要具有最低datestart值的行的标题列

我尝试过使用

  SELECT *，min（datestart）
  

但没有返回datestart值最小的行，只是最低的datestart。我需要从最低日期开始的标题。

（相关）表结构：

< pre $ CREATE TABLE`table`（
`title` varchar（1000）NOT NULL，
`datestart` timestamp NOT NULL default CURRENT_TIMESTAMP，
`sid `bigint（12）unsigned NOT NULL，
KEY`datestart`（`datestart`）
）ENGINE = InnoDB DEFAULT CHARSET = utf8;


有什么想法？

解决方案

  select t1。* from`table` as t1 
 inner join（ 
从`table`中选择sid，min（datestart）作为老人
 
按sid划分
按老年人划分限制10）按t2 
在t1.sid = t2.sid和t1.datestart = t2.elder 
  

在（sid，datestart）

I have a query like:

SELECT *
FROM table
GROUP BY sid
ORDER BY datestart desc
LIMIT 10

which returns the last 10 sid groups.

For each of these groups, I need the title column of the row with the lowest datestart value

I tried using

SELECT *, min(datestart)

but that didn't return the row with the smallest datestart value, just the lowest datestart. I need the title from the lowest datestart.

(Relevant) Table Structure:

    CREATE TABLE `table` (
  `title` varchar(1000) NOT NULL,
  `datestart` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `sid` bigint(12) unsigned NOT NULL,
  KEY `datestart` (`datestart`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Any ideas?

解决方案

Updated answer

select t1.* from `table` as t1
inner join (
select sid,min(datestart) as elder
from `table`
group by sid
order by elder desc limit 10) as t2
on t1.sid = t2.sid and t1.datestart = t2.elder

Use a composite index on (sid,datestart)

这篇关于MySQL Group通过获取具有最小日期值的标题列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！