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

Рассмотрим типичную задачу построение выборки. Предположим имеется таблица "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

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

четверг, 14 июня 2018 г.

среда, 13 июня 2018 г.

среда, 7 января 2015 г.

Карта митохондриальной ДНК (В.Африка) построенная методом средневзвешенной интерполяции

четверг, 19 июня 2014 г.

База исторических объектов

среда, 26 марта 2014 г.

Программа для построения маршрута по картам OpenStreetMap, C#

вторник, 25 марта 2014 г.

UserControl для редактирования даты и времени, C#

Пользовательские фильтры в запросе

четверг, 14 июня 2018 г.

среда, 13 июня 2018 г.

среда, 7 января 2015 г.