mysql - SQL 按父子顺序排序

Question

基本上我在这里的查询中需要帮助。我想按正确的顺序排列，即孩子必须在父母的名字下并按 AZ 顺序排列。但是，如果我添加一个subChild下孩子 ( Split 1) 似乎顺序是错误的。它应该在Room Rose.

p/s : 一个 subChild 也可以创建另一个 subChild

这里我提供一个演示

感谢您帮助我正确订购此商品？

SELECT A.venueID
     , B.mainVenueID
     , A.venueName 
  FROM tblAdmVenue A 
  LEFT 
  JOIN tblAdmVenueLink B
    ON A.venueID = B.subVenueID
 ORDER   
    BY COALESCE(B.mainVenueID, A.venueID)
     , B.mainVenueID IS NOT NULL
     , A.venueID

我希望它返回类似这样的订单。

venueName
--------------
Banquet
Big Room
-Room Daisy
-Room Rose
  -Split 1
Hall
-Meeting Room WP

似乎这种递归方法也不起作用

WITH venue_ctg AS (
  SELECT A.venueID, A.venueName, B.mainVenueID 
  FROM tblAdmVenue A LEFT JOIN tblAdmVenueLink B
  ON A.venueID = B.subVenueID
  WHERE B.mainVenueID IS NULL 

  UNION ALL

  SELECT A.venueID, A.venueName, B.mainVenueID 
  FROM tblAdmVenue A LEFT JOIN tblAdmVenueLink B
  ON A.venueID = B.subVenueID
  WHERE B.mainVenueID IS NOT NULL
)
SELECT *
FROM venue_ctg ORDER BY venueName

输出给定

标签： mysqlsqlrecursive-query

Answer 1

对于您的数据，您可以使用它：要正确显示它，您可以使用逗号之类的分隔符，并拆分返回的数据，并检查层次结构

-- schema
CREATE TABLE tblAdmVenue (
    venueID VARCHAR(225) NOT NULL,
    venueName VARCHAR(225) NOT NULL,
    PRIMARY KEY(venueID)
);

CREATE TABLE tblAdmVenueLink (
    venueLinkID VARCHAR(225) NOT NULL,
    mainVenueID VARCHAR(225) NOT NULL,
    subVenueID VARCHAR(225) NOT NULL,
    PRIMARY KEY(venueLinkID)
    -- FOREIGN KEY (DepartmentId) REFERENCES Departments(Id)
);

-- data
INSERT INTO tblAdmVenue (venueID, venueName)
VALUES ('LA43', 'Big Room'), ('LA44', 'Hall'),
       ('LA45', 'Room Daisy'), ('LA46', 'Room Rose'),
       ('LA47', 'Banquet'), ('LA48', 'Split 1'),
       ('LA49', 'Meeting Room WP');

INSERT INTO tblAdmVenueLink (venueLinkID, mainVenueID, subVenueID)
VALUES ('1', 'LA43', 'LA45'), ('2', 'LA43', 'LA46'),
       ('3', 'LA46', 'LA48'), ('4', 'LA44', 'LA49');

✓

✓

✓

✓

with recursive cte (subVenueID, mainVenueID,level) as (
  select     subVenueID,
             mainVenueID, 1 as level
  from       tblAdmVenueLink
  union
  select     p.subVenueID,
             cte.mainVenueID,
             cte.level+1
  from       tblAdmVenueLink p
  inner join cte
          on p.mainVenueID = cte.subVenueID
)
select 
   
    CONCAT(GROUP_CONCAT(b.venueName  ORDER BY level DESC SEPARATOR  '-->') ,'-->',a.venueName)
from cte c 
LEFT JOIN tblAdmVenue a ON a.venueID = c.subVenueID 
LEFT JOIN tblAdmVenue b ON b.venueID = c.mainVenueID
GROUP BY subVenueID;

| CONCAT(GROUP_CONCAT(b.venueName ORDER BY level DESC SEPARATOR '-->') ,'-->',a.venueName) |
| :------------------------------------------------ ---------------------------------------------------- |
| 大房间-->房间雏菊 |
| 大房间-->房间玫瑰 |
| 大房间-->玫瑰房-->Split 1 |
| 大厅-->会议室 WP |

db<>在这里摆弄