所以我有這張桌子：
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、NAME、id_product 的數量總和，對于 PRICE 列，我想獲得來自最舊產品的價格值（我希望它考慮CREATED_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
uj5u.com熱心網友回復：
您需要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
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/454827.html
標籤：sql PostgreSQL tsql 通过...分组
上一篇：T-SQL中的復雜正則運算式