逗号分隔的字符串过滤参数 [英] comma separated string filter parameter

问题描述

我使用复选框列表作为sql查询的过滤器。我把这些值作为csv传递到目前为止......所以差不多好。



问题是，如果我选择多个值，那么我将忽略/跳过没有至少（不确定如何放置）所有选定值的记录。



ex ...

（破折号仅用于格式化目的）

person ------- person / sport --------- sport

_________________________________________

a ------------ a |棒球------棒球

b ------------ a |足球------足球

c ------------ a |棒球------篮球

-------------- b |棒球

-------------- b |足球

-------------- c |棒球

-------------- c |足球

-------------- c |篮球



如果我选择棒球，那么人，a，b和c将被退回。

如果我选择棒球和足球然后所有3个人都将出现在gridview中。

但是，如果我选择任何组合与baseketball，那么b将不会显示，因为在很多桌子中，b没有篮球。 />


例如，如果我选择足球和篮球

或

棒球，足球和篮球

只有a和c人会出现



我想要的是所有3人出现，因为如果我选择所有体育运动，那么b有足球吗？ 。



非常不确定我需要采取哪些步骤以及我需要做出的改变才能实现这一目标。



感谢您的帮助。



sql split功能：

  ALTER  功能 fnSplitter（ @IDs   Varchar （ 100 ））
 返回  @ Tbl_IDs  表（ID  Int ） As  
 
 开始 
 < span class =code-comment>  -   附加逗号 
 设置  @ IDs  =  @ IDs  + ' < span class =code-string>，' 
   -   保持位置的索引搜索 
 声明  @ Pos1   Int  
 声明  @ pos2   Int  
 
   -   从第一个字符 
 设置 @Pos1 = 1 
 设置 @ Pos2 = 1 
 
   @ Pos1< Len（ @ID ）
 开始 
 设置  @ Pos1  = CharIndex（' ，'， @ IDs ， @ Pos1 ）
 插入  @ Tbl_IDs  选择 Cast（子串（ @ID ， @ Pos2 ，@ Pos1- @ Pos2） As   Int ）
 <跨度lass =code-comment>  -   转到下一个非逗号字符 
 设置 @ Pos2 = @ Pos1 + 1 
   -   从下一个charcater搜索 
 设置  @ Pos1  = @ Pos1 + 1 
 结束 
 返回 
 结束 

dataset / tableadapter查询

  SELECT   DISTINCT  
 person.personID，sports.sportsID，person.personName，sports.sport，
 
  FROM  sports  INNER   JOIN  
 person  INNER   JOIN  
 personSport  ON  personSport.personID = person.personID 
  INNER   JOIN  
 personSport  ON  personSport.sportID = sports.sportsID 
 
  WHERE （person.personName  LIKE  ' ％' +  @ personName  + ' ％'）
  AND  
（sports.sportID  IN  
（ SELECT  ID  FROM  dbo.fnSplitter （ @ IDs ） AS  fnSplitter_1）  OR   @ IDs  =  0 ）
  / *   @IDs = 0是初始值...在页面加载时弹出eveerything * /  

html中的
，我有一个gridview，在objectdatasource中删除，select参数是发送到@的复选框列表ID来自拆分函数，类型是字符串

解决方案

你甚至不需要一个函数。由于您要传递逗号分隔值，因此您需要进行动态查询。例如，声明一个SQL变量，然后将该变量设置为select语句。之后执行该变量。

  DECLARE   @ SQL   VARCHAR （ 1000 ）
 
  SET   @ SQL  = 
 '  SELECT DISTINCT 
 person.personID，sports.sportsID，person.personName，sports.sport，
 
 FROM sports INNER JOIN 
 person INNER JOIN 
 personSport ON personSport.personID = person.personID 
 INNER JOIN 
 personSport ON personSport.sportID = sports.sportsID 
 
 WHERE（person.personName LIKE''％' +  @ personName  + ' ％''）
 AND 
（spor ts.sportID IN（@IDs））' 
 
  EXEC （ @ SQL ）

i''m using a checkbox list as a filter for a sql query. i pass the values as a csv and so far...so almost good.

the problem is, if i select more than one value, then i will ignore/skip over records that do NOT have AT LEAST (not sure how else to put it) all of the selected values.

ex...
(dashes were for formatting purposes only)
person -------person/sport---------sport
_________________________________________
a ------------a | baseball------baseball
b ------------a | football------football
c ------------a | baseball------basketball
--------------b | baseball
--------------b | football
--------------c | baseball
--------------c | football
--------------c | basketball

if i select "baseball" then person a, b, and c will be returned.
if i select baseball and football then all 3 persons will show up in the gridview.
however, if i select any combination with baseketball, then b will NOT show up because in the many-many table, b does not have basketball.

for instance, if i pick football and basketball
or
baseball, football, and basketball
only persons a and c will show up

what i would like is for all 3 to show up because person b DOES have football if i select all sports.

very unsure what steps i need to take and changes i need to make in order for that to happen.

thanks for the help.

sql split function:

ALTER FUNCTION fnSplitter (@IDs Varchar(100) )  
Returns @Tbl_IDs Table  (ID Int)  As  

Begin 
 -- Append comma
 Set @IDs =  @IDs + ',' 
 -- Indexes to keep the position of searching
 Declare @Pos1 Int
 Declare @pos2 Int
 
 -- Start from first character 
 Set @Pos1=1
 Set @Pos2=1

 While @Pos1<Len(@IDs)
 Begin
  Set @Pos1 = CharIndex(',',@IDs,@Pos1)
  Insert @Tbl_IDs Select  Cast(Substring(@IDs,@Pos2,@Pos1-@Pos2) As Int)
  -- Go to next non comma character
  Set @Pos2=@Pos1+1
  -- Search from the next charcater
  Set @Pos1 = @Pos1+1
 End 
 Return
End

dataset/tableadapter query

SELECT DISTINCT 
                         person.personID, sports.sportsID, person.personName, sports.sport,

FROM            sports INNER JOIN
                person INNER JOIN
                personSport ON personSport.personID = person.personID 
                       INNER JOIN
                personSport ON personSport.sportID = sports.sportsID

WHERE        (person.personName LIKE '%' + @personName + '%') 
              AND 
             (sports.sportID IN
                    (SELECT   ID FROM dbo.fnSplitter(@IDs) AS fnSplitter_1) OR @IDs = 0)
/*the @IDs = 0 is for the initial value...eveerything pops up on pageload*/

in the html, i have a gridview, dropped in an objectdatasource and the select parameter is the checkboxlist which sends to "@IDs" from the split function and the type is "string"

解决方案

You don''t even need a function. Since you are passing comma separated values, you will need to make a dynamic query. For instance, declare a SQL variable, then set that variable to your select statement. After that execute that variable.

DECLARE @SQL VARCHAR(1000)

SET @SQL = 
	'SELECT DISTINCT 
                         person.personID, sports.sportsID, person.personName, sports.sport,
 
FROM            sports INNER JOIN
                person INNER JOIN
                personSport ON personSport.personID = person.personID 
                       INNER JOIN
                personSport ON personSport.sportID = sports.sportsID
 
WHERE        (person.personName LIKE ''%' + @personName + '%'') 
              AND 
             (sports.sportID IN(@IDs))'
			
EXEC(@SQL)

这篇关于逗号分隔的字符串过滤参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！