MySQL 一起使用 ORDER BY 和 GROUP BY
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10030787/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using ORDER BY and GROUP BY together
提问by Luis
My table looks like this (and I'm using MySQL):
我的表看起来像这样（我正在使用 MySQL）：
m_id | v_id | timestamp
------------------------
6 | 1 | 1333635317
34 | 1 | 1333635323
34 | 1 | 1333635336
6 | 1 | 1333635343
6 | 1 | 1333635349
My target is to take each m_id one time, and order by the highest timestamp.
我的目标是对每个 m_id 取一次，并按最高时间戳排序。
The result should be:
结果应该是：
m_id | v_id | timestamp
------------------------
6 | 1 | 1333635343
34 | 1 | 1333635336
And i wrote this query:
我写了这个查询：
SELECT * FROM table GROUP BY m_id ORDER BY timestamp DESC
But, the results are:
但是，结果是：
m_id | v_id | timestamp
------------------------
34 | 1 | 1333635323
6 | 1 | 1333635317
I think it causes because it first does GROUP_BY and then ORDER the results.
我认为这是因为它首先执行 GROUP_BY 然后对结果进行排序。
Any ideas? Thank you.
有任何想法吗？谢谢你。
回答by Matt Fenwick
One way to do this that correctly uses group by:
一种正确使用的方法group by：
select l.*
from table l
inner join (
select
m_id, max(timestamp) as latest
from table
group by m_id
) r
on l.timestamp = r.latest and l.m_id = r.m_id
order by timestamp desc
How this works:
这是如何工作的：
- selects the latest timestamp for each distinct
m_idin the subquery - only selects rows from
tablethat match a row from the subquery (this operation -- where a join is performed, but no columns are selected from the second table, it's just used as a filter -- is known as a "semijoin"in case you were curious) - orders the rows
- 为
m_id子查询中的每个不同选择最新的时间戳 - 仅从
table与子查询中的一行匹配的行中选择行（此操作 - 执行连接，但未从第二个表中选择列，它仅用作过滤器 - 被称为“半连接”，以防万一很好奇） - 对行进行排序
回答by Mosty Mostacho
If you really don't care about which timestamp you'll get and your v_idis always the same for a given m_iyou can do the following:
如果您真的不关心您将获得哪个时间戳，并且v_id对于给定的时间戳始终相同，m_i您可以执行以下操作：
select m_id, v_id, max(timestamp) from table
group by m_id, v_id
order by timestamp desc
Now, if the v_idchanges for a given m_idthen you should do the following
现在，如果v_id给定的更改，m_id那么您应该执行以下操作
select t1.* from table t1
left join table t2 on t1.m_id = t2.m_id and t1.timestamp < t2.timestamp
where t2.timestamp is null
order by t1.timestamp desc
回答by abinash sahoo
Here is the simplest solution
这是最简单的解决方案
select m_id,v_id,max(timestamp) from table group by m_id;
Group by m_id but get max of timestamp for each m_id.
按 m_id 分组，但获取每个 m_id 的最大时间戳。
回答by Ronak Patel
You can try this
你可以试试这个
SELECT tbl.* FROM (SELECT * FROM table ORDER BY timestamp DESC) as tbl
GROUP BY tbl.m_id
回答by Go ahead
SQL>
查询>
SELECT interview.qtrcode QTR, interview.companyname "Company Name", interview.division Division
FROM interview
JOIN jobsdev.employer
ON (interview.companyname = employer.companyname AND employer.zipcode like '100%')
GROUP BY interview.qtrcode, interview.companyname, interview.division
ORDER BY interview.qtrcode;
回答by Gurpreet Singh
Just you need to desc with asc. Write the query like below. It will return the values in ascending order.
只是你需要用 asc 来描述。编写如下查询。它将按升序返回值。
SELECT * FROM table GROUP BY m_id ORDER BY m_id asc;
回答by Vikas Garhwal
Why make it so complicated? This worked.
为什么要弄得这么复杂？这奏效了。
SELECT m_id,v_id,MAX(TIMESTAMP) AS TIME
FROM table_name
GROUP BY m_id
