У меня есть представление в SQL Server, которое чем-то похоже на следующий пример.
SELECT *
FROM PEOPLE
LEFT OUTER JOIN (SELECT ID
FROM OTHER_TABLE
WHERE SOME_FIELD = 'x'
OR SOME_FIELD = 'y'
OR SOME_FIELD = 'z') AS PEOPLE_TO_EXCLUDE ON PEOPLE.ID = PEOPLE_TO_EXCLUDE.ID
WHERE PEOPLE_TO_EXCLUDE.ID IS null
хлопот:
Я вполне способен добавлять и изменять "OR SOME_FIELD = 'w'" бесчисленное количество раз. Тем не менее, я делаю это представление для того, чтобы пользователь мог открыть его в Excel через ODBC. Пользователь должен иметь возможность изменить внутренний выбор по своему вкусу, чтобы он соответствовал тому, что он ограничивает в это время дня/недели/месяца/года/и т. д. Мне нужно сделать это таким образом, чтобы она могла легко ограничить SOME_FIELD.
Есть ли у кого-нибудь предложения о том, как это сделать? В идеале я мог бы дать ей представление, в котором она могла бы поместить список значений, разделенных запятыми, которые не могут быть SOME_FIELD. Поскольку у людей может быть несколько строк в OTHER_TABLE, я не могу просто отключить ее ограничение для этой таблицы. Например, у кого-то может быть SOME_FIELD = 'x', но также может быть строка в таблице, где SOME_FIELD = 's'. Этот человек должен быть исключен, потому что у него есть "x", хотя у него также есть "s". Вот почему внутренний выбор необходим.
Спасибо за вашу помощь.
Ответ 1 · рейтинг 2 · KM.
Не создавайте запросы для пользователей EXCEL, они всегда ломают их, и тогда вам придется их отлаживать. Вместо этого создайте хранимую процедуру, передайте CSV. В хранимой процедуре разделите CSV с помощью функции разделения и присоединитесь к нему. У пользователя будет только запрос EXCEL, например:
EXEC YourProcedure 'x,y,z'
В результате они не сломают запрос.
Чтобы получить помощь по функции разделения, см.: "Массивы и списки в SQL Server 2008 с использованием таблицы -Valued Parameters" Эрланда Соммарскога, то в SQL Server существует множество способов разделения строки. В этой статье рассматриваются плюсы и минусы практически каждого метода:
Вам нужно создать функцию разделения. Вот как можно использовать функцию разделения:
SELECT
*
FROM YourTable y
INNER JOIN dbo.yourSplitFunction(@Parameter) s ON y.ID=s.Value
Я предпочитаю использовать числовую таблицу для разделения строки в TSQL, но существует множество способов разделения строк в SQL Server, см. предыдущую ссылку, в которой объясняются плюсы и минусы каждого из них.
Чтобы метод "Таблица чисел" работал, вам нужно выполнить одну настройку таблицы времени, которая создаст таблицу Numbers, содержащую строки от 1 до 10 000:
SELECT TOP 10000 IDENTITY(int,1,1) AS Number
INTO Numbers
FROM sys.objects s1
CROSS JOIN sys.objects s2
ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)
После настройки таблицы Numbers создайте эту функцию разделения:
CREATE FUNCTION [dbo].[FN_ListToTable]
(
@SplitOn char(1) --REQUIRED, the character to split the @List string on
,@List varchar(8000)--REQUIRED, the list to split apart
)
RETURNS TABLE
AS
RETURN
(
SELECT
ListValue
FROM (SELECT
LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number - 1))) AS ListValue
FROM (
SELECT @SplitOn + @List + @SplitOn AS List2
) AS dt
INNER JOIN Numbers n ON n.Number < LEN(dt.List2)
WHERE SUBSTRING(List2, number, 1) = @SplitOn
) dt2
WHERE ListValue IS NOT NULL AND ListValue!=''
);
GO
Теперь вы можете легко разделить строку CSV на таблицу и присоединиться к ней:
Create Procedure YourProcedure
@Filter VARCHAR(1000)
AS
SELECT
p.*
FROM PEOPLE p
LEFT OUTER JOIN (SELECT
o.ID
FROM OTHER_TABLE o
INNER JOIN (SELECT
ListValue
FROM dbo.FN_ListToTable(',',@Filter )
) f ON o.SOME_FIELD=f.ListValue
) x ON p.ID=x.ID
WHERE x.ID IS null
GO
