时间戳

首页 > 解决方案 > 使用 Group By 和 Order by Date 选择

mysql - 使用 Group By 和 Order by Date 选择

问题描述

我的桌子orders：

ID (int pkey) | store_id (int fkey) | order_category (int fkey) | order_price (deci 10,2) | date (date)

1 | 123 | 5 | 10.25 | 2-11-2021
2 | 678 | 2 | 10.25 | 2-10-2021
3 | 123 | 5 | 10.25 | 2-08-2021
4 | 123 | 3 | 10.25 | 2-04-2021
5 | 678 | 2 | 10.25 | 2-01-2021
6 | 123 | 1 | 10.25 | 2-01-2021
...

我正在尝试获取order_price每个order_category.

SELECT order_price FROM orders
WHERE store_id = 123
GROUP BY order_category
ORDER BY date

但它似乎在应用ORDER BY date时不起作用GROUP BY。我想要的结果是：

1 | 123 | 5 | 10.25 | 2-11-2021
4 | 123 | 3 | 10.25 | 2-04-2021
6 | 123 | 1 | 10.25 | 2-01-2021

使用 MySQL8.0.23

标签： mysqlsql

解决方案

您可以尝试使用按类别分组的最大日期的子查询

select o.order_price, t.order_category
from orders 
INNER JOIN (
    SELECT order_category, max(date) max_date
     order_price 
    FROM orders
    WHERE store_id = 123
    GROUP BY order_category
) t on t.order_category = o.order_category 
    AND t.max_date = o.date
ORDER BY date

推荐阅读