MySQL query |从组中选择最新行

Question

我有一个类似的表:

<表类> product_id client_id 类别 价格分配created_datetbody><<tr>113.12022-11-01213.22022-11-0231B3.32022-11-0341B3.42022-11-0452B3.52022-11-0562B3.62022-11-06723.72022-11-0782C3.82022-11-08

您可以这样做。使用MAX首先获取最新日期，并按类别和客户端ID分组。

查看这个db_fiddle

SELECT a.pid, b.cid, b.category, a.price, b.created_date FROM products a
JOIN 
(SELECT category, cid, MAX(created_date) as created_date FROM products GROUP BY cid, category) as b
ON a.category = b.category AND a.cid = b.cid AND a.created_date = b.created_date
ORDER BY pid

下次提问时请提供DDL + DML命令，这样别人就不用自己准备DDL和DML命令了。最好也分享一下你的尝试。

按照postgresql标准编写。它可能对你有帮助。

select 
distinct
first_value(pid) over(w) as pid,
first_value(cid) over(w),
first_value(category) over(w),
first_value(price) over(w),
first_value(created_date) over(w)
from products
window  w as (partition by cid,category order by created_date desc)

在Postgresql DB中测试。

如果你的MySQL版本是8.0或者更高，那么我们可以使用windows函数去做

create table test_data(
product_id int,
client_id int,
category varchar(10),   
price float,
created_date date
);
insert into test_data(product_id,client_id,category,price,created_date) values
(1,1,'A',3.1,'2022-11-01'),
(2,1,'A',3.2,'2022-11-02'),
(3,1,'B',3.3,'2022-11-03'),
(4,1,'B',3.4,'2022-11-04'),
(5,2,'B',3.5,'2022-11-05'),
(6,2,'B',3.6,'2022-11-06'),
(7,2,'A',3.7,'2022-11-07'),
(8,2,'C',3.8,'2022-11-08');

SELECT t.*
FROM 
(SELECT *,
row_number() over(PARTITION BY client_id,category order by created_date DESC)  as tn from test_data) as t
WHERE t.tn=1
order by t.product_id

DB Fiddle Demo