Хорошо, позвольте мне повторить мое понимание вашего вопроса: вам нужна хранимая процедура, которая может принимать переменное количество параметров и возвращать верхнюю строку, которая соответствует параметрам в взвешенном порядке предпочтения, переданном в SQL Server 2005.
В идеале он будет использовать предложения WHERE для предотвращения полного сканирования таблиц, а также использовать преимущества индексов и будет "короткое замыкание" поиска - вы не хотите искать все возможные комбинации, если одна из них может быть найдена раньше. Возможно, мы также можем разрешить другие компараторы, кроме =, такие как >= для дат, LIKE для строк и т. д.
Один из возможных способов — передать параметры в виде XML, как в эта статья и использовать хранимые процедуры .Net, но давайте пока оставим простой ванильный T-SQL.
Мне это кажется бинарным поиском по параметрам: искать все параметры, затем отбрасывать последний, затем отбрасывать предпоследний, но включать последний и т. д.
Давайте передадим параметры в виде строки с разделителями, поскольку хранимые процедуры не позволяют передавать массивы в качестве параметров. Это позволит нам получить переменное количество параметров в нашей хранимой процедуре, не требуя хранимой процедуры для каждого варианта параметров.
Чтобы разрешить какое-либо сравнение, мы передадим весь список предложений WHERE, например так: заголовок типа '%something%'
Передача нескольких параметров означает их разграничение в строке. Мы будем использовать символ тильды ~ для разделения параметров, например: author = 'Chris Latta'~title like '%something%'~pages >= 100
Тогда это просто вопрос выполнения двоичного взвешенного поиска первой строки, которая соответствует нашему упорядоченному списку параметров (надеюсь, хранимая процедура с комментариями не требует пояснений, но если нет, дайте мне знать). Обратите внимание, что вам всегда гарантируется результат (при условии, что в вашей таблице есть хотя бы одна строка), поскольку последний поиск не имеет параметров.
Вот код хранимой процедуры:
CREATE PROCEDURE FirstMatch
@SearchParams VARCHAR(2000)
AS
BEGIN
DECLARE @SQLstmt NVARCHAR(2000)
DECLARE @WhereClause NVARCHAR(2000)
DECLARE @OrderByClause NVARCHAR(500)
DECLARE @NumParams INT
DECLARE @Pos INT
DECLARE @BinarySearch INT
DECLARE @Rows INT
-- Create a temporary table to store our parameters
CREATE TABLE #params
(
BitMask int, -- Uniquely identifying bit mask
FieldName VARCHAR(100), -- The field name for use in the ORDER BY clause
WhereClause VARCHAR(100) -- The bit to use in the WHERE clause
)
-- Temporary table identical to our result set (the books table) so intermediate results arent output
CREATE TABLE #junk
(
id INT,
author VARCHAR(50),
title VARCHAR(50),
printed DATETIME,
pages INT
)
-- Ill use tilde ~ as the delimiter that separates parameters
SET @SearchParams = LTRIM(RTRIM(@SearchParams))+ '~'
SET @Pos = CHARINDEX('~', @SearchParams, 1)
SET @NumParams = 0
-- Populate the #params table with the delimited parameters passed
IF REPLACE(@SearchParams, '~', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @NumParams = @NumParams + 1
SET @WhereClause = LTRIM(RTRIM(LEFT(@SearchParams, @Pos - 1)))
IF @WhereClause <> ''
BEGIN
-- This assumes your field names dont have spaces and that you leave a space between the field name and the comparator
INSERT INTO #params (BitMask, FieldName, WhereClause) VALUES (POWER(2, @NumParams - 1), LTRIM(RTRIM(LEFT(@WhereClause, CHARINDEX(' ', @WhereClause, 1) - 1))), @WhereClause)
END
SET @SearchParams = RIGHT(@SearchParams, LEN(@SearchParams) - @Pos)
SET @Pos = CHARINDEX('~', @SearchParams, 1)
END
END
-- Set the binary search to search from all parameters down to one in order of preference
SET @BinarySearch = POWER(2, @NumParams)
SET @Rows = 0
WHILE (@BinarySearch > 0) AND (@Rows = 0)
BEGIN
SET @BinarySearch = @BinarySearch - 1
SET @WhereClause = ' WHERE '
SET @OrderByClause = ' ORDER BY '
SELECT @OrderByClause = @OrderByClause + FieldName + ', ' FROM #params WHERE (@BinarySearch & BitMask) = BitMask ORDER BY BitMask
SET @OrderByClause = LEFT(@OrderByClause, LEN(@OrderByClause) - 1) -- Remove the trailing comma
SELECT @WhereClause = @WhereClause + WhereClause + ' AND ' FROM #params WHERE (@BinarySearch & BitMask) = BitMask ORDER BY BitMask
SET @WhereClause = LEFT(@WhereClause, LEN(@WhereClause) - 4) -- Remove the trailing AND
IF @BinarySearch = 0
BEGIN
-- If nothing found so far, return the top row in the order of the parameters fields
SET @WhereClause = ''
-- Use the full order sequence of fields to return the results
SET @OrderByClause = ' ORDER BY '
SELECT @OrderByClause = @OrderByClause + FieldName + ', ' FROM #params ORDER BY BitMask
SET @OrderByClause = LEFT(@OrderByClause, LEN(@OrderByClause) - 1) -- Remove the trailing comma
END
-- Find out if there are any results for this search
SET @SQLstmt = 'SELECT TOP 1 id, author, title, printed, pages INTO #junk FROM books' + @WhereClause + @OrderByClause
Exec (@SQLstmt)
SET @Rows = @@RowCount
END
-- Stop the result set being eaten by the junk table
SET @SQLstmt = REPLACE(@SQLstmt, 'INTO #junk ', '')
-- Uncomment the next line to see the SQL you are producing
--PRINT @SQLstmt
-- This gives the result set
Exec (@SQLstmt)
END
Эта хранимая процедура вызывается так:
FirstMatch 'author = ''Chris Latta''~pages > 100~title like ''%something%'''
Вот и все — полностью расширяемый, оптимизированный поиск лучших результатов в порядке взвешенного предпочтения. Это была интересная задача, и она показывает, чего можно добиться с помощью родного T-SQL.
Пара небольших проблем с этим:
- он полагается на то, что вызывающая сторона знает, что они должны оставить пробел после имени поля, чтобы параметр работал правильно.
- вы не можете иметь имена полей с пробелами в них - поправимо с некоторыми усилиями
- предполагается, что соответствующий порядок сортировки всегда восходящий
- следующий программист, которому придется взглянуть на эту процедуру, подумает, что вы сумасшедший :)
person
Chris Latta
schedule
23.12.2008
