SQL 如何从两个查询的联合中选择前 n 个查询，其中结果顺序需要按单个查询进行排名？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6131323/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to select the top n from a union of two queries where the resulting order needs to be ranked by individual query?
提问by Jedidja
Let's say I have a table with usernames:
假设我有一个带有用户名的表：
Id | Name
-----------
1 | Bobby
20 | Bob
90 | Bob
100 | Joe-Bob
630 | Bobberino
820 | Bob Junior
I want to return a list of nmatches on name for 'Bob' where the resulting set first contains exact matches followed by similar matches.
我想返回n“Bob”的名称匹配列表，其中结果集首先包含完全匹配，然后是类似匹配。
I thought something like this might work
我认为这样的事情可能会奏效
SELECT TOP 4 a.* FROM
(
SELECT * from Usernames WHERE Name = 'Bob'
UNION
SELECT * from Usernames WHERE Name LIKE '%Bob%'
) AS a
but there are two problems:
但是有两个问题：
- It's an inefficient query since the sub-select could return many rows (looking at the execution plan shows a join happening before top)
- (Almost) more importantly, the exact match(es) will not appear first in the results since the resulting set appears to be ordered by primary key.
- 这是一个低效的查询，因为子选择可能返回许多行（查看执行计划显示在 top 之前发生了连接）
- （几乎）更重要的是，精确匹配项不会首先出现在结果中，因为结果集似乎是按主键排序的。
I am looking for a query that will return (for TOP 4)
我正在寻找将返回的查询（对于 TOP 4）
Id | Name
---------
20 | Bob
90 | Bob
(and then 2 results from the LIKE query, e.g. 1 Bobby and 100 Joe-Bob)
Is this possible in a single query?
这可以在单个查询中实现吗？
回答by Andomar
You could use a caseto place the exact matches on top:
您可以使用 acase将精确匹配放在顶部：
select top 4 *
from Usernames
where Name like '%Bob%'
order by
case when Name = 'Bob' then 1 else 2 end
Or, if you're worried about performance and have an index on (Name):
或者，如果您担心性能并在 上有索引(Name)：
select top 4 *
from (
select 1 as SortOrder
, *
from Usernames
where Name = 'Bob'
union all
select 2
, *
from Usernames
where Name like '%Bob%'
and Name <> 'Bob'
and 4 >
(
select count(*)
from Usernames
where Name = 'Bob'
)
) as SubqueryAlias
order by
SortOrder
回答by Will A
A slight modification to your original query should solve this. You could add in an additional UNION that matches WHERE Name LIKE 'Bob%'and give this priority 2, changing the '%Bob'priority to 3 and you'd get an even better search IMHO.
对原始查询稍作修改即可解决此问题。您可以添加一个匹配的附加 UNIONWHERE Name LIKE 'Bob%'并将此优先级设置为 2，将'%Bob'优先级更改为 3，您将获得更好的搜索恕我直言。
SELECT TOP 4 a.* FROM
(
SELECT *, 1 AS Priority from Usernames WHERE Name = 'Bob'
UNION
SELECT *, 2 from Usernames WHERE Name LIKE '%Bob%'
) AS a
ORDER BY Priority ASC
回答by Daniel Williams
This works for me:
这对我有用：
SELECT TOP 4 * FROM (
SELECT 1 as Rank , I, name FROM Foo WHERE Name = 'Bob'
UNION ALL
SELECT 2 as Rank,i,name FROM Foo WHERE Name LIKE '%Bob%'
) as Q1
ORDER BY Q1.Rank, Q1.I
回答by Viktor Stolbovoy
SET ROWCOUNT 4
SELECT * from Usernames WHERE Name = 'Bob'
UNION
SELECT * from Usernames WHERE Name LIKE '%Bob%'
SET ROWCOUNt 0
回答by Mikael Eriksson
This might do what you want with better performance.
这可能会以更好的性能满足您的需求。
SELECT TOP 4 a.* FROM
(
SELECT TOP 4 *, 1 AS Sort from Usernames WHERE Name = 'Bob'
UNION ALL
SELECT TOP 4 *, 2 AS Sort from Usernames WHERE Name LIKE '%Bob%' and Name <> 'Bob'
) AS a
ORDER BY Sort
回答by Ads
The answer from Will A got me over the line, but I'd like to add a quick note, that if you're trying to do the same thing and incorporate "FOR XML PATH", you need to write it slightly differently.
Will A 的回答让我明白了，但我想补充一点，如果您尝试做同样的事情并合并“FOR XML PATH”，则需要稍微不同地编写它。
I was specifying XML attributes and so had things like :
我正在指定 XML 属性，因此有以下内容：
SELECT Field_1 as [@attr_1]
What you have to do is remove the "@" symbol in the sub queries and then add them back in with the outer query. Like this:
您需要做的是删除子查询中的“@”符号，然后将它们添加回外部查询中。像这样：
SELECT top 1 a.SupervisorName as [@SupervisorName]
FROM
(
SELECT (FirstNames + ' ' + LastName) AS [SupervisorName],1 as OrderingVal
FROM ExamSupervisor SupervisorTable1
UNION ALL
SELECT (FirstNames + ' ' + LastName) AS [SupervisorName],2 as OrderingVal
FROM ExamSupervisor SupervisorTable2
) as a
ORDER BY a.OrderingVal ASC
FOR XML PATH('Supervisor')
This is a cut-down version of my final query, so it doesn't really make sense, but you should get the idea.
这是我最终查询的精简版，因此它没有任何意义，但您应该明白这一点。
