MySQL:组合多个 where 条件 [英] MySQL: Combining multiple where conditions

问题描述

我正在开发一个菜单系统，它接受一个 url，然后查询数据库来构建菜单.
我的菜单表是:

I'm working on a menu system that takes a url and then queries the db to build the menu.
My menu table is:

+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int(11)      | NO   | PRI | NULL    | auto_increment | 
| node_id | int(11)      | YES  |     | NULL    |                | 
| parent  | int(11)      | YES  |     | NULL    |                | 
| weight  | int(11)      | YES  |     | NULL    |                | 
| title   | varchar(250) | YES  |     | NULL    |                | 
| alias   | varchar(250) | YES  |     | NULL    |                | 
| exclude | int(11)      | YES  |     | NULL    |                | 
+---------+--------------+------+-----+---------+----------------+

我的问题的相关列是别名、父级和节点 ID.
所以对于像这样的网址:http://example.com/folder1/folder2/filename
别名可能 = "filename", "folder1", "folder2"
Parent = 父文件夹的 node_id.

The relevant columns for my question are alias, parent and node_id.
So for a url like: http://example.com/folder1/folder2/filename
Alias would potentially = "filename", "folder1", "folder2"
Parent = the node_id of the parent folder.

我所知道的是如何将 url 拆分为一个数组并检查别名以匹配每个部分.我不知道的是如何让它然后按别名匹配folder2"并且其父别名匹配folder1"的父级过滤.我正在想象这样的查询:

What I know is how to split the url up into an array and check the alias for a match to each part. What I don't know is how to have it then filter by parent whose alias matches "folder2" and whose parent alias matches "folder1". I'm imagining a query like so:

select * from menu 
where alias='filename' and 
where parent = node_id 
where alias='folder2' and parent = node_id 
where alias='folder1'

除非我知道以上是错误的.我希望这可以在单个查询中完成.

Except I know that the above is wrong. I'm hoping this can be done in a single query.

提前感谢您的帮助！

推荐答案

select * from menu 
where alias='filename' and 
parent = (select node_id from menu
          where alias='folder2' and
          parent = (select node_id from menu
                    where alias='folder1'
                   )
         )

这篇关于MySQL:组合多个 where 条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！