从表中选择值，从另一个表中选择值。

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、用户名= 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:

另外一个问题:是否可以在……类似于命令和数组样式(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.

提前谢谢。

4 个解决方案

#1

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 site you will see that you can use REGEXP to match your result instead of LIKE so you I think you can use something like

第二部分，如果你看看MySQL dev网站上的这个页面，你会发现你可以使用REGEXP来匹配你的结果，而不是像你这样，我认为你可以使用类似的东西。

REGEXP 'john|steven|bob'

REGEXP约翰steven | |鲍勃的

#2

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%'

#3

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:

经过数小时无希望的搜索正确语法后，我找到了我要找的东西。最好的方法是，不要使用regexp并使查询复杂化，我建议将所有列名合并到字符串中，然后通过像我们有关键字那样的许多点来搜索它。也许这看起来不优雅，但对我来说已经足够好了。例子:

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，所以使用IFNULL(null_record)。

Hope it helps anyone.

希望它可以帮助任何人。

#4

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%'
            )

#1