在单个 SQL 语句中使用 LIKE 和 IN 以及子查询

本文介绍了在单个 SQL 语句中使用 LIKE 和 IN 以及子查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在编写一个查询，其中我试图在子查询/CTE 中搜索通配符子字符串，并将此逻辑嵌套在我的 CASE 语句中.例如:

I am writing a query in which I am trying to search a subquery/CTE for a wildcard substring, and nesting this logic in my CASE statement. For example:

SELECT CASE WHEN '%' + text + '%' IN (SELECT Column1 FROM Table) THEN 'I am in Column1' ELSE text END FROM Table

不幸的是，似乎没有办法做到这一点.因为我需要使用 LIKE 运算符并且无法同时使用 LIKE 和 IN.我必须分别编写每个 LIKE 语句，这将用于 1000 多行.有人推荐更直接的解决方案吗?提前致谢！

Unfortunately, it looks like there is no possibly way to do this. Since I would need to use the LIKE operator and there is no way to use both LIKE and IN. I would have to write each LIKE statement separately, and that would be for 1000+ rows. Does anyone recommend a more immediate solution? Thanks kindly in advance!

--对不起，每条评论都有一些澄清.一个更好的例子:

-- Sorry, some clarifications per comments. A better example:

UserID | UserPeers | Gender -------------------------------------------- Mike | Tom1, Bob1 | M John | Tom1, Greg1 | M Sally |Mike1, John1 | F Sara | Sally1, Bob1 | F

在上表中，我需要搜索 UserPeers 列中的子字符串，以查看它们是否存在于 UserID 列中的任何位置.在这种情况下，成功返回的行将是 Sally 和 Sara 下的行，因为Mike"和Sally"存在于 UserID 下.

In the above table, I need to search the substrings in UserPeers columns to see if they exist anywhere in the UserID column. The rows that would be successfully returned in this case would be the ones under Sally and Sara, since 'Mike' and 'Sally' exist under UserID.

SELECT * FROM Users WHERE '%' + UserPeers + '%' LIKE (SELECT UserID FROM Users)

这里返回的错误是:子查询返回了 1 个以上的值.当子查询跟在 =、!=、<、<=、>、>= 或当子查询用作表达式时，这是不允许的.

The error returned here is: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

推荐答案

SELECT UserID, CASE WHEN EXISTS ( SELECT 1 FROM dbo.Users WHERE UserPeers LIKE '%' + u.UserID + '%' ) THEN 'I am in Column1' ELSE UserID END FROM dbo.Users AS u;