mysql - 从表中选择值 LIKE 值来自另一个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10400884/
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
mysql - select values from tables LIKE values from another table
提问by Kalreg
I have MySQL database with two tables: users and items. They look like this:
我有两个表的 MySQL 数据库：用户和项目。它们看起来像这样：
users:
用户：
id | user_name | desc
--------------------------
1 | john | tall
2 | dave | fat
3 | maria | pretty
items:
项目：
id | item_name | color
--------------------------
1 | trousers | red
2 | shoes | blue
3 | shoes | red
I want to select from database list of items that have specific id and name of it is LIKE a keyword. It's for use of simple search engine. I am making such query:
我想从具有特定 id 的项目的数据库列表中选择，它的名称就像一个关键字。它是为使用简单的搜索引擎。我正在做这样的查询：
SELECT id, user_name, ( SELECT item_name FROM items WHERE id = u.id ) as desc FROM users u WHERE desc LIKE "%shoes%" AND color LIKE "%blue%"
As a result, I would expect one line containing id = 2, username = dave and itemname = shoes because it's the only one row fulfilling my query. Unfortunately, I get a message that there is no a 'desc' column. I know that there is not such a column in 'users' but how tell MySQL to grab it from subquery named desc?
因此，我希望一行包含 id = 2、username = dave 和 itemname = shoes，因为它是唯一满足我查询的一行。不幸的是，我收到一条消息，说没有“desc”列。我知道“用户”中没有这样的列，但是如何告诉 MySQL 从名为 desc 的子查询中获取它？
Additional question: is that possible to work with WHERE ... LIKE command and array style like IN (val1, val2, val3)? I mean instead of loooong queries:
附加问题：是否可以使用 WHERE ... LIKE 命令和数组样式，如 IN (val1, val2, val3)？我的意思是而不是 loooong 查询：
SELECT name
FROM users
WHERE name
LIKE "%john%" OR name
LIKE "%steven%" OR name LIKE "bob%"
make shorter:
缩短：
SELECT name FROM users WHERE name LIKE IN ( "%john%", "%steven%", "%bob%")
Thanks in advance.
提前致谢。
回答by Jérémie Parker
Try this :
尝试这个 ：
SELECT
u.id, u.user_name, i.item_name, i.color
FROM
users AS u,
items AS i
WHERE
i.id = u.id
AND
i.item_name LIKE "%shoes%"
AND
i.color LIKE "%blue%"
For the second part, if you look at this page on MySQL dev siteyou will see that you can use REGEXPto match your result instead of LIKEso you I think you can use something like
对于第二部分，如果您查看MySQL 开发站点上的此页面，您将看到您可以使用REGEXP来匹配您的结果，而不是LIKE因此您我认为您可以使用类似
REGEXP 'john|steven|bob'
REGEXP 'john|steven|bob'
回答by Chetter Hummin
You need to have an inner join with items. Also I don't think you can use desc. Have used descr in my answer instead
您需要与项目进行内部联接。此外，我认为您不能使用 desc。在我的回答中使用了 descr
Haven't tested this, but please try the following
还没有测试过这个，但请尝试以下
SELECT u.id, u.user_name, i.item_name as descr
FROM users u INNER JOIN items i
ON i.id = u.id
WHERE i.item_name LIKE '%shoes%'
AND i.color LIKE '%blue%'
回答by Kalreg
After hours of hopless searching correct syntax i found what i was looking for. Best way, instead of using regexp and complicating a query i suggest using concat all column names into on string and then search it via as many LIKEs as we have keywords. Maybe that doesnt look elegant but is good enough for me. Example:
经过数小时无休止地搜索正确的语法，我找到了我要找的东西。最好的方法，而不是使用正则表达式并使查询复杂化，我建议使用将所有列名连接到字符串中，然后通过与关键字一样多的 LIKE 进行搜索。也许这看起来不优雅，但对我来说已经足够了。例子：
SELECT user_name, item_name FROM table WHERE CONCAT(user_name, item_name) LIKE '%keyword1%'
Look out for NULL records - if you concat only one field with null, the result becomes null too so use IFNULL(null_record, '').
留意 NULL 记录——如果你只用 null 连接一个字段，结果也会变成 null，所以使用 IFNULL(null_record, '')。
Hope it helps anyone.
希望它可以帮助任何人。
回答by Jathin
SELECT *
FROM users u
WHERE
u.id IN (SELECT id
FROM items
WHERE
items.id=u.id
AND
color LIKE '%$search%'
AND
item_name LIKE '%$search%'
)
