Question

我有一个历史表,每当一个记录发生变化时,都要作一shot。我试图以最初的被俘日期恢复某个历史。目前,我正在利用这一点:

select
    s.Description,
    h.CaptureDate OriginalCaptureDate
from
    HistoryStock s
    left join 
        ( select 
              StockId,
              CaptureDate
          from
              HistoryStock
          where
              HistoryStockId in ( select MIN(HistoryStockId) from HistoryStock group by StockId )
        ) h on s.StockId = h.StockId
where
    s.HistoryStockId = @HistoryStockId

这一工作虽然有100万人,但进展缓慢,我无法确定如何优化这一询问。

如何优化这一询问?

www.un.org/Depts/DGACM/index_spanish.htm

WITH OriginalStock (StockId, HistoryStockId)
AS  (
    SELECT  StockId, min(HistoryStockId)
    from HistoryStock group by StockId
    ),
OriginalCaptureDate (StockId, OriginalCaptureDate) 
As (
   SELECT h.StockId, h.CaptureDate
   from HistoryStock h join OriginalStock o on h.HistoryStockId = o.HistoryStockId
  )

select
    s.Description,
    h.OriginalCaptureDate
from
    HistoryStock s left join OriginalCaptureDate h on s.StockId = h.StockId
where
    s.HistoryStockId = @HistoryStockId

我即将更新该守则,以便使用幼儿教育,但我不太明智,业绩仅略有提高。任何想法?

仅是另一个说明,我需要查阅历史表中第一个记录,即储存,而不是最早的捕获日期。

Answer 1

我并不肯定,我完全理解数据如何从你的问答中起作用,但像我这样提出问题,对我的看法是永远不好的。你们可以按照以下方针尝试:

    WITH MinCaptureDate (StockID, MinCaptureDate)
AS  (
    SELECT   HS.StockID
        ,MIN(HS.CaptureDate) AS OriginalCaptureDate
    FROM    HistoryStock    HS
    GROUP BY 
         HS.Description
    )
SELECT   HS.Description
    ,MCD.OriginalCaptureDate
FROM    HistoryStock    HS
JOIN    MinCaptureDate  MCD
    ON HS.StockID = MCD.StockID
WHERE   HS.StockID = @StockID

Answer 2

我认为,看到你正在努力实现的目标。你们基本上想要描述具体的历史记录,但是,如果你们的历史表看上去,你想要的是与第一个历史记录有关的日期。

StockId   HistoryStockId   CaptureDate   Description
1         1                Apr 1         Desc 1
1         2                Apr 2         Desc 2
1         3                Apr 3         Desc 3

页: 1 页: 1

Description   OriginalCaptureDate
Desc 2        Apr 1

我认为,下面的询问将使你的工作稍有改善。

WITH OriginalStock (StockId, CaptureDate, RowNumber) 
AS  ( 
    SELECT  
        StockId, 
        CaptureDate,
        RowNumber = ROW_NUMBER() OVER (PARTITION BY StockId ORDER BY HistoryStockId ASC)
    from HistoryStock 
    )

select 
    s.Description, 
    h.CaptureDate 
from 
    HistoryStock s left join OriginalStock h on s.StockId = h.StockId and h.RowNumber = 1
where 
    s.HistoryStockId = @HistoryStockId

友情链接