从两个表中使用 like 运算符查询 SQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2108016/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Query SQL with like operator from two tables
提问by simone
How can I do a SQL query with the likeoperator from two different tables?
如何使用like来自两个不同表的运算符执行 SQL 查询？
I need something like:
我需要类似的东西：
select * from table1 where name like %table2.name
It's not a common field but a substring of a field on another table.
它不是一个公共字段，而是另一个表上一个字段的子字符串。
回答by T.J. Crowder
Edit
编辑
(original answer is further down)
（原始答案进一步向下）
Your comment (and subsequent edit) completely changes the question.
您的评论（以及随后的编辑）完全改变了问题。
To do that, you can use LIKEas part of the ONclause in a join:
为此，您可以在连接中将其LIKE用作ON子句的一部分：
CREATE TABLE a (foo varchar(254))
GO
CREATE TABLE b (id int, bar varchar(254))
GO
INSERT INTO a (foo) VALUES ('one')
INSERT INTO a (foo) VALUES ('tone')
INSERT INTO a (foo) VALUES ('phone')
INSERT INTO a (foo) VALUES ('two')
INSERT INTO a (foo) VALUES ('three')
INSERT INTO b (id, bar) VALUES (2, 'ne')
INSERT INTO b (id, bar) VALUES (3, 't')
SELECT a.foo
FROM a
INNER JOIN b ON a.foo LIKE '%' + b.bar
WHERE b.id = 2
(That's the SQL Server version; for MySQL, add in the various semicolons, remove the GOs, and use ...LIKE concat('%', b.bar)instead.)
（这是 SQL Server 版本；对于 MySQL，添加各种分号，删除GOs，然后使用...LIKE concat('%', b.bar)。）
That uses id= 2 to find bar= "ne" in table b, then prepends the %operator and uses it to filter results from a. Results are:
它使用id= 2bar在 table 中查找= "ne" b，然后添加%运算符并使用它来过滤来自a. 结果是：
one
tone
phone
You won't have to do the concat if you can store the operator in b.bar.
如果您可以将运算符存储在b.bar.
Separately, I was surprised to find that this works (on SQL Server) as well:
另外，我惊讶地发现这也适用（在 SQL Server 上）：
SELECT foo
FROM a
WHERE foo LIKE (
SELECT TOP 1 '%' + bar
FROM b
WHERE id = 2
)
...but the version using JOINis probably more flexible.
...但使用的版本JOIN可能更灵活。
That should get you going.
那应该能让你继续前进。
Original answer
原答案
(Arguably no longer relevant)
（可以说不再相关）
It's hard to tell what you're asking, but here's an example of using LIKEto limit the results from a JOIN:
很难说你在问什么，但这里有一个使用LIKE来限制结果的例子JOIN：
SELECT a.foo, b.bar
FROM someTable a
INNER JOIN someOtherTable b
ON a.someField = b.someField
WHERE a.foo LIKE 'SOMETHING%'
AND b.bar LIKE '%SOMETHING ELSE'
That will give you foofrom someTableand barfrom someOtherTablewhere the rows are related by someFieldand foostarts with "SOMETHING" and barends with "SOMETHING ELSE".
这会给你foo的someTable，并bar从someOtherTable其中的行是由相关的someField和foo开始有“东西”，并bar结束与“别的东西”。
回答by Anton Gogolev
Not particularly sure about the precise syntax, but here's an idea:
不是特别确定精确的语法，但这里有一个想法：
select ... from (
select ID, Foo as F from FooTable
union all
select ID, Bar as F from BarTable) R
where R.F like '%text%'
回答by Hammad Khan
Based on @TJCrowder answer
基于@TJCrowder 的回答
Test Tables
测试表
create table #A (
id varchar(10)
)
create table #b(
number varchar(5)
)
insert into #A values('123')
insert into #A values('456')
insert into #A values('789')
insert into #A values('0123')
insert into #A values('4567')
select * from #A
insert into #B values('12')
insert into #b values('45')
insert into #b values('987')
insert into #b values('012')
insert into #b values('666')
Actual query
实际查询
select * from #a, #b
where #a.id like '%' + #b.number + '%'
Modify the above query as you need such as
根据需要修改上述查询，例如
select #A.* from #A,#B ...
or
select * from #a, #b
where #a.id like #b.number + '%' -- one side match
