如何通过

Question

当我按类别进行分组时，我如何获得最新的产品标题？

例如，假设这是我的产品表:

id | title | catID | timestamp
1  | apple | 1     | 2020-07-13 08:21:47
2  | pear  | 1     | 2020-07-14 08:21:47
3  | kiwi  | 1     | 2020-07-15 08:21:47

我想要一个查询，给我的总产品和最后添加。它有点贪心查询，使它变得更困难，但是我想我可以用一个group by来完成这个任务。如果可能的话，我真的更愿意将所有这些都保留在一个查询中。不过，我担心我需要添加一个子查询。

SELECT
    p.catID
    , COUNT(p.id) as items
    , MAX(p.id) as last_added_id
    , CASE WHEN p.id = MAX(p.id) THEN p.title
FROM products p
    WHERE p.catID = 1
GROUP BY p.catID

问题是，我的头衔是空的

Answer 1

使用窗口函数count()，max()和first_value():

select distinct
  p.catid,
  count(*) over (partition by p.catid) items,
  first_value(p.id) over (partition by p.catid order by p.timestamp desc) id, 
  first_value(p.title) over (partition by p.catid order by p.timestamp desc) title,
  max(p.timestamp) over (partition by p.catid) timestamp
from products p

请参阅演示。
结果:

> catid | items | id | title | timestamp          
> ----: | ----: | -: | :---- | :------------------
>     1 |     3 |  3 | kiwi  | 2020-07-15 08:21:47

Answer 2

我实际上想不出一种不使用子查询就能做到这一点的方法。假设您使用的是MySQL8+，我们可以在这里使用窗口函数:

SELECT id, title, catID, timestamp, cnt
FROM
(
    SELECT *, COUNT(*) OVER () AS cnt
    FROM products
    WHERE catID = 1
) t
ORDER BY timestamp DESC
LIMIT 1;