在单个 SQL 语句中使用 LIKE 和 IN 以及子查询 [英] Using LIKE and IN and a Subquery in a single SQL Statement

问题描述

我正在编写一个查询，其中我试图在子查询/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;

这篇关于在单个 SQL 语句中使用 LIKE 和 IN 以及子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！