需编写SQL查询语句对MySQL表indexlistapp树形层级条目分组

Got it, let's break down how to group those nested tree entries in your table. The Tree column defines the hierarchy level (1 = root, 2 = child, 3 = grandchild), and Seq keeps everything in the right order. Here's a straightforward approach to organize these entries into their respective groups:

Step 1: Assign Group IDs to Related Entries #

First, we need a way to link each root entry (Tree=1) with all its subsequent child entries. Using MySQL 8.0+ window functions, we can create a group_id that increments every time we hit a new root entry:

SELECT
  Index,
  Seq,
  Tree,
  App,
  Idx,
  SUM(CASE WHEN Tree = 1 THEN 1 ELSE 0 END) OVER (ORDER BY Seq) AS group_id
FROM indexlistapp;

This query will assign the same group_id to all entries under the same root. For example, the 1.8L turbo gas entry and its two VIN children will share group_id 1, while the 1.9L turbo diesel and its descendants get group_id 2.

Step 2: Display the Hierarchy Clearly #

If you want to visualize the nested structure, use the group ID to format entries with indentation matching their tree level:

WITH grouped_entries AS (
  SELECT
    Index,
    Seq,
    Tree,
    App,
    Idx,
    SUM(CASE WHEN Tree = 1 THEN 1 ELSE 0 END) OVER (ORDER BY Seq) AS group_id
  FROM indexlistapp
)
SELECT
  CONCAT(
    REPEAT('  ', Tree - 1),
    App,
    IF(Idx IS NOT NULL AND Idx != '.NULL.', CONCAT(' (Idx: ', Idx, ')'), '')
  ) AS hierarchical_entry,
  group_id
FROM grouped_entries
ORDER BY group_id, Seq;

This will produce output like this (cleanly indented by hierarchy level):

1.8L（涡轮增压汽油） | 1
  VIN C（第5位，发动机ID AWD） (Idx: 300-76318B) | 1
  VIN D（第5位，发动机ID AWW） (Idx: 300-67718B) | 1
1.9L（涡轮增压柴油） | 2
  VIN F（第5位） | 2
    MT (Idx: 300-76119A) | 2
    AT (Idx: 300-76119B) | 2
  VIN P（第5位） | 2

Alternative: Aggregate Entries by Group #

If you prefer to collapse each group into a single row (e.g., for reporting), use GROUP_CONCAT to combine all entries in the group:

WITH grouped_entries AS (
  SELECT
    Index,
    Seq,
    Tree,
    App,
    Idx,
    SUM(CASE WHEN Tree = 1 THEN 1 ELSE 0 END) OVER (ORDER BY Seq) AS group_id
  FROM indexlistapp
)
SELECT
  group_id,
  GROUP_CONCAT(
    CONCAT(REPEAT('  ', Tree - 1), App, IF(Idx IS NOT NULL AND Idx != '.NULL.', CONCAT(' (', Idx, ')'), ''))
    ORDER BY Seq SEPARATOR '\n'
  ) AS full_group_hierarchy
FROM grouped_entries
GROUP BY group_id;

For Older MySQL Versions (Pre-8.0) #

If you don't have window functions, use a correlated subquery to assign group IDs:

SELECT
  *,
  (SELECT COUNT(*) FROM indexlistapp i2 WHERE i2.Seq <= i1.Seq AND i2.Tree = 1) AS group_id
FROM indexlistapp i1;

You can tweak the formatting (indentation, concatenation) to match exactly what you need for your use case.

内容的提问来源于stack exchange，提问作者Ronnie Tanner