限制从联合查询返回的行的最有效方法 - TSQL

问题描述

我有一个简单的存储过程，其中包含两个与联合连接的查询:

I have a simple stored proc with two queries joined with a union:

select name as 'result'
from product
where...

union

select productNum as 'result'
from product
where...

我想将此限制在前 10 个结果中.

I want to limit this to the TOP 10 results.

如果我将 TOP 10 放在每个单独的查询中，我总共会得到 20 个结果.

If I put TOP 10 in each seperate query I get 20 results total.

将总结果限制为 10 的最有效方法是什么?我不想在每个都做前 5 名，因为我可能最终会遇到类似 7 个名字"的情况.和 3 个产品编号".

What is the most efficient way to limit total results to 10? I dont want to do TOP 5 in each because I may end up in a situation where I have something like 7 "names" and 3 "productsNumbers".

推荐答案

WITH Results (Result)
AS
(

select name as 'result'
from product
where...

union

select productNum as 'result'
from product
where...
)

SELECT TOP 10 * FROM Results

常用表表达式