Использование Microsoft Query и ODBC для SQL Server, сложный запрос

У меня есть представление в 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». Вот почему внутренний выбор необходим.

Спасибо за вашу помощь.


odbc sql sql-server excel
person wilbbe01    schedule 19.11.2010    source источник

Ответы (1)


arrow_upward
2
arrow_downward

Не создавайте запросы для пользователей 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
person KM.    schedule 19.11.2010