具有顺序过滤条件的 MySQL SELECT 查询

Question

希望标题没有那么困惑 - 老实说我不知道​​如何用一行更好地解释我的问题 :-)(顺便说一句，谷歌一下)

表“组”的结构是一个嵌套集，用于创建树状菜单。表“文章”中的文章引用此类组元素的 ID，以便 - 很好地 - 将它们分组:

Table 'article'                     Table 'group'
id   |   article      |   groupid           id   |  title    |   left    |   right
1    |   Bowl         |   2                 1    |  material |   1       |   6
2    |   Bowl         |   5                 2    |  wood     |   2       |   3
3    |   Cube         |   3                 3    |  steel    |   4       |   5
4    |   Cube         |   6                 4    |  shape    |   6       |   13
5    |   Bowl         |  10                 5    |  circle   |   7       |   8
6    |   Pyramid      |   2                 6    |  square   |   9       |   10
7    |   Pyramid      |   3                 7    |  rectangle|   11      |   12
8    |   Pyramid      |  11                 8    |  color    |   14      |   21
9    |   Bowl         |  11                 9    |  red      |   15      |   16
10   |   Cube         |   9                10    |  green    |   17      |   18
11   |   Pyramid      |   9                11    |  blue     |   19      |   20

要选择组元素的任意组合，我只需要一个查询，提及从 group.left 到 group.right 的范围，给我一组 group.id，我可以将其与 article.groupid 进行比较。以下声明回应所有由木材组成的元素:

SELECT
   a.article
FROM
   article AS a
LEFT JOIN 
   group AS g
ON (
      (
         2 >= g.left
      AND
         3 <= g.right
      AND
         g.id = a.groupid
      )
   )
GROUP BY
   a.article

现在我的问题是:我应该如何创建一个查询，以响应例如所有由木头制成且有任何颜色的元素？我希望在 WHERE 子句中重复串联的子查询会达成协​​议:

SELECT
    a.article
FROM
    article AS a
WHERE
    a.groupid IN
        (
            SELECT
                CONCAT(g.id) AS gr
            FROM
                group AS g
            WHERE
                (
                    2 >= g.left 
                AND
                    3 <= g.right
                )
        )
AND
    a.groupid IN
        (
            SELECT
                CONCAT(g.id) AS gr
            FROM
                group AS g
            WHERE
                (
                    14 >= g.left 
                AND
                    21 <= g.right
                )
        )

此查询没有响应。顺便说一句，我不喜欢子查询，但 JOINS 和 Sub-JOINS 的许多努力都没有奏效。我的思绪在旋转 - 你们有什么建议吗？

谢谢

Answer 1

编辑:现在工作。 Test it here (but in MSSQL)

第一种方法基于您的查询，问题来自非规范化设计。要执行查询，您将检查是否存在引用某种属性的同名文章行:

SELECT
    distinct a.article
FROM
    article AS a
WHERE
    exists 
      (      
           SELECT
                1
            FROM
                group AS g
            INNER JOIN
                article as a2
                on g.id = a2.groupid
            WHERE 
                a2.article = a.article and
                (2 <= g.left AND 3 >= g.right)
       ) and
      exists (
           SELECT
                1
            FROM
                group AS g
            INNER JOIN
                article as a2
                on g.id = a2.groupid
            WHERE 
                a2.article = a.article and
                (14 <= g.left AND 21 >= g.right)
        )

已编辑

OP 使用嵌套集。但也许文章的表格仍然没有很好地规范化。我建议考虑这个设计:

article( id, name )
groupNestedSet( id, title, left, right )
article_group( idArticle, idGroup)

样本归一化数据:

article( id, name )
1, Bowl   (only one time)
3, Cube   (also only one time)

groupNestedSet( id, title, left, right )
-- your data --

article_group( idArticle, idGroup)
1, 2
1, 5
3, 3
3, 5
...