SQL“LIKE”查询多个条件 [英] SQL "LIKE" query with multiple condition

问题描述

我想查询2个列表，从表'A''中找到列''A1''，列为2列即可。表'B'中的B1，B2。查询应该使用LIKE运算符，其中列A1喜欢（''％B1％''）或列A1喜欢（''％B2％''）。



当前结果给了我2个单独记录的喜欢，这是正确的，因为列匹配多行。



这是我想要的...

如果列A1与B1或B2匹配，则应跳过寻找更多匹配。它不应该在表B的其他行中查找。另外我想标记列与B1或B2匹配。





< br $> b $ b

谢谢



Pinank Shah

解决方案

它不会停止搜索是否找到一个匹配，但你可以过滤记录，最后你会得到你想要的结果

试试这个...

   tbl1  as  
（
 选择 '  abc'  as  A1 
  union   all  
 选择 '  xyz'  as  A1 
）
  s选择 * 来自 
（
 选择 Row_Number（） over （ partition   by  A1  order   by  A1）SrNo，* 来自 tbl1 
  left   join  
（
  select  '  b'  as  B1，'  A'  as  B2 
  union   all  
  select  '  a'  as  B1，'  x'  as  B2 
） as  tbl2  on  A1  like  ' ％' + B1 + ' ％' 或 A1 喜欢 ' ％' + B2 +  ' ％' 
） as  ftbl 其中 srno = 1 

快乐编码！

：）

I 我会逐步完成这个解决方案......

引用：

查询应该使用LIKE列A1的运算符（''％B1％） '）或列A1喜欢（''％B2％''）

。

可以用

来解决SELECT *来自内部联接B上A1喜欢'' ％''+ B2 +''％''或A1喜欢''％''+ B1 +''％''

Quote：

如果列A1与B1或B2匹配则应跳过寻找更多匹配

使用类似

的内容SELECT TOP 1 * FROM A ... 

一旦找到一个匹配的行就会停止。请注意，您可以将此与 ORDER BY ... 结合使用以优化其行为。

引用：

此外，我想标记与B1或B2匹配的列

。我将确定使用哪个列（B1或B2）一个 CASE 这样的语句

 CASE WHEN CHARINDEX（B2，A1）> 0那么''''''''''''''''''''''''''''$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $使用子查询识别表A上的单个匹配行，我将调用该子查询的结果 TEMPRES ，然后我们要去加入A到TEMPRES以更新相关行。 
 
 
 
在下面的完整示例中，我假设您有一些方法可以唯一地识别表A中的每一行（这通常是一个非常好的主意 - 有一个查看列的IDENTITY（s，i）属性。在我的例子中，我称之为 ID  
 
 
 更新 A 
  SET  A1 = TEMPRES.WHICH 
  FROM  A 
  INNER   JOIN  
（ SELECT   TOP   1  A.ID， CASE   WHEN  CHARINDEX（B2，A1）>  0  那么 '  B2'  ELSE  '  B1'  END   AS  WHICH 
  FROM  A  INNER   JOIN  B  ON  A1  LIKE  ' ％' + B2 + ' ％' 或 A1  LIKE  ' ％' + B1 + ' ％'）TEMPRES  ON  A.ID = TEMPRES.ID 
 
 
最后，您可能会发现这些参考资料很有用
 
  http://www.akadia.com/services/sqlsrv_subqueries.html  [ ^ ] 
 
 http://msdn.microsoft.com/en-us/library/ms186775.aspx  [ ^ ] 
 
 
 
  

Hi,

i want to query on 2 lists finding in column ''A1'' from Table ''A'' with the 2 columns viz. B1, B2 in Table ''B''. Query should be using "LIKE" operator where Column A1 like (''%B1%'') OR Column A1 like (''%B2%'').

current result gives me 2 likes for single record, which is correct in its place because Column matched from multiple rows.

Here is i want...
If column A1 matched from B1 OR B2 then should skip looking for further matches. it should not look in other rows of table B. Also i want to mark that the column matched with B1 or B2.




Thanks

Pinank Shah

解决方案

It will not stop searching if one match found but you can filter records so, finally you will get result you want
Try this...

with tbl1 as
        (
            select 'Abc' as A1
            union all
            select 'xyz' as A1
        )
select * from
(
    select Row_Number() over(partition by A1 order by A1) SrNo,* from tbl1
    left join
        (
            select 'b' as B1, 'A' as B2
            union all
            select 'a' as B1, 'x' as B2
        ) as tbl2 on A1 like '%' + B1 + '%' or A1 like '%' + B2 + '%'
) as ftbl where srno=1

Happy Coding!
:)

I''ll walk through this solution bit by bit ...

Quote:

Query should be using "LIKE" operator where Column A1 like (''%B1%'') OR Column A1 like (''%B2%'')

.
Could be solved with

SELECT * FROM A INNER JOIN B ON A1 LIKE ''%'' + B2 + ''%'' OR A1 LIKE ''%'' + B1 + ''%''

Quote:

If column A1 matched from B1 OR B2 then should skip looking for further matches

Use something like

SELECT TOP 1 * FROM A ...

which will stop once it has found a single matching row. Note that you can use this in conjunction with ORDER BY ... to refine it''s behaviour.

Quote:

Also i want to mark that the column matched with B1 or B2

.I''m going to identify which column we used (B1 or B2) using a CASE statement like this

CASE WHEN CHARINDEX(B2, A1) > 0 THEN ''B2'' ELSE ''B1'' END AS WHICH

To pull of this together we''re first going to identify the single matching row on table A with a sub-query and I''m going to call the results of that sub-query TEMPRES, then we''re going to join A to TEMPRES to update the relevent row.

In the full example below I''ve assumed that you have some way of uniquely identifying each row in table A (this is usually a very good idea - have a look at the IDENTITY(s,i) property for a column). In my example I''ve called this ID

UPDATE A
SET A1 = TEMPRES.WHICH
FROM A
INNER JOIN
    (SELECT TOP 1 A.ID, CASE WHEN CHARINDEX(B2, A1) > 0 THEN 'B2' ELSE 'B1' END AS WHICH
        FROM A INNER JOIN B ON A1 LIKE '%' + B2 + '%' OR A1 LIKE '%' + B1 + '%' ) TEMPRES ON A.ID=TEMPRES.ID

Finally, you might find these references helpful
http://www.akadia.com/services/sqlsrv_subqueries.html[^]
http://msdn.microsoft.com/en-us/library/ms186775.aspx[^]

[Edit - for some reason I started using column A2 instead of A1 ... corrected the sql]

这篇关于SQL“LIKE”查询多个条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！