Как выбрать верхний n из объединения двух запросов, где полученный заказ должен быть ранжирован по отдельному запросу?

Скажем, у меня есть таблица с именами пользователей:

Id  |  Name
-----------
1   |  Bobby
20  |  Bob
90  |  Bob
100 |  Joe-Bob
630 |  Bobberino
820 |  Bob Junior

Я хочу вернуть список совпадений n по имени для "Боб", где в результате набора сначала содержатся точные соответствия, за которыми следуют аналогичные совпадения.

Я думал, что что-то вроде этого может работать

SELECT TOP 4 a.* FROM
(
    SELECT * from Usernames WHERE Name = 'Bob'
    UNION
    SELECT * from Usernames WHERE Name LIKE '%Bob%'
) AS a

но есть две проблемы:

Это неэффективный запрос, поскольку sub- select может возвращать много строк (при просмотре плана выполнения показано, что соединение происходит до вершины)
(Почти), что более важно, точное совпадение не будет отображаться первым в результатах, так как результирующий набор выглядит упорядоченным по первичному ключу.

Я ищу запрос, который вернется (для TOP 4)

Id | Name
---------
20 | Bob
90 | Bob

(and then 2 results from the LIKE query, e.g. 1 Bobby and 100 Joe-Bob)

Возможно ли это в одном запросе?

Ответ 1

Вы можете использовать case для размещения точных совпадений сверху:

select  top 4 *
from    Usernames
where   Name like '%Bob%'
order by
        case when Name = 'Bob' then 1 else 2 end

Или, если вы беспокоитесь об эффективности и указали индекс на (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

Ответ 2

Небольшая модификация исходного запроса должна решить эту проблему. Вы можете добавить дополнительный UNION, который соответствует WHERE Name LIKE 'Bob%' и присвоить этот приоритет 2, изменив приоритет '%Bob' на 3, и вы получите еще лучший поиск IMHO.

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

Ответ 3

Это может сделать то, что вы хотите, с лучшей производительностью.

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

Ответ 4

Это работает для меня:

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

Ответ 5

SET ROWCOUNT 4

SELECT * from Usernames WHERE Name = 'Bob'
UNION
SELECT * from Usernames WHERE Name LIKE '%Bob%'

SET ROWCOUNt 0

Ответ 6

Ответ от Will A заставил меня по очереди, но я хотел бы добавить короткую заметку, что если вы пытаетесь сделать то же самое и включить "FOR XML PATH", вам нужно написать немного по-разному.

Я указывал атрибуты XML и имел такие вещи, как:

SELECT Field_1 as [@attr_1]

Что вам нужно сделать, это удалить символ "@" в подзапросах, а затем добавить их обратно с внешним запросом. Вот так:

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')

Это cut- нижняя версия моего последнего запроса, поэтому на самом деле это не имеет смысла, но вы должны получить эту идею.