如何獲得最高（最大）成本、最低（最低）成本以及每個發生的日期？

我需要找到給定日期範圍內表格（有很多部分）中每個部分的最高成本和最低成本。並輸出記錄每個成本的日期。

範例數據：

CREATE TABLE v_po_history
(Part int,cost numeric(20,6),date_received date);

INSERT INTO v_po_history

VALUES
(846060,28.373,'1/5/2022'),
(846060,27.588,'3/8/2022'),
(846060,29.143,'4/25/2022'),
(846060,29.143,'2/28/2022'),
(70/1300/100,176.500,'1/7/2022'),
(70/1300/100,195.000,'3/19/2022'),
(80/800/75,77.846,'2/1/2022'),
(80/800/75,76.688,'4/19/2022'),
(80/800/75,76.602,'4/13/2022'),
(800372,0.9925,'1/1/2022'),
(800372,0.9925,'1/19/2022'),
(800372,0.9925,'4/1/2022'),
(800372,0.9925,'3/10/2022');

我需要我的輸出看起來像：

|  Part  |  Lowest Cost  |  Date  |  Highest Cost  |  Date  |
|--------|---------------|--------|----------------|--------|
| 846060 |    27.588     | 3/8/22 |     29.143     | 4/25/22|
|70/13...|    176.500    | 1/7/22 |     195.000    | 3/19/22|
|80/80...|    76.602     | 4/13/22|     77.846     | 2/1/22 |
| 800372 |    0.9925     | 1/1/22 |     0.9925     | 4/1/22 |

查詢我從其他論壇拼湊而成：

select distinct part, description, location, t_fd.First_Date, t_fd.lowest_cost, t_ld.Last_Date, t_ld.highest_cost from
v_po_history, 
--date for lowest cost
(select top 1 date_received as First_Date, min(cost) as lowest_cost from v_po_history where 
cost = (select min(cost) from v_po_history where part not like '*%' and date_received >= '2022-01-01' and date_received <= '2022-05-01' and location = 'HS')
and date_received >= '2022-01-01' and date_received <= '2022-05-01' 
group by date_received) as t_fd,
-- date for highest cost
(select top 1 date_received Last_Date, max(cost) as highest_cost from v_po_history where 
cost = (select max(cost) from v_po_history where part not like '*%' and date_received >= '2022-01-01' and date_received <= '2022-05-01' and location = 'HS')
and date_received >= '2022-01-01' and date_received <= '2022-05-01' 
group by date_received) as t_ld
where part not like '*%' and date_received >= '2022-01-01' and date_received <= '2022-05-01' 
and location = 'HS'

輸出：

|  Part  |  Lowest Cost  |  Date  |  Highest Cost  |  Date  |
|--------|---------------|--------|----------------|--------|
| 846060 |    0.9925     | 1/1/22 |     195.000    | 4/25/22|
|70/13...|    0.9925     | 1/1/22 |     195.000    | 4/25/22|
|80/80...|    0.9925     | 1/1/22 |     195.000    | 4/25/22|
| 800372 |    0.9925     | 1/1/22 |     195.000    | 4/25/22|

我明白為什麼我會得到這些結果。該查詢僅選擇最低成本和最高成本，但不是針對每個部分，僅選擇表中的最低和最高值。我的問題是如何調整它以獲得我想要的結果？還是我需要放棄這個查詢並以不同的方式處理這個問題？我正在使用 Pervasive SQL 順便說一句。這是一個小提琴

要獲得每個部分的結果，您顯然需要在某個地方按部分分組，而我在您的嘗試中看不到這一點。

這樣的事情可能對你有用：

select 
g.part, 
min_cost, 
(select max(p1.date_received) from part p1 
 where p1.part = g.part and p1.cost = g.min_cost) min_cost_date,
max_cost, 
(select max(p2.date_received) from part p2 
 where p2.part = g.part and p2.cost = g.max_cost) max_cost_date
from (select part, min(cost) min_cost, max(cost) max_cost
from part group by part) g

順便說一句，類似的東西70/1300/100看起來不像整數值。

小提琴。

引用自：https://dba.stackexchange.com/questions/312524