获得最近记录的通过日期在mysql

Question 1

我有一个表，其中储存的代码，价格，以及一个日期。

代码类型varchar 价格是型浮动(是的，我知道这是更好地储存作为双), 日期是型的日期

create table prices (
 code varchar(15), 
 price float(5),
 date date
)

所以，

insert into prices (code,price,date) values
('ADV000001','2.1','2021-02-03'),
('ADV000001','0.3','2021-11-22'),
('ADV000001','20.0','2021-11-23'),
('ADV000001','31.4','2017-01-11'),
('ADV000001','99.99','2012-12-09'),
('ADV000123','31.4','2017-04-21'),
('ADV000123','0.4','2016-12-02'),
('ADV000123','31','2012-11-06'),
('ADV000991','3','2000-01-15'),
('ADV000991','1.4','2004-01-13'),
('ADV000991','0.9','2011-01-30'),
('ADV000991','12','2017-01-23'),
('ADV000991','854.82','2004-04-30'),
('ADV000991','231.11','2009-04-15'),
('ADV000991','242.66','2021-04-09'),
('ADV000912','111.1','2021-01-11'),
('ADV000912','1.4','2020-01-11'),
('ADV031242','75.48','2019-03-11'),
('ADV031242','231.42','2019-07-11'),
('ADV011912','1324.11','1994-11-11'),
('ADV000112','685.04','1993-06-11'),
('ADV000777','757.54','2001-06-11'),
('ADV000777','221.74','2002-09-11'),
('ADV000777','1352.12','2012-04-11'),
('ADV000215','856.81','2011-12-11'),
('ADV000202','511.99','2011-11-11')

我尝试使这一选择，但是它不工作非常好，这带来了正确的日期，但是它带给我任何代价、不正确的!!!!

SELECT 
code, price, date 
FROM 
prices 
WHERE 
date = (SELECT MAX(date) FROM prices)

其结果是：

code       price    date
ADV000001   0.3    2021-11-23

理想的情况下，它应该显示每个代码与其相应的价格和最近的日期。

code           price    date
ADV000001      20       2021-11-23
ADV000123      31.4     2017-04-21
ADV000991    242.66     2021-04-09
...          ...          ...

任何想法？也许一次咨询? 一个组通过刑期不工作！

MYSQL MAX()function似乎没有工作,它不小组由代码并不仅带来了我一记录。这里就是链接，可以很快问问你的查询，我将不胜感激。 dbfiddle.英国

Question 2

这是你的查询，但首先!!! 它是不是一个好主意使用SQL关键词作为列名。如果你这样做你必须引tzhe fieldname与反引号。

得到一个结果

SELECT 
CODE, price, DATE 
FROM 
prices 
WHERE 
`date`= (SELECT MAX(DATE) FROM prices);

获得所有结果

SELECT p1.`code`, p1.`maxdate`, p2.`price` FROM (
    SELECT `CODE`, MAX(`DATE`) AS maxdate FROM prices GROUP BY `CODE`) AS p1
LEFT JOIN prices p2 ON p1.`code`= p2.`code`AND p2.`date` = p1.maxdate
ORDER BY p1.`code`;

样本

MariaDB [order]> SELECT 
    -> CODE, price, DATE 
    -> FROM 
    -> prices 
    -> WHERE 
    -> `date`= (SELECT MAX(DATE) FROM prices);
+-----------+-------+------------+
| CODE      | price | DATE       |
+-----------+-------+------------+
| ADV000001 |    20 | 2021-11-23 |
+-----------+-------+------------+
1 row in set (0.06 sec)


MariaDB [order]> SELECT p1.`code`, p1.`maxdate`, p2.`price` FROM (
    -> SELECT `CODE`, MAX(`DATE`) AS maxdate FROM prices GROUP BY `CODE`) AS p1
    -> LEFT JOIN prices p2 ON p1.`code`= p2.`code`AND p2.`date` = p1.maxdate
    -> ORDER BY p1.`code`;
+-----------+------------+---------+
| code      | maxdate    | price   |
+-----------+------------+---------+
| ADV000001 | 2021-11-23 |      20 |
| ADV000112 | 1993-06-11 |  685.04 |
| ADV000123 | 2017-04-21 |    31.4 |
| ADV000202 | 2011-11-11 |  511.99 |
| ADV000215 | 2011-12-11 |  856.81 |
| ADV000777 | 2012-04-11 | 1352.12 |
| ADV000912 | 2021-01-11 |   111.1 |
| ADV000991 | 2021-04-09 |  242.66 |
| ADV011912 | 1994-11-11 | 1324.11 |
| ADV031242 | 2019-07-11 |  231.42 |
+-----------+------------+---------+
10 rows in set (0.01 sec)

MariaDB [order]>

Question 3

做出你的选择查询的"顺"在下降。我说的是"选择"查询获取最新的日期在你的数据库。

Question 4

如果你使用 where `date` = (SELECT MAX(`date`) FROM `prices`) 然后它将只选择一个新的(MAX)从表中要使用的日期的条件。结果显示出只有1个记录。

参照从这个问题

SELECT 
    `prices`.*
FROM 
    `prices`
INNER JOIN (
    SELECT MAX(`date`) AS `maxdate`, `price`, `code`
    FROM `prices`
    GROUP BY `code`
) AS `prices2` 
    ON `prices`.`date` = `prices2`.`maxdate`
ORDER BY `date` DESC

结果：

代码	价格	日期
ADV000001	20	2021-11-23
ADV000991	242.66	2021-04-09
ADV000912	111.1	2021-01-11
ADV031242	231.42	2019-07-11
ADV000123	31.4	2017-04-21
ADV000777	1352.12	2012-04-11
ADV000215	856.81	2011-12-11
ADV000202	511.99	2011-11-11
ADV011912	1324.11	1994-11-11
ADV000112	685.04	1993-06-11

Bernd Buffen · Answer 1 · 2021-11-24T03:35:13

这是你的查询，但首先!!! 它是不是一个好主意使用SQL关键词作为列名。如果你这样做你必须引tzhe fieldname与反引号。

得到一个结果

SELECT 
CODE, price, DATE 
FROM 
prices 
WHERE 
`date`= (SELECT MAX(DATE) FROM prices);

获得所有结果

SELECT p1.`code`, p1.`maxdate`, p2.`price` FROM (
    SELECT `CODE`, MAX(`DATE`) AS maxdate FROM prices GROUP BY `CODE`) AS p1
LEFT JOIN prices p2 ON p1.`code`= p2.`code`AND p2.`date` = p1.maxdate
ORDER BY p1.`code`;

样本

MariaDB [order]> SELECT 
    -> CODE, price, DATE 
    -> FROM 
    -> prices 
    -> WHERE 
    -> `date`= (SELECT MAX(DATE) FROM prices);
+-----------+-------+------------+
| CODE      | price | DATE       |
+-----------+-------+------------+
| ADV000001 |    20 | 2021-11-23 |
+-----------+-------+------------+
1 row in set (0.06 sec)


MariaDB [order]> SELECT p1.`code`, p1.`maxdate`, p2.`price` FROM (
    -> SELECT `CODE`, MAX(`DATE`) AS maxdate FROM prices GROUP BY `CODE`) AS p1
    -> LEFT JOIN prices p2 ON p1.`code`= p2.`code`AND p2.`date` = p1.maxdate
    -> ORDER BY p1.`code`;
+-----------+------------+---------+
| code      | maxdate    | price   |
+-----------+------------+---------+
| ADV000001 | 2021-11-23 |      20 |
| ADV000112 | 1993-06-11 |  685.04 |
| ADV000123 | 2017-04-21 |    31.4 |
| ADV000202 | 2011-11-11 |  511.99 |
| ADV000215 | 2011-12-11 |  856.81 |
| ADV000777 | 2012-04-11 | 1352.12 |
| ADV000912 | 2021-01-11 |   111.1 |
| ADV000991 | 2021-04-09 |  242.66 |
| ADV011912 | 1994-11-11 | 1324.11 |
| ADV031242 | 2019-07-11 |  231.42 |
+-----------+------------+---------+
10 rows in set (0.01 sec)

MariaDB [order]>

Michael · Answer 2 · 2021-11-24T03:24:19

做出你的选择查询的"顺"在下降。我说的是"选择"查询获取最新的日期在你的数据库。

vee · Answer 3 · 2021-11-24T03:39:18

如果你使用 where `date` = (SELECT MAX(`date`) FROM `prices`) 然后它将只选择一个新的(MAX)从表中要使用的日期的条件。结果显示出只有1个记录。

参照从这个问题

SELECT 
    `prices`.*
FROM 
    `prices`
INNER JOIN (
    SELECT MAX(`date`) AS `maxdate`, `price`, `code`
    FROM `prices`
    GROUP BY `code`
) AS `prices2` 
    ON `prices`.`date` = `prices2`.`maxdate`
ORDER BY `date` DESC

结果：

代码	价格	日期
ADV000001	20	2021-11-23
ADV000991	242.66	2021-04-09
ADV000912	111.1	2021-01-11
ADV031242	231.42	2019-07-11
ADV000123	31.4	2017-04-21
ADV000777	1352.12	2012-04-11
ADV000215	856.81	2011-12-11
ADV000202	511.99	2011-11-11
ADV011912	1324.11	1994-11-11
ADV000112	685.04	1993-06-11

获得最近记录的通过日期在mysql

的问题

最好的答案

其他语言

此页面有其他语言版本

受欢迎的此类别

流行的问题，在这个类别