如何获得不同项目的最新计数(基于日期)?

Question

假设我有这样一个表:

<表类> 日期 itemID 数 tbody><<tr>2021-01-01122021-01-02132021-01-031空2021-01-01212021-01-022空2021-01-032空

您可以使用not exists。根据您的预期输出，我假设您想跳过null

select itemId, count
from t a
where count is not null and
not exists (select 1 
from t b 
where b.count is not null and b.itemId=a.itemId and a.date<b.date)

也可以用join

表示

select a.itemId, a.count
from t a
join (select itemId, max(date) as date 
from t where count is not null 
group by itemId) b on b.itemId=a.itemId and a.date=b.date

MySQl支持semi joins使用元组，所以你也可以做

select itemId, count
from t 
where (itemId,date) in (select itemId,max(date) 
from t where count is not null 
group by itemId)

以上的变化也可以使用correlated subquery

实现

select itemId, count
from t a
where date = (select max(date) 
from t where count is not null and itemId=a.itemId)

您可以使用ROW_NUMBER:

WITH cte AS (
SELECT t.*, ROW_NUMBER() OVER (PARTITION BY itemID ORDER BY date DESC) rn
FROM yourTable t
WHERE count IS NOT NULL
)
SELECT date, itemID, count
FROM cte
WHERE rn = 1;

您可以使用相关子查询:

select t.*
from t
where t.date = (select max(t2.date)
from t t2
where t2.itemId = t.itemId and t2.count is not null
);