使用Microsoft Query和ODBC to SQL Server，复杂的查询

Question

我在 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'”。不过，我正在为用户创建此视图，以便通过 ODBC 在 excel 中提取。用户需要能够根据自己的喜好修改内部选择，以匹配她在一天/周/月/年/等的某个时间碰巧限制的任何内容。我需要以一种允许她轻松限制 SOME_FIELD 的方式进行此操作。

有人对如何实现这一目标有建议吗？理想情况下，我可以给她一个视图，她可以在其中放置一个逗号分隔的值列表，而 SOME_FIELD 不能是。由于人们可能在 OTHER_TABLE 中有多行，所以我不能专门限制她对该表的限制。例如，某人可能有 SOME_FIELD = 'x'，但表中也有一行 SOME_FIELD = 's'。此人应被排除在外，因为他们有“x”，尽管他们也有“s”。这就是为什么内部选择是必要的。

感谢您的帮助。

Answer 1

不要为 EXCEL 用户创建查询，他们总是会破坏它们，然后您必须调试它们。相反，创建一个存储过程，传入 CSV。在存储过程中，使用 split 函数拆分 CSV 并加入其中。用户只会有一个 EXCEL 查询，例如：

EXEC YourProcedure 'x,y,z'

因此，他们不会破坏查询。

要帮助使用 split 函数，请参阅："SQL Server 2008 中使用表的数组和列表-值参数”作者：Erland Sommarskog ，那么在 SQL Server 中分割字符串的方法有很多种。本文涵盖了几乎每种方法的优点和缺点：

您需要创建一个 split 函数。这是分割函数的使用方式：

SELECT
    *
    FROM YourTable                               y
    INNER JOIN dbo.yourSplitFunction(@Parameter) s ON y.ID=s.Value

我更喜欢数字表方法在 TSQL 中分割字符串，但在 SQL Server 中分割字符串的方法有很多种，请参阅前面的链接，其中解释了每种方法的优点和缺点。

要使 Numbers Table 方法发挥作用，您需要执行此一次时间表设置，这将创建一个包含 1 到 10,000 行的 Numbers 表：

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