Question

例......

Create Table QryTable (ID [int], Description [nvarchar](255), CSV_Vals [nvarchar](255))
insert into QryTable
values
    (1,  Description of record #1 ,  val_1,val_2,val_3,val_4,val_5,val_6 ),
    (2,  Description of record #2 ,  val_1,val_3,val_6,val_9,val_10,val_11 ),
    (3,  Description of record #3 ,  val_2,val_3,val_4,val_15,val_20,val_21 )

Create Table CriterionTbl (ID [int] identity (1,1), CriterionVals [nvarchar](50))
insert into CriterionTbl values ( val_3 ), ( val_4 )

职能方面类似于:

Select *
from QryTable
where CSV_vals like `AND (SELECT CriterionVals from CriterionTbl)`

调查结果与提出询问的结果相同,但独立价值是质疑危机清单表的结果,只有记录包括危机变数表中所有数值,而不是列入危机变数表中任何数值的记录:

Select *
from QryTable
where CSV_vals like  %val_3% 
and  CSV_vals like  %val_4%

我能够利用以下问询,在危机地区获得任何价值的记录,但我需要写一个问询,回答所有标准都来自危机调查局的纪录。

这是我在危机地区记录的任何情况:

select *
from QryTable
Join (select * from CriterionTbl as CT)
on QryTable.CSV_Vals like  %  + CT.CriterionVals +  %

This returns: records 1, 2, and 3, becauuse 1,2, and 3 all have either val_3 or val_4 But what I want is only records 1 and 3, because only 1 and 3 have val_3 and val_4

但是,我需要从次顺序来做到这一点,这样,结果就依据的是危机清单的内容。

Answer 1

Usually, an any-criteria condition would be applied using WHERE EXISTS(SELECT * FROM Criteria WHERE criteria-passes) and an all-criteria condition can be applied using WHERE NOT EXISTS(SELECT * FROM Criteria WHERE NOT criteria-passes).

在本案中,我认为你需要后者:

select *
from QryTable QT
where not exists (
    select *
    from CriterionTbl CT
    where QT.CSV_Vals not like  %  + CT.CriterionVals +  % 
    where  ,  + QT.CSV_Vals +  ,  not like  %,  + CT.CriterionVals +  ,% 
)

我在上述测试中添加了mas号,以避免在像“阀_2”和“val_20”这样的扼杀之间作假。

也可使用<代码>ALL(>>的操作器,其编号为:编码轴承和CASE,该表述标明成功或未达到1或0。

select *
from QryTable QT
where 1 = all (
    select case when  ,  + QT.CSV_Vals +  ,  like  %,  + CT.CriterionVals +  ,%  then 1 else 0 end
    from CriterionTbl CT
)

值得注意的是,如果<条码>CriterionTbl是空的,情况永远是真实的。

以上两项成果相同:

ID	Description	CSV_Vals
1	Description of record #1	val_1,val_2,val_3,val_4,val_5,val_6
3	Description of record #3	val_2,val_3,val_4,val_15,val_20,val_21

See this db<>fiddle for a demo. (Thanks to @BartMcEndree for the first fiddle set-up.)

Answer 2

它并非完美,只是另一种做法。但其中一项选择是将数据分开,与关键词进行比较,并选定符合所有条件的记录。

declare @QryTable Table (ID [int], Description [nvarchar](255), CSV_Vals [nvarchar](255))
insert into @QryTable 
VALUES
(1,  Description of record #1 ,  val_1,val_2,val_3,val_4,val_5,val_6 ),
(2,  Description of record #2 ,  val_1,val_3,val_6,val_9,val_10,val_11 ),
(3,  Description of record #3 ,  val_2,val_3,val_4,val_15,val_20,val_21 )

declare @CriterionTbl Table  (ID [int] identity (1,1), CriterionVals [nvarchar](50))
insert into @CriterionTbl VALUES ( val_3 ), ( val_4 );

declare @maxCount int = 0;
select @maxCount = Count(*) from @CriterionTbl

Select * from  @QryTable
where id in (
    select ID FROM
    (
        select rn = ROW_NUMBER() over ( partition by id order by id), 
            *
        from 
            (select id, CSV_Vals, value from @QryTable cross apply String_split(CSV_Vals,  , )) t
        inner join 
            (select CriterionVals FROM @CriterionTbl ) ct on t.value = ct.CriterionVals 
    ) x 
     group by id having count(id) = @maxCount
)

UPDATE: (thank you T.N) for simplified version i will add to answer - so it can be searchable
)


-- count(distinct ...) is used to allow handle possible duplicates in either table
declare @maxCount int = (select Count(distinct CriterionVals) from CriterionTbl)

Select qt.*
from QryTable qt
where @maxCount = (
    select count(distinct ct.CriterionVals)
    from String_split(qt.CSV_Vals,  , ) t
    inner join CriterionTbl ct
        on t.value = ct.CriterionVals 
)

结果是

ID	Description	CSV_Vals
1	Description of record #1	val_1,val_2,val_3,val_4,val_5,val_6
3	Description of record #3	val_2,val_3,val_4,val_15,val_20,val_21

Answer 3

例如。我们可以使用任何适当的分类,而不是将CristerionTbl作为inner加入(从任何可下载的表格中选取x)作为。

select *
from(
  select q.* 
    ,row_number()over(partition by q.id order by q.id) rn
    ,count(*)over(partition by q.id) qty
  from QryTable q
  inner join CriterionTbl c 
    on concat( , ,CSV_vals, , ) like concat( %, ,c.CriterionVals, ,% )
)t
where qty=(select count(*) from CriterionTbl) and rn=1

There by calculationrow_number()over(partition by q.id order by q.id) rn we can get any 1 row from join result.
Value count(*)over(partition by q.id) qty - for filter rows, where all values from CtriterionTbl matches to CSV_vals.
We do not use distinct or group by for repetitive values in CriterionVals, because this does not affect the result.

https://dbfiddle.uk/jB2uPExN”rel=“nofollow noretinger”>Demo(关于@TN例)

或

select *
from(
select q.* 
  ,row_number()over(partition by q.id order by q.id) rn
from QryTable q
inner join CriterionTbl c 
  on concat( , ,csv_vals, , ) like concat( %, ,c.criterionvals, ,% )
)t
where rn=(select count(*) from CriterionTbl)

Answer 4

由于每个人都,“TN”提供了我能够申请的解决办法。

在我使用的情况下,我正在建立一个储存的程序,接受一个参数,该参数由“营地”或“+”分开进行搜索。

If separated by &, then each search term must be included in the result set.

如果在+之前分离,每一结果必须包括一个搜索术语。

以我简化的原始表格为例,我采用的是完整的解决办法:

Create Table QryTable (ID [int], Description [nvarchar](255), Vals_1 [nvarchar](255), Vals_2 [nvarchar](255), Vals_3 [nvarchar](255))
insert into QryTable
values
    (1,  Description of record #1 ,  val_1 val_2 val_3 , val_4 val_5 val_6 ),
    (2,  Description of record #2 ,  val_1 val_3 val_6 , val_9 val_10 val_11 ),
    (3,  Description of record #3 ,  val_2 val_3 val_4 , val_15 val_20 val_21 )
    (3,  Description of record #4 ,  val_2 val_3 val_4 , val_15 val_20 val_21 )
    (3,  Description of record #5 ,  val_2 val_3 val_4 , val_15 val_20 val_21 )
    (3,  Description of record #6 ,  val_2 val_3 val_4 , val_15 val_20 val_21 )

declare @AndOr int = 0
declare @Search_Table table (s_id int IDENTITY(1,1), searchTerm nvarchar(50))
--to return results with any term separated by + 
declare @SearchString nvarchar(255) =  val_1+val_3+val7 
--to return results with all terms separated by &
declare @SearchString nvarchar(255) =  val_1&val_3&val7 

IF CHARINDEX( & ,@SearchString) > 0
    SET @AndOr =1; SET @SearchString = replace(@SearchString,  && ,  | )
IF CHARINDEX( + ,@SearchString) > 0
    SET @SearchString = replace(@SearchString,  ++ ,  | )
    
insert into @Search_Table SELECT    distinct value FROM string_split(@SearchString, | ) ST

if @AndOr = 0
    SELECT  *
    FROM    QryTable QT
    JOIN    @Search_Table ST
    ON  (       QT.Vals_1 LIKE  %  + ST.SearchString +  % 
            OR  QT.Vals_2 LIKE  %  + ST.SearchString +  % 
            OR  QT.Vals_3 LIKE  %  + ST.SearchString +  % 
        )
if @AndOr = 1
    SELECT  *
    FROM    QryTable QT
    WHERE   1=ALL   (
                        SELECT CASE WHEN QT.Vals_1 LIKE  %  + ST.SearchString +  % 
                        OR  QT.Vals_2 LIKE  %  + ST.SearchString +  % 
                        OR  QT.Vals_3 LIKE  %  + ST.SearchString +  % 
                        THEN 1 ELSE 0 END
                        FROM @Search_Table ST
                    )

友情链接