Заметки и портфолио: марта 2014

Рассмотрим типичную задачу построение выборки. Предположим имеется таблица "A" с некоторыми полями, пользователю необходимо дать возможность построения условия WHERE в запросе SELECT по определённым, заранее известным полям. Пусть эти поля называются N1, N2, Nk. Дальнейшее изложение идёт для T-SQL MS SQL Server. Создадим таблицу для записи пользовательского условия WHERE :

CREATE TABLE [RATIFICATION](
 [FNUM] [int] NOT NULL,
 [ANUM] [int] NOT NULL,
 [EXPR] [varchar](4)  NULL,
 [VAL] [varchar](255)  NULL
) 

CREATE INDEX inx_RATIFICATION_F ON RATIFICATION (FNUM)
GO

CREATE INDEX inx_RATIFICATION_A ON RATIFICATION (ANUM)
GO

Каждая строка в таблице представляет условие Ni = "VAL" или Ni <> "VAL" Поле FNUN содержит номер фильтра, поле EXPR содержит имя поля Ni для условия равенства, и строку "-Ni" для условия неравенства значению. Поле VAL содержит строковое значение с которым происходит сравнение. Поле ANUM номеруется по следующему принципу: выражения объединённые логическим "И" должны иметь в строках их представляющих в таблице одинаковое значение ANUM, логически "ИЛИ" разное. Например сформируем записи фильтра номер 1 для выражения N1 = "AA" AND N2 <> "BB" OR N3 = "CC":

FNUM	ANUM	EXPR	VAL
1	1	N1	AA
1	1	-N2	BB
1	2	N3	CC

Примем, что если значение поля EXPR есть строка "F" или "-F", то имеется ввиду номер другого фильтра, поле VAL в этом случае содержит номер фильтра. Легко видеть, что таким образом мы можем записать любое логическое условие для полей N1, N2, Nk включая выражения со скобками.
Построим функцию fn_CheckRatification (@FNUM INT, @N1 VARCHAR(255), @N2 VARCHAR(255), @Nk VARCHAR(255)), которая по номеру фильтра @FNUM и для значений @N1, @N2 и @Nk возвращает число большее нуля в случае если выражение принимает значение "истинно" и ноль в случае "ложь". Тогда условие WHERE для запроса SELECT может быть записана с использованием параметра @FNUM - номер фильтра:

SELECT ...
FROM A
WHERE dbo.fn_CheckRatification (@FNUM, A.N1, A.N2, A.Nk) > 0

Далее, приводится текст функции fn_CheckRatification. Основная идея такова, с помощью операторов CASE по каждой строке возвращается поле 1 (истина) или 0 (ложь) для проверки условия равенства (неравенства) значению VAL. Далее берется группировка по полю ANUM, а в качестве агрегатной функции берётся среднее AVG операторов CASE. Так получаем проверку условий "И". Сумма по этому агрегатному полю даст проверку условий "ИЛИ"

CREATE      FUNCTION [dbo].[fn_CheckRatification] 
(@FNUM INTEGER, 
@N1 VARCHAR(255), 
@N2 VARCHAR(255), 
@N3 VARCHAR(255) 
)
RETURNS INTEGER
AS
BEGIN
DECLARE @RES INTEGER


SET @RES = 
(
SELECT SUM(A.EX) FROM
(SELECT ANUM, 
AVG(
CASE EXPR
 WHEN 'N1' THEN CASE WHEN @N1 = VAL THEN 1 ELSE 0 END
 WHEN '-N1' THEN  CASE WHEN @N1 <> VAL THEN 1 ELSE 0 END

 WHEN 'N2' THEN CASE WHEN @N2 = VAL THEN 1 ELSE 0 END
 WHEN '-N2' THEN  CASE WHEN @N2 <> VAL THEN 1 ELSE 0 END

 WHEN 'Nk' THEN CASE WHEN @Nk = VAL THEN 1 ELSE 0 END
 WHEN '-Nk' THEN  CASE WHEN @Nk <> VAL THEN 1 ELSE 0 END

 WHEN 'F'  THEN CASE WHEN (dbo.fn_CheckRatification(VAL, @N1, @N2, @Nk) > 0) THEN 1 ELSE 0 END
 WHEN '-F'  THEN CASE WHEN (dbo.fn_CheckRatification(VAL, @N1, @N2, @Nk) >0) THEN 0 ELSE 1 END

END ) EX
FROM RATIFICATION (NOLOCK)
WHERE FNUM = @FNUM
GROUP BY ANUM) A
)

SET @RES = ISNULL(@RES, 0)
RETURN @RES
END
GO