如何獲取最大創建日期的行數？
示例資料：
id|code|transaction_date |amount|record_status|creation_date
1 |0001|2021-12-10 00:00:00| 10.00|D |2021-12-10 00:00:00
2 |0001|2021-12-10 00:00:00| 10.00|D |2021-12-11 10:00:00
3 |0002|2021-12-11 00:00:00| 10.00|D |2021-12-11 00:00:00
4 |0002|2021-12-11 00:00:00| 10.00|D |2021-12-12 10:00:00
我想得到這個：
id|code|transaction_date |amount|record_status|creation_date
2 |0001|2021-12-10 00:00:00| 10.00|D |2021-12-11 10:00:00
4 |0002|2021-12-11 00:00:00| 10.00|D |2021-12-12 10:00:00
我正在嘗試這個
SELECT * FROM table1
WHERE (SELECT max(creation_date) from table1 WHERE DATE(transaction_date) = '2021-12-10')
AND record_status = 'D';
但我將無法選擇我想要的日期范圍 IN ('2021-12-10', 2021-12-11')
uj5u.com熱心網友回復：
嘗試：
CREATE TABLE table1 (
id int,
code int,
transaction_date datetime,
amount decimal(10,2),
record_status varchar(2),
creation_date datetime );
INSERT INTO table1 VALUES
(1,0001,'2021-12-10 00:00:00',10.00,'D','2021-12-10 00:00:00'),
(2,0001,'2021-12-10 00:00:00',10.00,'D','2021-12-11 10:00:00'),
(3,0002,'2021-12-11 00:00:00',10.00,'D','2021-12-11 00:00:00'),
(4,0002,'2021-12-11 00:00:00',10.00,'D','2021-12-12 10:00:00'),
(5,0003,'2021-12-10 00:00:00',10.00,'D','2021-12-11 10:00:00'),
(6,0003,'2021-12-10 00:00:00',10.00,'D','2021-12-16 10:00:00');
SELECT t1.*
FROM table1 t1
WHERE t1.creation_date = ( SELECT MAX(t2.creation_date)
FROM table1 t2
WHERE t2.code = t1.code
);
結果：
id code transaction_date amount record_status creation_date 2 1 2021-12-10 00:00:00 10.00 D 2021-12-11 10:00:00 4 2 2021-12-11 00:00:00 10.00 D 2021-12-12 10:00:00 6 3 2021-12-10 00:00:00 10.00 D 2021-12-16 10:00:00
演示：https : //dbfiddle.uk/?rdbms=mysql_8.0&fiddle=3b59835d81c67bc7b366ecce815ce67d
uj5u.com熱心網友回復：
對于使用范圍，您可以在兩者之間使用，例如：
SELECT * FROM table1
WHERE creation_date IN (
SELECT max(creation_date) from table1
WHERE record_status = 'D'
AND creation_date between '2021-12-11'
AND '2021-12-13'
group by DATE(transaction_date))
例子
uj5u.com熱心網友回復：
類似的東西怎么樣？
SELECT `table1`.*
FROM `table1`
INNER JOIN (
SELECT `code`, MAX(`creation_date`) AS `max_date`
FROM `table1`
WHERE `record_status` = 'D'
GROUP BY `code`
) `latest`
ON `table1`.`code` = `latest`.`code`
AND `table1`.`creation_date` = `latest`.`max_date`
這使用派生表來獲取每個代碼的最大日期。
uj5u.com熱心網友回復：
SELECT *
FROM table t1
WHERE NOT EXISTS ( SELECT NULL
FROM table t2
WHERE t1.code = t2.code
AND t1.creation_date < t2.creation_date
/* AND t2.record_status = 'D'
AND t2.DATE(transaction_date) = '2021-12-10' */ )
/* AND record_status = 'D'
AND DATE(transaction_date) = '2021-12-10' */ ;
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/383441.html