MySQL-具有重复固定id范围的查询

Question

我有以下查询：

SELECT child.id_catalog_category AS id_category, ancestor.id_catalog_category AS tree
FROM catalog_category AS child
JOIN catalog_category AS ancestor
ON (child.lft BETWEEN ancestor.lft AND ancestor.rgt)
WHERE ancestor.id_catalog_category != 1
ORDER BY id_category ASC, tree ASC

它是针对层次产品类别的二叉树的重构。对于一个id_category，我们最多可以有4个“树”值，如示例所示：

id_category /树 3 2 3 3 4 2 4 3 4 4 5 2 5 3 5 5 6 2 6 3 6 6 7 2 7 3 7 7

所期望的结果应是：

id / id_category /树 1 3 2 2 3 3 3空空 4空空 1 4 2 2 4 3 3 4 4 4零零.

换句话说，我想为每个id_category添加一个从1到4的区域id，如果id_category的值小于4，它应该显示空值。

问候

Answer 1

为了复制您指定的数据集，我这样做是为了获得一个工作集：

CREATE TABLE cc (id_category INT, tree INT);
INSERT INTO cc VALUES (3,2),(3,3),(4,2),(4,3),(4,4),(5,2),(5,3),
                      (5,5),(6,2),(6,3),(6,6),(7,2),(7,3),(7,7);
SELECT cc.* FROM cc ORDER BY 1,2;

这里的SQL：http://sqlfiddle.com/#!2/16249/3

下面是我如何处理这个问题的方法。首先，我将得到一个明确的id_category值列表。很直截了当。(我可以使用不同的关键字，而不是使用组，不管是哪种。)

SELECT id_category
  FROM cc
 GROUP BY id_category
 ORDER BY id_category

然后，我将从每一行生成四行。因此，我将将前面的查询包装为内联视图(将其括在一组括号中，给它一个别名，并引用整个混乱，就像它只是一个表名一样)。就像这样：

SELECT c.id_category, j_.j
  FROM (SELECT 1 AS j UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) j_
  JOIN (
        SELECT cc.id_category
          FROM cc cc
         GROUP BY cc.id_category
         ORDER BY cc.id_category
       ) c
 ORDER BY c.id_category, j_.j

我使用另一个内联视图返回整数1到4，并执行交叉连接，为每个不同的id_category获取四行。基本上可以得到我想要返回的结果集的轮廓..。但是，对于树列，我没有任何值(除了NULL)。

现在，我需要备份，并开始处理另一个行集，基本上是cc表中的一个有序集，但这次包括来自树列的值。在这里，我不关心得到确切的四行，只是树列中有值的行。同样，非常简单的是：

SELECT s.id_category_id, s.tree
  FROM cc s
 ORDER BY s.id_category, s.tree

但是现在，我希望在每个id_category中为每一行分配一个相对行号。我可以将该查询包装在一组括号中，给它一个别名，并将它当作一个表来处理，如下所示：

SELECT @i := IF(r.id_category = @prev_idcat,@i + 1,1) AS i
     , @prev_idcat := r.id_category AS id_category
     , r.tree
  FROM (SELECT @i := 0, @prev_idcat := NULL) i_ 
  JOIN (
        SELECT s.id_category, s.tree
          FROM cc s
         ORDER BY s.id_category, s.tree
       ) r

我对用户变量使用了一个MySQL技巧，为每个不同的id_category分配升序整数值，从1开始。这里的诀窍是让MySQL为我排序行(在内联视图中别名为r )，并在用户变量中“保存”上一行的id_category，以便将其与下一行进行比较。

现在，我们真的到了可以在MySQL中使用公共表表达式的地步了。但是由于它们不是，我们向前推进，嵌套我们的内联视图。

因此，我将给每个“行编号”查询一个别名，并引用它们，就像它们是表一样；查询将是表单.

SELECT b.*, q.*
  FROM ( ) b
  LEFT
  JOIN ( ) q
    ON q.id_category = b.id_category AND q.i = b.j

(我们省略了这些内联视图的内容，只是为了全面了解该语句的实际操作。)

这看起来很难看，但这就是魔法发生的地方。我从b中提取每个id_category的四行，并将其加入到q中，在id_category和“行号”上进行匹配。它是一个左外部联接，所以我将从b中获取所有行，并从q中获取任何“匹配”行。

SELECT b.id_category, q.tree
  FROM (SELECT c.id_category, j_.j
          FROM (SELECT 1 AS j UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
               ) j_
          JOIN (
                SELECT cc.id_category
                  FROM cc cc
                 GROUP BY cc.id_category
                 ORDER BY cc.id_category
               ) c
         ORDER BY c.id_category, j_.j
       ) b   
  LEFT 
  JOIN (SELECT @i := IF(r.id_category = @prev_idcat,@i + 1,1) AS i
             , @prev_idcat := r.id_category AS id_category
             , r.tree
          FROM (SELECT @i := 0, @prev_idcat := NULL) i_ 
          JOIN (
                SELECT s.id_category, s.tree
                  FROM cc s
                 ORDER BY s.id_category, s.tree
               ) r
       ) q
    ON q.id_category = b.id_category AND q.i = b.j
 ORDER BY b.id_category, b.j

规范中唯一剩下的是生成id列的值。如果我要插入一个表，我可以使用一个AUTO_INCREMENT列来完成它。但是，如果没有这一点，我生成id值的最方便的地方是内联视图中的b。只是稍微调整一下，最后，我们有一个查询的怪物，它返回指定的结果集：

SELECT b.k AS id, b.id_category, q.tree
  FROM (SELECT @k := @k + 1 AS k
             , c.id_category
             , j_.j
          FROM (SELECT @k := 0) k_
          JOIN (SELECT 1 AS j UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
               ) j_ 
          JOIN (
                SELECT cc.id_category
                  FROM cc cc
                 GROUP BY cc.id_category
                 ORDER BY cc.id_category
               ) c
         ORDER BY c.id_category, j_.j
       ) b
  LEFT
  JOIN (SELECT @i := IF(r.id_category = @prev_idcat,@i + 1,1) AS i
             , @prev_idcat := r.id_category AS id_category
             , r.tree
          FROM (SELECT @i := 0, @prev_idcat := NULL) i_ 
          JOIN (
                SELECT s.id_category, s.tree
                 FROM cc s
                 ORDER BY s.id_category, s.tree
               ) r
       ) q
    ON q.id_category = b.id_category AND q.i = b.j 
 ORDER BY b.id_category, b.j

要使它与您的行集一起工作，您可以用括在括号中的查询替换对我的cc表的每个引用。或者，您可以像我所做的那样创建一个名为cc的表，并将查询的结果插入其中。

有些人可能有一个更简单的SQL语句，可以可靠地生成相同的结果集。我很想学一种更简单的方法。