从同一select语句中使用N动态选择前N个

Question

我有一个程序尝试为每个特工在表格中插入样本，每个特工的样本数量因某些计算而异

Declare @samplesize int
Declare @Top int
set @samplesize=0

;WITH DataToInsert AS
(
    Select AgentID, Surveys, LOB,(case when day(getdate())<4 then 3 else (day(getdate())) - (Surveys*3) end) SampleSize from Current_Agent_SurveyCount_HSI Where surveys<8 
)

--Insert Into Survey_Source_New (LOB, CenterName, CallDate, AgentZid, TN, Ticket, RecordingID, Cycle, [TimeStamp])  
--Select top 5 ss.LOB, CenterName, CallDate, AgentZid, TN, Ticket, RecordingID, Cycle, [TimeStamp]  
--From Survey_source_Level1 ss
--inner join DataToInsert du on ss.AgentZID=du.agentID
--where flag is null and du.samplesize>6
--order by newid()

Insert Into Survey_Source_New (LOB, CenterName, CallDate, AgentZid, TN, Ticket, RecordingID, Cycle, [TimeStamp])  
Select top (@Top) @Top=du.samplesize,ss.LOB, CenterName, CallDate, AgentZid, TN, Ticket, RecordingID, Cycle, [TimeStamp]  
From Survey_source_Level1 ss
inner join DataToInsert du on ss.AgentZID=du.agentID
where flag is null and du.samplesize<7
order by newid()

我到这里的错误是

消息4115，级别15，状态1，第4
行在TOP子句的参数中不允许引用列“ samplesize”。此处仅允许引用外部作用域的列或独立的表达式和子查询。

有没有解决方法？

任何帮助表示赞赏。

提前致谢。

Answer 1

您可以row_number()用来做基本上相同的事情：

WITH DataToInsert AS
(
    Select AgentID, Surveys, LOB,(case when day(getdate())<4 then 3 else (day(getdate())) - (Surveys*3) end) SampleSize from Current_Agent_SurveyCount_HSI Where surveys<8 
)
Insert Into Survey_Source_New (LOB, CenterName, CallDate, AgentZid, TN, Ticket, RecordingID, Cycle, [TimeStamp])  
    select LOB, CenterName, CallDate, AgentZid, TN, Ticket, RecordingID, Cycle, [TimeStamp]
    from (Select ss.LOB, CenterName, CallDate, AgentZid, TN, Ticket, RecordingID, Cycle, [TimeStamp],
                 row_number() over (order by newid()) as seqnum
          From Survey_source_Level1 ss inner join
               DataToInsert du on ss.AgentZID=du.agentID
          where flag is null and du.samplesize<7
         ) t
    where seqnum <= du.sample_size

您也许可以简化一下，但是我不知道它是否flag来自du或ss。