使用Microsoft Query和ODBC到SQL Server，进行复杂的查询 [英] Using Microsoft Query and ODBC to SQL Server, complicated query

问题描述

我在SQL Server中有一个类似于以下示例的视图.

I have a view in SQL Server that is somewhat similar to the following example.

         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的方式进行此操作.

I am perfectly capable of adding and modifying "OR SOME_FIELD = 'w'" countless numbers of times. However, I am making this view for a user to pull up in excel via ODBC. The user needs to be able to modify the inner select to her liking, to match whatever she happens to be limiting on at that time of the day/week/month/year/etc. I need to make this in a way that allows her to easily limit on SOME_FIELD.

有人对如何做到这一点有建议吗?理想情况下，我可以给她一个视图，她可以以逗号分隔的值列表放置SOME_FIELD不能包含的值.由于其他人在OTHER_TABLE中可能有多行，所以我不能仅仅限制她在该表中的位置.例如，某人可能具有SOME_FIELD ='x'，但在表中也有一行，其中SOME_FIELD ='s'.应该将此人排除在外，因为即使他们也有"s"，他们也都有"x".这就是为什么需要内部选择的原因.

Does anyone have suggestions on how to accomplish this? Ideally I could give her a view, which she could put a comma separated list of values that SOME_FIELD cannot be. Since people may have multiple rows in OTHER_TABLE I can't just have her limit off of that table specifically. For example someone may have SOME_FIELD = 'x' but also have a row in the table where SOME_FIELD = 's'. This person should be excluded because they have 'x' even though they also have 's'. So that is why the inner select is necessary.

感谢您的帮助.

推荐答案

不要为EXCEL用户创建查询，它们总是会打断它们，然后您必须对其进行调试.而是创建一个存储过程，传入CSV.在存储过程中，使用拆分功能拆分CSV并将其加入.用户将仅具有EXCEL查询，例如:

Don't create queries for EXCEL users, they always break them and then you have to debug them. Instead, create a stored procedure, pass in a CSV. In the stored procedure split the CSV using a split function and join to it. The user will only have an EXCEL query like:

EXEC YourProcedure 'x,y,z'

因此，它们不会中断查询.

As a result, they will not break the query.

为帮助使用拆分功能，请参见:"SQL Server中的数组和列表使用表值参数2008年"(Erland Sommarskog )，那么有很多方法可以在SQL Server中拆分字符串.本文介绍了几乎每种方法的优点和缺点:

To help with the split function, see: "Arrays and Lists in SQL Server 2008 Using Table-Valued Parameters" by Erland Sommarskog , then there are many ways to split string in SQL Server. This article covers the PROs and CONs of just about every method:

您需要创建一个拆分功能.拆分功能的使用方法如下:

You need to create a split function. This is how a split function can be used:

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

我更喜欢使用数字表方法在TSQL中拆分字符串，但是有很多方法可以在SQL Server中拆分字符串，请参见前面的链接，其中解释了每种方法的优点和缺点.

I prefer the number table approach to split a string in TSQL but there are numerous ways to split strings in SQL Server, see the previous link, which explains the PROs and CONs of each.

为使Numbers Table方法起作用，您需要进行一次时间表设置，这将创建一个包含从1到10,000行的表Numbers:

For the Numbers Table method to work, you need to do this one time table setup, which will create a table Numbers that contains rows from 1 to 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)

设置数字表后，创建此拆分函数:

Once the Numbers table is set up, create this split function:

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字符串拆分为表格并加入表格:

You can now easily split a CSV string into a table and join on it:

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

这篇关于使用Microsoft Query和ODBC到SQL Server，进行复杂的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！