那么让我们说：
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')
我需要结果是小于参考日期的前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.
解决方法
maintain the sequence of the items in the temporary table
,但是如果你想通过eserial获得结果,那么你必须在查询中添加ORDER BY eserial.如果没有ORDER BY,则可以按任何顺序返回结果行.这适用于您选择的任何方法.
因此,以您的上一个查询为基础,它将如下所示：
Select
lst.eserial,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
)
主键将创建唯一的聚簇索引.
一个更重要的说明.主TableSource表中的列ESERIAL和CREATEDDATETIME的类型和排序规则是什么？确保临时表中列的类型和排序规则与主TableSource表匹配.如果类型不同(varchar与nvarchar或datetime与日期)或排序规则不同,则可能不使用索引=>它会很慢.
编辑
您在问题中多次使用短语“与临时表相同的序列”,但它并不十分清楚您的意思.您的示例数据无助于解决歧义.列名称eserial也增加了混乱.我可以看到两种可能的含义：
>从eserial列中的值排序的临时表中返回行.
>以与插入时相同的顺序从临时表中返回行.
我的原始答案暗示(1)：它返回临时表中按eserial列中的值排序的行.
如果要在将行插入表中时保留行的顺序,则需要以某种方式明确记住此顺序.最简单的方法是将一个IDENTITY列添加到临时表中,然后按此列排序.像这样：
Create Table #mylist
(
ID int IDENTITY PRIMARY KEY,eserial nvarchar(35) Collate sql_Latin1_General_CP850_CI_AS,refdate datetime
)
在最后的查询中使用ORDER BY lst.ID.
