Herman<T>.Instance Ответов: 1

Sql запрос поиск значений которые могут быть в любом порядке

Мои друзья,

на SQL Server 2014 у меня есть хранимая процедура поиска. В текущем случае он проверяет наличие %searchterm% по нескольким полям. Если он совпадает, то возвращает данные.
В большинстве случаев это нормально, но что такое тот, кто ищет "б а" вместо "А Б"? Пользователь не увидит никакого результата, пока они есть. Как я могу выполнить поиск с неизвестным порядком клавиш поиска?

Я создал функцию SplitString (в sql server 2016 она есть, но в 2014 году не работает), чтобы иметь строки, которые содержат один поисковый запрос на строку.
Как я могу использовать эти строки в like, чтобы я мог возвращать результаты, основанные на случайном порядке заданных поисковых запросов пользователем

Что я уже пробовал:

DECLARE @searchtext NVARCHAR(1000)= 'vessel ship container';
SELECT pv.[Id], 
       [ProjectNo], 
       [MainProjectNo], 
       [ProjectDescription], 
       [CustomerNo], 
       [CustomerName], 
       [Status],
       CASE
           WHEN [ProjectTypeCode] = 'NULL'
           THEN NULL
           ELSE [ProjectTypeCode]
       END AS [ProjectTypeCode], 
       [ManagerName],
       CASE
           WHEN [EngineerName] = 'NULL'
           THEN NULL
           ELSE [EngineerName]
       END AS [EngineerName],
       
FROM [dbo].[IProjectView] AS pv
     CROSS APPLY dbo.SplitString(@searchtext, ' ') as res
WHERE 1 = 1
      AND ProjectNo LIKE '%' + @searchtext + '%'
      OR MainProjectNo LIKE '%' + @searchtext + '%'
      OR CustomerNo LIKE '%' + @searchtext + '%'
      OR CustomerName LIKE '%' + @searchtext + '%'
      OR [Status] LIKE '%' + @searchtext + '%'
      OR ProjectTypeCode LIKE '%' + @searchtext + '%'
      OR ManagerName in (select [Value] from dbo.SplitString(@searchtext, ' ')) -- LIKE '%' + @searchtext + '%'
      OR EngineerName LIKE '%' + @searchtext + '%'
      OR ProjectDescription LIKE '%' + @searchtext + '%'
ORDER BY ProjectNo;


Функция SplitString возвращает:
%vessel%
%ship%
%container%

W∴ Balboos, GHB

Результаты splitstring, в серии как .... И ВРОДЕ ТОГО ... похоже, они сделают свое дело. И сила всех спичек, но в любом месте строки будет делать это для каждого.

Вам нужно построить запрос динамически, основываясь на количестве элементов в ваших результатах разделения строк (или должно быть, это " делает лучшее упражнение)

Herman<T>.Instance

динамически, вы имеете в виду CTE?

W∴ Balboos, GHB

Нет - я очень просто об этом говорю. Вы можете построить строку запроса в цикле, чтобы она обрабатывала каждый из N компонентов.

Другой способ: создайте таблицу #temp с результатами совпадения подстроки on. Затем удаляются из этой таблицы все, что не содержит других подстрок.

Это действительно тот же самый чистый результат, что и куча подобных предложений and'Ed.

Ничего таинственного - прямолинейно и прямолинейно. Если бы я делал это, я бы строил свою строку на php (или C или C++) и отправлял ее на уже построенный сервер.

1 Ответов

Рейтинг:
12

CHill60

Вместо того чтобы использовать LIKE, не могли бы вы использовать CHARINDEX - что-то вроде 

declare @splitresults table (dataitem varchar(max))
insert into @splitresults values
('vessel'), ('ship'), ('container') 
-- Code above just spoofing the results of a split function.

declare @project table(id int identity(1,1), textitem varchar(max))
insert into @project values
('this vessel should be found'),
('this ship is a vessel'),
('this container is a box'),
('should not find me')
-- The table above is just some dummy data to search

SELECT *
FROM @project A
INNER JOIN @splitresults B ON CHARINDEX(B.dataitem, A.textitem) > 0
Это дало следующие результаты
id	textitem						dataitem
1	this vessel should be found		vessel
2	this ship is a vessel			vessel
2	this ship is a vessel			ship
3	this container is a box			container
Вам нужно будет присоединиться ко всем соответствующим столбцам (или использовать союзы) Думаю, это будет не особенно эффектно.

Для SQL 2014 смотрите этот поток для функции разделения Как разделить строки в sql server[^]

EDIT: я только что заметил еще одну проблему. 
WHERE 1 = 1
      AND ProjectNo LIKE '%' + @searchtext + '%'
      OR MainProjectNo LIKE '%' + @searchtext + '%'
      OR CustomerNo LIKE '%' + @searchtext + '%'
Вы смешиваете и и или пробуете 
WHERE 1 = 1
      AND 
      (
           ProjectNo LIKE '%' + @searchtext + '%'
           OR MainProjectNo LIKE '%' + @searchtext + '%'
           OR CustomerNo LIKE '%' + @searchtext + '%'
           . . .
       )


EDIT 2 - это то, что я имел в виду под опциями для проверки других столбцов. 2-й подход имеет преимущество в удалении дубликатов результатов
--Approach 1 - multiple conditions in ON clause
SELECT A.*
FROM @project A
INNER JOIN @splitresults B ON CHARINDEX(B.dataitem, A.textitem1) > 0 OR CHARINDEX(B.dataitem, A.textitem2) > 0

-- Approach 2 UNION multiple queries - one query per column to check
SELECT A.*
FROM @project A
INNER JOIN @splitresults B ON CHARINDEX(B.dataitem, A.textitem1) > 0 
UNION
SELECT A.*
FROM @project A
INNER JOIN @splitresults B ON CHARINDEX(B.dataitem, A.textitem2) > 0


Редактировать после ОП комментарии
Я неправильно понял это требование - все элементы поиска должны отображаться в строке, чтобы он был включен.

Я изменил свои тестовые данные, чтобы иметь дополнительные столбцы:
declare @project table(id int identity(1,1), textitem varchar(max), textitem2 varchar(max), textitem3 varchar(max))
insert into @project values
('this vessel should be found','ship','container'),
('this ship is a vessel','1','ship'),
('this container is a box','vessel','2'),
('should not find me at all','1','2')
Я также добавил подсчет количества поисковых элементов, которые у нас есть 
declare @searchitems int = (SELECT COUNT(*) FROM @splitresults)
Я опустил список потенциальных кандидатов во временную таблицу 
SELECT DISTINCT  A.id, A.textitem + A.textitem2 + A.textitem3 as searchstring, B.dataitem
INTO #res1
FROM @project A
INNER JOIN @splitresults B ON CHARINDEX(B.dataitem, A.textitem) > 0 OR CHARINDEX(B.dataitem, A.textitem2) > 0 OR CHARINDEX(B.dataitem, A.textitem3) > 0
Ключевая предпосылка заключается в том, что любой столбец, который можно найти, образует столбец [searchstring] в фиксированном порядке, и каждый из этих столбцов также должен быть включен в предложение ON.

Эта временная таблица теперь содержит от 0 до n строк для каждого из проектов, где n будет числом элементов поиска. Поэтому просто извлеките строки из @project для любого идентификатора, который появляется в #res n раз..
select * from @project WHERE id IN
(
	select id
	FROM #res1 
	group by id
	having COUNT(*) = @searchitems
)
Не самое приятное, но вроде бы работает - что там говорят о грубой силе и невежестве :-)
Я также попробовал рекурсивный CTE, но просто не мог справиться с этим.

Herman<T>.Instance

Привет CHill60,
Я принял ваше решение, потому что это работает очень хорошо.... Если у меня есть n ключевых слов и все ключевые слова должны существовать не какие-то, а все. Он делает или сейчас вместо и.

Мой код:
DECLARE @searchtext NVARCHAR(1000)= 'судно-контейнер судна';

;С containsfields as (SELECT [Value] from dbo.SplitString(@searchtext, ' '))
Выбор различных ПВ.[Код],
[Проектно],
[MainProjectNo],
[Описание проекта],
[CustomerNo],
[Имя пользователя],
[Статус],
ДЕЛО
Когда [ProjectTypeCode] = 'NULL'
ТОГДА НОЛЬ
ELSE [ProjectTypeCode]
END AS [ProjectTypeCode],
[Имя менеджера],
ДЕЛО
Когда [EngineerName] = 'NULL'
ТОГДА НОЛЬ
ELSE [EngineerName]
Конец как [Имя_инженера],
От [dbo].[IProjectView] как pv
Внутреннее соединение содержит поля в виде cf
на CHARINDEX(cf.Value, pv.SearchLine) > 0
Заказ по ProjectNo;


Строка поиска - это объединение полей в представлении.
Как изменить запрос, чтобы все ключевые слова были найдены в 1 строке?

CHill60

В моем "подходе 1" с несколькими условиями ON вы можете изменить ОТ или до и т. е. 

SELECT A.*
FROM @project A
INNER JOIN @splitresults B ON CHARINDEX(B.dataitem, A.textitem1) > 0 AND CHARINDEX(B.dataitem, A.textitem2) > 0
Просто повторите этот шаблон для всех столбцов, которые вы хотите проверить

Herman<T>.Instance

У меня может быть n splitresults, но все они должны происходить в пределах pv.SearchLine

CHill60

А! Я смотрел на это с другой стороны. Я еще раз подумаю об этом

CHill60

Я обновил свое решение. Это не элегантно, но это сработало на моих фиктивных данных. Можете ли вы дать ему попробовать?

Herman<T>.Instance

Привет CHill60,
Я только что проверил его, и он дает мне ровно 0 строк в результате, если слова находятся в правильном порядке или в другом порядке. Спасибо за вашу помощь, несмотря на результат. Я пытаюсь найти другие пути