如何根据最古老的日期分组和返回值？

Question

因此，我有一个表：

  n    |   id_product     |   name    |  price  |   quantity   |   created_at
 -------------------------------------------------------------------------------
  1         1da05150         pencil      800          10          31-12-2021
  2         1da05150         pencil      700          7           15-01-2022
  3         1da05150         pencil      1500         13          20-01-2022
  4         510eac00         book        350          25          29-12-2021

我想按ID_Product列对它们进行分组。 在我想回来的列中，将是ID_Product，名称，ID_Product的数量总和，对于我想获得的价格列，我想获得来自最古老的产品的价格值（我希望它考虑考虑到为价格目的

而创建的_at列应该是这样的：

  n    |   id_product     |   name    |  price  |   quantity   |   created_at
 -------------------------------------------------------------------------------
  1         1da05150         pencil      800          30          31-12-2021
  2         510eac00         book        350          25          29-12-2021

Answer 1

您需要subquery与自己加入表
您的数据

create temporary table tmp_a (
   n          int
  ,id_product VARCHAR(30)
  ,name       VARCHAR(30)
  ,price      int
  ,quantity   int
  ,created_at date
);

INSERT INTO tmp_a
(n,id_product,name,price,quantity,created_at)
VALUES (1,'1da05150','pencil',800,10,'12-31-2021'),
(2,'1da05150','pencil',700,7,'01-15-2022'),
(3,'1da05150','pencil',1500,13,'01-20-2022'),
(4,'510eac00','book',350,25,'12-29-2021');

查询

SELECT t1.n,
       t1.id_product,
       t1.NAME,
       t1.price,
       t2.quantity,
       t2.created_at
FROM   tmp_a t1
       JOIN (SELECT id_product,
                    NAME,
                    Sum(quantity)   quantity,
                    Min(created_at) created_at
             FROM   tmp_a
             GROUP  BY id_product,
                       NAME) t2
         ON t1.id_product = t2.id_product
            AND t1.created_at = t2.created_at