使用同一个 select 语句中的 N 动态选择前 N 个 [英] select top N dynamically with N from the same select statement
本文介绍了使用同一个 select 语句中的 N 动态选择前 N 个的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个程序试图为每个代理在表中插入样本,每个代理的样本数量根据某些计算而变化
I have a procedure that tries to insert samples in a table for each Agent, the # of samples for each agent varies based on some calculation
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".仅引用外部的列此处允许使用范围或独立表达式和子查询.
是否有解决方法?
感谢任何帮助.
提前致谢.
推荐答案
您可以使用 row_number()
来做本质上相同的事情:
You can use row_number()
to do essentially the same thing:
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.
You might be able to simplify this a bit, but I don't know if flag
is coming from du or ss.
这篇关于使用同一个 select 语句中的 N 动态选择前 N 个的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文