有没有办法将值传递给派生表查询？
在派生表中，我想从外部查询中引用一个值 ([docSVsys].[sID])。
我收到一个错误:
是的，我知道这个查询可以简化为无循环。
有一个必须循环的游标并尝试将其转换为如此设置。
select top 10 [docSVsys].[sID], [sI].[count]
from docSVsys
join
(
select count(*) as [count]
from docSVenum1 as [sIt]
where [sIt].[sID] = [docSVsys].[sID]
) as [sI]
on '1' = '1'
order by [docSVsys].[sID]
交叉申请似乎可以解决问题。
它比游标版本快 1/3。
下面使用交叉应用的真实查询。
SELECT [sO].[sID], [sI].[max], [sI].[avg], [sI].[stdev]
FROM docSVsys as [sO] with (nolock)
cross apply
(
select [sO].[sID], max(list.match) as 'max', avg(list.match) as 'avg', stdev(list.match) as 'stdev'
from
(
select #SampleSet.[sID], [match] = 200 * count(*) / CAST ( #SampleSetSummary.[count] + [sO].[textUniqueWordCount] as numeric(8,0) )
from #SampleSet with (nolock)
join FTSindexWordOnce as [match] with (nolock) -- this is current @sID
on match.wordID = #SampleSet.wordID
and [match].[sID] = [sO].[sID]
join #SampleSetSummary with (nolock) -- to get the word count from the sample set
on #SampleSetSummary.[sID] = #SampleSet.[sID]
group by #SampleSet.[sID], #SampleSetSummary.[count]
) as list
having max(list.match) > 60
) as [sI]
where [textUniqueWordCount] is not null and [textUniqueWordCount] > 4 and [sO].[sID] <= 10686
order by [sO].[sID]
最佳答案
你可以用 CROSS APPLY 而不是 JOIN 做你想做的事:
select top 10 [docSVsys].[sID], [sI].[count]
from docSVsys
cross apply
(
select count(*) as [count]
from docSVenum1 as [sIt]
where [sIt].[sID] = [docSVsys].[sID]
) as [sI]
order by [docSVsys].[sID]
关于sql-server - 将值传递给派生表，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/11677527/