SQL geschachtelte Reihenfolge durch?

Antwort wurde bereits angenommen, aber ich dachte, ich würde teilen meine Gedanken dazu sowieso. ich habe versucht zu Sortieren, die wichtigsten Kategorien nach Ihren display_order Spalte als gut. hier ist meine Tabelle

mysql> select * from categories;
+-------------+---------------+-----------+---------------+
| category_id | category_name | parent_id | display_order |
+-------------+---------------+-----------+---------------+
|           1 | B             |         0 |             2 |
|           2 | C             |         0 |             3 |
|           3 | b2            |         1 |             2 |
|           4 | b1            |         1 |             1 |
|           5 | c3            |         2 |             3 |
|           6 | A             |         0 |             1 |
|           7 | c2            |         2 |             2 |
|           8 | b3            |         1 |             3 |
|           9 | a2            |         6 |             2 |
|          10 | a1            |         6 |             1 |
|          11 | c1            |         2 |             1 |
|          12 | a3            |         6 |             3 |
+-------------+---------------+-----------+---------------+
12 rows in set (0.00 sec)

wie Sie sehen, habe ich großen Wert auf die Beurteilung der Kategorien in keiner linearen Ordnung 🙂

meiner Abfrage:

SELECT
    sub_id AS category_id,
    sub_name AS category_name,
    sub_parent_id AS parent_id,
    main_order + sub_order AS display_order
FROM (
    SELECT
        c1.display_order + c1.display_order * (
            SELECT
                inner_c.display_order
            FROM
                categories AS inner_c
            WHERE
                inner_c.parent_id <> 0
            ORDER BY
                inner_c.display_order DESC
            LIMIT 1) AS main_order,
        c2.display_order AS sub_order,
        c2.category_name AS sub_name,
        c2.category_id AS sub_id,
        c2.parent_id AS sub_parent_id
    FROM
        categories AS c1
    JOIN
        categories AS c2
    ON
        c1.category_id = c2.parent_id
    WHERE
        c1.parent_id = 0
    ) AS renumbered
UNION ALL
SELECT
    category_id,
    category_name,
    parent_id,
    display_order + display_order * (
        SELECT
            inner_c.display_order
        FROM
            categories AS inner_c
        WHERE
            inner_c.parent_id <> 0
        ORDER BY
            inner_c.display_order DESC
        LIMIT 1) AS display_order
FROM
    categories
WHERE
    parent_id = 0
ORDER BY
    display_order;

Ich-wenn möglich-erstellen von SQL-inkrementell, nicht zuletzt, weil es gibt mir die Möglichkeit zu testen, wie ich gehe.

Die erste Sache, die wir tun können, ist identifizieren Sie die top-level-Kategorien:

 SELECT category_id   AS tl_cat_id,
        category_name AS tl_cat_name,
        display_order AS tl_disp_order
   FROM Categories
  WHERE parent_id = 0;

Nun müssen wir beitreten, die mit den Kategorien und Unterkategorien, um das Ergebnis zu erhalten:

SELECT t.tl_cat_id, t.cat_name, t.tl_disp_order, c.category_id, c.category_name,
       CASE WHEN c.parent_id = 0 THEN 0 ELSE c.display_order END AS disp_order
  FROM Categories AS c
  JOIN (SELECT category_id   AS tl_cat_id,
               category_name AS tl_cat_name,
               display_order AS tl_disp_order
          FROM Categories
         WHERE parent_id = 0) AS t
     ON c.tl_cat_id = t.parent_id OR (c.parent_id = 0 AND t.tl_cat_id = c.category_id)
  ORDER BY tl_disp_order, disp_order;

In der join-Bedingung ist ungewöhnlich, sollte aber funktionieren; es sammelt Zeilen, in denen die parent ID ist die gleiche wie die aktuelle Kategorie-ID, oder Reihen, in denen die parent-ID 0, aber die Kategorie-ID ist die gleiche. Die Bestellung ist dann fast schon trivial - außer, dass, wenn man es mit der sub-Kategorie bestellen, Sie möchten den übergeordneten Element an der Vorderseite der Liste. Der CASE-Ausdruck behandelt, die Zuordnung.