MySQL查询: multi level category on the same table

Question

地球人美好的一天，

这是我的表结构

acctCode| refAcctCode | acctName
------    ----------    --------
 AC001                    SALES
 AC002                    AR
 AC003     AC001          FURNITURE
 AC004     AC003          OFFICE FURNITURE
 AC005     AC002          INVOICE
 AC006     AC001          UTILITIES

预期输出为

SALES
    FURNITURE
        OFFICE FURNITURE
    UTILITIES
AR
    INVOICE

但是它不仅限于第 3 级，还限于第 n 级

例如:

Parent1
  Child1
    child1.1
      child1.1.1
        child1.1.1.1
  child2
  child3
  child3

parent2
  child1
  child2
  child3

给定表结构格式，我如何查询以获得该输出？

谢谢

Answer 1

这是一个基于用户定义过程的解决方案:

CREATE PROCEDURE list()
BEGIN
 DROP TABLE IF EXISTS tmp; -- for collecting the records
 CREATE TABLE tmp (id varchar(1024), name varchar(128));
 SET @i=-5; SET @s=0;      -- @i: offset for id comparison
                           -- @s: space padding for name column
 INSERT INTO tmp           -- fill in entries without refAcctCode
 SELECT a.acctCode, a.acctName FROM tbl a WHERE a.refAcctCode is null;

                           -- do as often as necessary ...    
 WHILE ROW_COUNT() > 0 DO 
  SET @i=@i+6; SET @s=@s+2;-- increment variables
  INSERT INTO tmp
  SELECT CONCAT(id,' ',a.acctCode), CONCAT(SPACE(@s),a.acctName) 
  FROM tbl a INNER JOIN tmp b ON SUBSTR(id,@i)=a.refAcctCode;
 END WHILE;

 SELECT * FROM tmp ORDER BY id;  -- list the consolidated temp. table
END;

通过执行

CALL list()

您将得到以下输出:

    id                name
------------------------------------------
1   AC001             SALES
2   AC001 AC003         FURNITURE
3   AC001 AC003 AC004     OFFICE FURNITURE
4   AC001 AC006         UTILITIES
5   AC002             AR
6   AC002 AC005         INVOICE

您可以在这里找到一个有效的演示:http://rextester.com/ZRXN57872

我使用空格作为 accName 列的前缀。这样可以更轻松地查看结果列表中的类别级别。请随意尝试该过程以使其满足您的特定需求。