SQL Server 我可以从我创建的联合表中获取产品成本更改的最后日期和相关成本吗？

bhmjp9jg 于 2023-02-11 发布在其他

关注(0)|答案(1)|浏览(104)

我编写了一个查询，从我们公司的SQL Server数据库的表中提取产品的最新价格以及该价格，并使用UNION将其与这些相同产品的最新交货价格连接起来。现在是否有办法将该联合查询嵌套到另一个联合查询中，以便从联合表中查找最新价格？
这是工会查询的代码：

select  LastCostDate,
        [LocStock].SiteID,      
        [LastDate].PLUID,
        [LastDate].Description,
        [LastCost].Cost
From    (select max([PLUCostHistory].ChangeDate) as LastCostDate, [PLUCostHistory].PLUID, [PLU].Description From [PLUCostHistory] Inner Join [PLU]
        on [PLU].PLUID=[PLUCostHistory].PLUID group by [PLUCostHistory].PLUID, [PLU].Description ) as LastDate

Inner Join (select [PLUCostHistory].ChangeDate, [PLUCostHistory].PLUID, [PLU].Description, sum([PLUCostHistory].Cost) as Cost From [PLUCostHistory] Inner Join [PLU]
        on [PLU].PLUID=[PLUCostHistory].PLUID group by [PLUCostHistory].ChangeDate, [PLUCostHistory].PLUID, [PLU].Description ) as LastCost
On      LastDate.Description=LastCost.Description
And     LastDate.LastCostDate=LastCost.ChangeDate
And     LastDate.PLUID=LastCost.PLUID
Inner Join [LocStock]
        on [LocStock].PLUID=[LastDate].PLUID

UNION

select  LastCostDate,
        [DelMast].SiteID,       
        [LastDate].PLUID,
        [LastDate].Description,
        [LastCost].Cost
From    (select max([DelMast].DeliveryDate) as LastCostDate, [DelDets].PLUID, [PLU].Description From [DelMast] Inner Join [DelDets]
        on [DelDets].DeliveryID=[DelMast].DeliveryID Inner Join [PLU] on [PLU].PLUID=[DelDets].PLUID Inner Join [PLUGroupRef]
        on [DelDets].PLUID=[PLUGroupRef].PLUID Inner Join [PLUGroup2]
        on [PLUGroup2].PLUGroup2ID=[PLUGroupRef].PLUGroup2ID group by [DelDets].PLUID, [PLU].Description) as LastDate

Inner Join (select [DelMast].DeliveryDate, [DelDets].PLUID, [PLU].Description, sum([DelDets].Cost) as Cost From [DelMast] Inner Join [DelDets] on [DelDets].DeliveryID=[DelMast].DeliveryID Inner Join [PLU]
        on [PLU].PLUID=[DelDets].PLUID group by [DelMast].DeliveryDate, [DelDets].PLUID, [PLU].Description) as LastCost
On      LastDate.Description=LastCost.Description
And     LastDate.LastCostDate=LastCost.DeliveryDate
And     LastDate.PLUID=LastCost.PLUID
Inner Join [LocStock]
        on [LocStock].PLUID=[LastDate].PLUID
Inner Join [DelMast] 
        on [LocStock].SiteID=[DelMast].SiteID

表一
| 变更日期|产品|费用|
| - ------|- ------|- ------|
| 二零零零年一月一日|水|五个|
| 二○ ○ ○年一月六日|香蕉|第二章|
| 二○ ○ ○年一月九日|橙|三个|
| 二○ ○ ○年一月十日|水|三个|
| 二零零零年一月一日|香蕉|二、五|
表二
| 变更日期|产品|费用|
| - ------|- ------|- ------|
| 二○ ○ ○年一月八日|水|六个|
| 二○ ○ ○年一月三日|香蕉|1个|
| 二○ ○ ○年一月五日|水|三个|
| 二○ ○ ○年二月一日|香蕉|三个|
| 二○ ○ ○年一月十二日|橙|四个|
预期输出：
| 变更日期|产品|费用|
| - ------|- ------|- ------|
| 二○ ○ ○年一月十日|水|三个|
| 二○ ○ ○年一月六日|香蕉|第二章|
| 二○ ○ ○年一月十二日|橙|四个|

sql-server

来源：https://stackoverflow.com/questions/75325576/can-i-get-the-last-date-a-product-had-its-cost-changed-and-that-relevant-cost-fr

1条答案

按热度按时间

fdbelqdn1#

别管我上面的评论，我想出了下面的SQL小把戏，但是用数据的静态副本替换了长联合子查询：http://sqlfiddle.com/#!18/7907d4/1

CREATE TABLE T_Items (
  ChangeDate date          NOT NULL,
  Product    nvarchar(200) NOT NULL,
  Cost       money         NOT NULL
)

INSERT INTO T_Items SELECT '01/01/2000', 'Water', 5;
INSERT INTO T_Items SELECT '06/01/2000', 'Banana', 2;
INSERT INTO T_Items SELECT '09/01/2000', 'orange', 2;
INSERT INTO T_Items SELECT '10/01/2000', 'Water', 3;
INSERT INTO T_Items SELECT '01/01/2000', 'Banana', 2.5;
INSERT INTO T_Items SELECT '08/01/2000', 'Water', 6;
INSERT INTO T_Items SELECT '03/01/2000', 'Banana', 1;
INSERT INTO T_Items SELECT '05/01/2000', 'Water', 3;
INSERT INTO T_Items SELECT '02/01/2000', 'Banana', 3;
INSERT INTO T_Items SELECT '12/01/2000', 'orange', 4;

SELECT TOP 1 WITH TIES
   ChangeDate,
   Product,
   Cost
FROM
  T_Items
ORDER BY ROW_NUMBER() OVER (PARTITION BY Product ORDER BY ChangeDate DESC);

您可以用联合子查询替换部分T_Items，使其如下所示：

SELECT TOP 1 WITH TIES
   ChangeDate,
   Product,
   Cost
FROM
  (select  LastCostDate,
    [LocStock].SiteID,      
    [LastDate].PLUID,[...]) AS d1
ORDER BY ROW_NUMBER() OVER (PARTITION BY Product ORDER BY ChangeDate DESC)

不要忘记AS d1部分，在使用子查询时它是强制的。
如果我没看错的话，您提供的表与发布的查询不匹配，因为列名不同（LastCostDate与ChangeDate）。看起来更像是发布了INNER JOIN查询的结果：

select [PLUCostHistory].ChangeDate, [PLUCostHistory].PLUID, [PLU].Description, sum([PLUCostHistory].Cost) as Cost From [PLUCostHistory] Inner Join [PLU]
        on [PLU].PLUID=[PLUCostHistory].PLUID group by [PLUCostHistory].ChangeDate, [PLUCostHistory].PLUID, [PLU].Description ) as LastCost

因此，如果这不是您想要的，请调整您的问题。

赞(0）回复(0）举报 2023-02-11

我来回答

SQL Server 我可以从我创建的联合表中获取产品成本更改的最后日期和相关成本吗？

1条答案

相关问题

热门标签

最新问答