如何使用MySQL获取最新日期

How can I get the most recent date using MySQL? I tried max but I did not get the result that I want. My table looks like this:

如何使用MySQL获取最新日期?我试过最大但我没有得到我想要的结果。我的表看起来像这样:

+---------+---------+-----------------------+--------+------------+---------+
| Name    | idStore | Name                  | idItem | date       | price   |
+---------+---------+-----------------------+--------+------------+---------+
| walmart |       1 | Red Delicious Apples  |      1 | 2011-10-22 | 0.98000 |
| walmart |       1 | Red Delicious Apples  |      1 | 2011-10-28 | 0.98000 |
| walmart |       1 | Red Delicious Apples  |      1 | 2011-10-28 | 0.98000 |
| walmart |       1 | Red Delicious Apples  |      1 | 2011-11-22 | 0.98000 |
| walmart |       1 | Honeycrisp Apples     |      2 | 2011-10-22 | 1.98000 |
| walmart |       1 | Sonya Apples          |      3 | 2011-10-22 | 2.88000 |
| walmart |       1 | Gold Delicious Apples |      4 | 2011-10-22 | 0.98000 |
| walmart |       1 | Sweet Tango Apples    |      5 | 2011-10-22 | 2.48000 |
| walmart |       1 | Granny Smith Apples   |      6 | 2011-10-22 | 1.28000 |
| walmart |       1 | Fugi Apples           |      7 | 2011-10-22 | 1.38000 |
+---------+---------+-----------------------+--------+------------+---------+

I want to get this table:

我想得到这张桌子:

+---------+---------+-----------------------+--------+------------+---------+
| Name    | idStore | Name                  | idItem | date       | price   |
+---------+---------+-----------------------+--------+------------+---------+
| walmart |       1 | Red Delicious Apples  |      1 | 2011-11-22 | 0.98000 |
| walmart |       1 | Honeycrisp Apples     |      2 | 2011-10-22 | 1.98000 |
| walmart |       1 | Sonya Apples          |      3 | 2011-10-22 | 2.88000 |
| walmart |       1 | Gold Delicious Apples |      4 | 2011-10-22 | 0.98000 |
| walmart |       1 | Sweet Tango Apples    |      5 | 2011-10-22 | 2.48000 |
| walmart |       1 | Granny Smith Apples   |      6 | 2011-10-22 | 1.28000 |
| walmart |       1 | Fugi Apples           |      7 | 2011-10-22 | 1.38000 |
+---------+---------+-----------------------+--------+------------+---------+

I am having hard time figuring this out. Thanks!

我很难搞清楚这一点。谢谢!

5 个解决方案

#1

Online Example Query

在线示例查询

http://data.stackexchange.com/*/q/118881/how-can-i-get-the-most-resent-date-using-mysql

SELECT NameStore, idStore, Name, idItem, max(date) AS date, price
FROM tablename
GROUP by NameStore, idStore, Name, idItem, price
ORDER BY date DESC, idItem ASC

#2

You can use group by:

您可以使用group by:

select NameStore, idStore, Name, idItem, max(date) date, price
from table
group by NameStore, idStore, Name, idItem, price

#3

See SQL Select only rows with Max Value on a Column

请参见SQL仅选择列上具有“最大值”的行

SELECT yt1.*
FROM yourtable yt1
LEFT OUTER JOIN yourtable yt2 ON (yt1.idItem = yt2.idItem AND yt1.date < yt2.date)
WHERE yt2.idItem IS NULL;

#4

-1

put this at the end ORDER BY date DESC

把它放在最后的ORDER BY日期DESC

So make it look like this:

所以让它看起来像这样:

SELECT * FROM `tablename` ORDER BY `date` DESC

Using DISTINCT(Name) or GROUP BY Name in the SQL sentence above, would make one item pop up only once.

在上面的SQL语句中使用DISTINCT(Name)或GROUP BY Name,只会使一个项目弹出一次。

#5

-1

You have to use the GROUP BY clause like this:

你必须像这样使用GROUP BY子句:

SELECT Name, idStore, Name, idItem, date, price 
FROM mytable 
GROUP BY idItem 
ORDER BY date DESC, idItem DESC;

Change the order direction using DESC or ASC. You can learn more about this reading the documentation.

使用DESC或ASC更改订单方向。您可以通过阅读文档了解更多信息。

#1