MySQL 使用子查询中的 WHERE 匹配多个结果

发布于 2024-11-14 02:27:09 字数 992 浏览 2 评论 0原文

我被困在这个问题上：我正在开发 Joomla！ 1.6 扩展和使用类别。

对于那些没有使用过类别的人，它们的工作方式如下：

您创建一个类别（名称、描述、父级）并将其存储在类别表中。

|  id  |  parent_id  |  lft  |  rgt  |  level  |  etc  |
--------------------------------------------------------
|   1  |      0      |   -   |   -   |    1    |
|   2  |      1      |   -   |   -   |    2    |

id和parent_id，我不需要解释。

level：是类别的级别，为 0 ROOT，因此 root 的子级将是级别 1，级别 1 的子级将是级别 2 等

lft 和 rgt 是用于包装所有类别子类别的值（当您需要获取 1 级子级到 2 级的 IE 类别时很有用）

我的表有一个 catid 列，它指向相应的 ID类别。

我需要的是获取该类别中的所有子项，但仅低一级，

即，如果我的 catid 指向 2 级类别，我需要获取级别为 3 且其父级 id = 的所有类别catid

如何编写查询？

我有这样的东西（查询是由 PHP 脚本编写的）/

让我们尝试使用 1 级类别

SELECT * 
FROM my_table 
WHERE catid = 1 
   OR catid = (SELECT catid 
               FROM categories 
               WHERE level = 2 AND parent_id = 1)

如何与所有子查询的值进行比较？

原文

I'm stuck on this: I'm working on a Joomla! 1.6 extension and using categories.

For those who haven't used categories, they work like this:

You create a category (name, description, parent) and its stored in the categories table.

|  id  |  parent_id  |  lft  |  rgt  |  level  |  etc  |
--------------------------------------------------------
|   1  |      0      |   -   |   -   |    1    |
|   2  |      1      |   -   |   -   |    2    |

id and parent_id, I don't need to explain.

level: is the level of the category, being 0 ROOT, so a child of root would be level 1 and a child of a level one be level 2 , etc

lft and rgt are values used to wrap all categories sub categories (useful when you need to get I.E. category of level 1 children up to level 2)

My table has a catid column, that points to the ID of the corresponding category.

What I need is to get all children from that category but only one level lower,

I.E. if my catid points to a level 2 category I would need to get all categories whose level is 3 and their parent's id = catid

How do I write the query?

I have something like this (the query is written by a PHP script)/

Let's try with a level 1 category

SELECT * 
FROM my_table 
WHERE catid = 1 
   OR catid = (SELECT catid 
               FROM categories 
               WHERE level = 2 AND parent_id = 1)

How can I compare against all of the subquery's values?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

十年不长 2024-11-21 02:27:09

如果没有子查询，您将获得更好的性能：

SELECT t.*
FROM my_table AS t
INNER JOIN categories AS c ON t.catid = c.id
WHERE c.id = 1
OR c.parent_id = 1

我故意省略了 level = 2，因为这是冗余信息。如果类别的parent_id=1，则其级别=2。

You'll get better performance without a subquery:

SELECT t.*
FROM my_table AS t
INNER JOIN categories AS c ON t.catid = c.id
WHERE c.id = 1
OR c.parent_id = 1

I omitted the level = 2 on purpose, as this is a redundant information. If a category has a parent_id=1, then its level=2.

回复收藏 0 原文

潦草背影 2024-11-21 02:27:09

您可以使用IN。

SELECT *
FROM   my_table
WHERE  catid = 1
        OR catid IN (SELECT catid
                     FROM   categories
                     WHERE  level = 2
                            AND parent_id = 1)

You could use IN.

SELECT *
FROM   my_table
WHERE  catid = 1
        OR catid IN (SELECT catid
                     FROM   categories
                     WHERE  level = 2
                            AND parent_id = 1)

回复收藏 0 原文

~没有更多了~