sql - 在不更改引用表中项目顺序的情况下提取数据

我想查找临时表中列出的值:

因此，让我们说:

Create Table #mylist
(
eserial nvarchar(35) Collate SQL_Latin1_General_CP850_CI_AS,
refdate datetime
)

Insert Into #mylist (eserial, refdate) Values ('A', '2015-09-15')
Insert Into #mylist (eserial, refdate) Values ('B', '2015-09-14')
Insert Into #mylist (eserial, refdate) Values ('C', '2015-09-13')
Insert Into #mylist (eserial, refdate) Values ('D', '2015-09-12')

我需要结果的Top 1日期小于引用日期。
并且应以与临时表相同的顺序返回。

我试过的

Select
    lst.eserial,
    lst.refdate,
    app.CREATEDDATETIME,
From #mylist lst
Outer Apply
    (Select Top 1 rec.CREATEDDATETIME, rec.ESERIAL, rec.ITEMID
     From TableSource rec
     Where lst.eserial=rec.ESERIAL And rec.CREATEDDATETIME<lst.refdate
     Order By rec.CREATEDDATETIME Desc
    ) As app

这行得通，但是很慢。另外，如果增加行数，则不会始终保持eserial的顺序。我需要查询以保留放入临时表中的顺序。

同样，我的预期输出是:



其中eserial是与临时表相同的序列，而CREATEDDATETIME是小于引用日期的最大日期。如果您了解Excel，则更像是有条件的Vlookup。

最佳答案

您的意思不是很清楚



，但如果要按eserial排序结果，则必须将ORDER BY eserial添加到查询中。如果没有ORDER BY，则可以按任何顺序返回结果行。这适用于您选择的任何方法。

因此，以您的上一个查询为基础，它将看起来像这样:

Select
    lst.eserial
    ,lst.refdate
    ,app.CREATEDDATETIME
From
    #mylist lst
    Outer Apply
    (
        Select Top 1 rec.CREATEDDATETIME
        From TableSource rec
        Where lst.eserial=rec.ESERIAL And rec.CREATEDDATETIME<lst.refdate
        Order By rec.CREATEDDATETIME Desc
    ) As app
ORDER BY lst.eserial;

为了使其快速有效地在TableSource上的(ESERIAL, CREATEDDATETIME)中添加索引。索引中列的顺序很重要。

了解OUTER APPLY查询中是否还有其他任何列以及如何使用它们也很重要。您在问题的第一个变量中提到了AREAID列，但在最后一个变量中没有提到。如果确实有更多的列，那么请清楚地表明您打算如何使用它们，因为正确的索引将取决于它。 (ESERIAL, CREATEDDATETIME)上的索引足以满足我在上面编写的查询，但是如果您有更多列，则可能需要其他索引。

如果您使用PRIMARY KEY定义了临时表，这也将对优化器有所帮助:

Create Table #mylist
(
    eserial nvarchar(35) Collate SQL_Latin1_General_CP850_CI_AS PRIMARY KEY,
    refdate datetime
)

主键将创建一个唯一的聚集索引。

还有一个重要的注意事项。主ESERIAL表中的CREATEDDATETIME和TableSource列的类型和排序规则是什么？确保临时表中列的类型和排序规则与主TableSource表匹配。如果类型不同(varchar vs. nvarchar或datetime vs. date)或排序规则不同，则可能无法使用索引=>它将很慢。

编辑

您在问题中多次使用了短语“与临时表相同的序列”，但是并不清楚它的含义。您的样本数据无助于解决歧义。列名eserial也增加了困惑。我可以看到两种可能的含义:

从temp表中返回按eserial列中的值排序的行。

以与插入时相同的顺序从临时表返回行。

我的原始答案暗示(1):它从temp表中返回按eserial列中的值排序的行。

如果要保留将行插入表中时的顺序，则需要以某种方式明确记住该顺序。最简单的方法是将IDENTITY列添加到temp表中，然后按此列排序。像这样:

Create Table #mylist
(
    ID int IDENTITY PRIMARY KEY,
    eserial nvarchar(35) Collate SQL_Latin1_General_CP850_CI_AS,
    refdate datetime
)

并在最终查询中使用ORDER BY lst.ID。