English 中文(简体)
审视成果的第一行
原标题:Looking up the first row of results

我有一个历史表,每当一个记录发生变化时,都要作一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

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

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

问题回答

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

    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

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

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 




相关问题
How to write this T-SQL WHERE condition?

I ve got two tables: TableA Col1 Col2 TableB Col3 Col4 I want to join them together: SELECT * from TableA join TableB ON (...) Now, in place of ... I need to write an expression ...

Customer and Order Sql Statement

TSQL query to select all records from Customer that has an Order and also select all records from customer that does not have an Order. The table Customer contains a primary key of CustomerID. The ...

Recommended way of querying multiple Versioned tables

Have a win 2003 box with MSSQL 2005 running on it. There is a database which is populated every morning with new/modified SalesOrder made the previous day. The database has several tables: SalesOrder, ...

update duplicate record

I have a table with the following fields Id Name IsPublic i need to write a sql query that updates IsPublic to false where name has a duplicate. Only one of the duplicates should have IsPublic = ...

Define variable to use with IN operator (T-SQL)

I have a Transact-SQL query that uses the IN operator. Something like this: select * from myTable where myColumn in (1,2,3,4) Is there a way to define a variable to hold the entire list "(1,2,3,4)"? ...

Selecting records during recursive stored procedure

I ve got a content management system that contains a hierarchical structure of categories, with sub-categories subject to different ordering options at each level. Currently, that s retrieved by a (...

热门标签