Sql 如何根据同一表中某一列中的值重新排列行(仅输出它们)(重新排序)

我有下表:

CREATE TABLE `table` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `categoryName` text DEFAULT NULL,
  `freeText` tinyint(1) DEFAULT 0,
  `multiple` tinyint(1) DEFAULT 0,
  `activecat` tinyint(1) DEFAULT 1,
  `insertAfter` int(11) DEFAULT NULL,
  PRIMARY KEY (`ID`)
);

内容如下:

INSERT INTO `table` VALUES
(1,'gartenname',1,0,1,0),
(2,'plz',1,0,1,0),
(3,'ort',1,0,1,0),
(4,'str',1,0,1,0),
(5,'nr',1,0,1,0),
(6,'land',0,0,1,0),
(7,'lat',1,0,1,0),
(8,'long',1,0,1,0),
(9,'lagebeschreibung',1,0,1,0),
(10,'gartenwebsite',1,0,1,0),
(11,'start',1,0,1,0),
(12,'beschreibung',1,0,1,0),
(13,'eigentum',0,0,1,0),
(14,'grundflaeche',1,0,1,**23**),
(15,'gartenflaeche',1,0,1,0),
(16,'beetflaeche',1,0,0,0),
(17,'zweck',0,0,1,0),
(18,'zweck2',0,0,1,**12**),
(19,'zweck3',1,0,0,0),
(20,'einordnung',0,0,1,**7**),
(21,'zweige',0,1,0,0),
(22,'motivation',0,1,1,0),
(23,'nutzen',0,1,0,0),
(24,'initiative',0,1,1,0),
(25,'schwerpunkt',0,1,1,0),
(26,'status',0,0,1,0),
(27,'weltbild',0,0,1,0),
(28,'standort',0,1,1,0),
(29,'boden',0,0,1,0),
(30,'spezifisch',0,0,0,0), ... )

我需要一个SQL查询，它输出完整的数据，同时在适当的位置插入带有"InsertAfter"！=0的行.

预期的输出将是:

(1,'gartenname',1,0,1,0),
(2,'plz',1,0,1,0),
(3,'ort',1,0,1,0),
(4,'str',1,0,1,0),
(5,'nr',1,0,1,0),
(6,'land',0,0,1,0),
(7,'lat',1,0,1,0),
(20,'einordnung',0,0,1,**7**),
(8,'long',1,0,1,0),
(9,'lagebeschreibung',1,0,1,0),
(10,'gartenwebsite',1,0,1,0),
(11,'start',1,0,1,0),
(12,'beschreibung',1,0,1,0),
(18,'zweck2',0,0,1,**12**),
(13,'eigentum',0,0,1,0),
(15,'gartenflaeche',1,0,1,0),
(16,'beetflaeche',1,0,0,0),
(17,'zweck',0,0,1,0),
(19,'zweck3',1,0,0,0),
(21,'zweige',0,1,0,0),
(22,'motivation',0,1,1,0),
(23,'nutzen',0,1,0,0),
(14,'grundflaeche',1,0,1,**23**),
(24,'initiative',0,1,1,0),
(25,'schwerpunkt',0,1,1,0),
(26,'status',0,0,1,0),
(27,'weltbild',0,0,1,0),
(28,'standort',0,1,1,0),
(29,'boden',0,0,1,0),
(30,'spezifisch',0,0,0,0), ... )

我怎样才能做到这一点呢？

我试过这个:

CREATE TEMPORARY TABLE temp LIKE table;

INSERT INTO temp SELECT * FROM table

;WITH cte (ID, insertAfter, categoryName, freeText, multiple, activecat, sort) AS
(
  /* query #1 : pull only parent records */
  select ID, insertAfter, categoryName, freeText, multiple, activecat,
         cast(right('0000' + cast(row_number() over (order by ID) as varchar(5)), 5) as varchar(1024))
  from   temp
  where  ID = insertAfter                           /* pull just the parent records */

  union all

  /* query #2 : add children records */
  select t.ID, t.insertAfter, t.categoryName, t.freeText, t.multiple, t.activecat,
         cast(c.sort + right('0000' + cast(row_number() over (order by t.ID) as varchar(5)), 5) as varchar(1024))
  from   cte c                                   /* include data from 1st query */
         inner join temp t on c.ID = t.insertAfter/* only pull children of the parent records returned in query #1 */
  where  t.ID <> t.insertAfter                      /* a record cannot be a child of itself, prevents infinite recursion */
)
select *
from cte
order by sort;

drop table temp

从这里改写:Order By SQL Query based on Value another Column，甚至是来自那里的 comments 的第二个解决方案.

第一个解决方案:#1146-Tabelle‘wg_ULTI_DB.cte’existiert Nicht-Table不存在

注释中的解决方案只输出带有INSERTAFTER！=0的行.

我试着用php解决这个问题:

        $sql_joined = "SELECT * FROM table_a a INNER JOIN table_b b ON b.insertAfter= a.ID ORDER BY a.ID, b.insertAfter";

        $content = $wpdb->get_results($sql_joined, ARRAY_A);

        function custom_splice(&$ar, $a, $b)
        {
            $out = array_splice($ar, $a, 1);
            array_splice($ar, $b, 0, $out);
        }

        $i = 0;

        foreach ($content as $arr) {
            if ($arr['insertAfter'] != 0) {
                custom_splice($content, $i, $arr['insertAfter']);
            }
            $i++;
        }