如何在一个 SQL 语句中喜欢两列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3828842/
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
How to like two columns in one SQL statement
提问by William_He
How to write this SQL?
这个SQL怎么写？
Table A Column aa
-----
Hyman
jim
alan
Table B Column bb
-----
Hymany
jimmy
william
The output is:
输出是：
-----
Hyman Hymany
jim jimmy
Because aa's value is bb's substring.
因为aa的值是bb的子串。
回答by Michael Pakhantsov
Select aa, bb
from a, b
where a.aa like '%' + b.bb + '%'
OR b.bb like '%' + a.aa + '%'
for mysql you need use concat('%', field, '%')
对于 mysql，您需要使用 concat('%', field, '%')
for oracle you need use '||' insteaf of '+'
对于oracle，您需要使用'||' 代替'+'
回答by Guffa
You can construct a pattern from the substring:
您可以从子字符串构造一个模式：
select a.aa, b.bb
from TableA a
inner join TableB b on b.bb like '%' + a.aa + '%'
回答by Adriaan Stander
You can try something like
你可以尝试类似的东西
DECLARE @TableA TABLE(
AA VARCHAR(20)
)
INSERT INTO @TableA SELECT 'Hyman'
INSERT INTO @TableA SELECT 'jim'
INSERT INTO @TableA SELECT 'alan'
DECLARE @TableB TABLE(
BB VARCHAR(20)
)
INSERT INTO @TableB SELECT 'Hymany'
INSERT INTO @TableB SELECT 'jimmy'
INSERT INTO @TableB SELECT 'william'
SELECT *
FROM @TableA A,
@TableB B
WHERE B.BB LIKE '%' + A.AA + '%'
回答by CristiC
You can do something like this:
你可以这样做：
SELECT
a.aa, b.bb
FROM
a
JOIN
b ON b.bb like '%' + a.aa + '%'
But you have to be very carefully because you can find multiple rows returned for the same name.
但是您必须非常小心，因为您可以找到为相同名称返回的多行。
回答by Francisco Cifuentes
This works for me:
这对我有用：
SELECT
a.aa, b.bb
FROM
a
JOIN
b ON b.bb like '%' || a.aa || '%'
回答by Rajasekaran P
To Improve Performance, Use Virtual column (you can create index etc).
要提高性能，请使用虚拟列（您可以创建索引等）。
create table text2(
x number(10),
x1 as ('%'||x||'%'))
insert into text2 (x) values ('100')
insert into text2 (x) values ('33')
commit
select * from text2
create table text1
( x number(10));
insert into text1 values (100 );
insert into text1 values (1001);
insert into text1 values (3300);
commit;
select text1.x, text2.x1 from text1 , text2
where text1.x like text2.x1
X X1 33 %33% 100 %100%
X X1 33 %33% 100 %100%
to know more about virtual column , http://www.dba-oracle.com/oracle11g/oracle_11g_function_based_columns.htm
要了解有关虚拟列的更多信息， 请访问 http://www.dba-oracle.com/oracle11g/oracle_11g_function_based_columns.htm
-Raj
-拉吉
