主页 / user-252213

SkylarP's questions

Martin Hope
SkylarP
Asked: 2022-05-25 08:19:56 +0800 CST
  • 0

我需要找到给定日期范围内表格（有很多部分）中每个部分的最高成本和最低成本。并输出记录每个成本的日期。

示例数据：

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 顺便说一句。这是一个小提琴

query subquery