MySQL Group By 获取具有最小日期值的标题列

发布于 2024-11-01 15:33:54 字数 594 浏览 1 评论 0原文

我有一个像这样的查询：

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

它返回最后 10 个 sid 组。

对于每个组，我需要

尝试使用的

SELECT *, min(datestart)

具有最低日期开始值的行的标题列，但这不会返回具有最小日期开始值的行，而只是返回最低日期开始。我需要从最低日期开始的标题。

（相关）表结构：

    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;

有什么想法吗？

原文

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

我最亲爱的 2024-11-08 15:33:54

更新的答案

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

在（sid，datestart）上使用复合索引

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)

回复收藏 0 原文

猫腻 2024-11-08 15:33:54

尝试这个查询。您将得到预期的结果。如果它不起作用，请更改 Table_2.datestart > Table_1.datestart by Table_2.datestart < Table_1.datestart

SELECT title, datestart
FROM `table` AS Table_1
LEFT JOIN `table` AS Table_2 ON (Table_2.sid = Table_1.sid AND Table_2.datestart > Table_1.datestart)
Table_2.sid IS NULL;

已编辑的查询

SELECT Table_1.title, Table_1.datestart
FROM `table` AS Table_1
LEFT JOIN `table` AS Table_2 ON (Table_2.sid = Table_1.sid AND Table_2.datestart > Table_1.datestart)
Table_2.sid IS NULL;

Try this query. You will get expected results. If it don't work change Table_2.datestart > Table_1.datestart by Table_2.datestart < Table_1.datestart

SELECT title, datestart
FROM `table` AS Table_1
LEFT JOIN `table` AS Table_2 ON (Table_2.sid = Table_1.sid AND Table_2.datestart > Table_1.datestart)
Table_2.sid IS NULL;

Edited query

SELECT Table_1.title, Table_1.datestart
FROM `table` AS Table_1
LEFT JOIN `table` AS Table_2 ON (Table_2.sid = Table_1.sid AND Table_2.datestart > Table_1.datestart)
Table_2.sid IS NULL;

回复收藏 0 原文

~没有更多了~