MySQL inner join 从同一张表中排序父子孙

Question

MySQL 数据库包含国家、城镇和城镇内的区域，都在“mailshot”表中。我想按降序粒度的顺序返回整个集合，使用和内部连接。我实际上想向用户显示一个下拉列表，供他们选择国家或城镇或城镇内的地区。

数据如下所示:

mailshot_id   mailshot_parent   mailshot_name   mailshot_level 49              0               England         056              0               Scotland        0140             49              London          1149             49              York            1191             56              Glasgow         1300             140             Wimbledon       2310             140             Westminster     2 493             56              Edinburgh       1

我希望它像这样输出:

mailshot_id   mailshot_parent   mailshot_name   mailshot_level 49              0               England         0149             49              York            1140             49              London          1300             140             Wimbledon       2310             140             Westminster     2 56              0               Scotland        0191             56              Glasgow         1493             56              Edinburgh       1

我几乎已经明白了:

 SELECT    p.mailshot_id as p_id,     p.mailshot_name as p_name,     p.mailshot_level as p_level,    p.mailshot_parent as p_parent,     c.mailshot_id as c_id,     c.mailshot_parent as c_parent,     c.mailshot_level as c_level,     c.mailshot_name as c_name,     case         WHEN p.mailshot_parent  = 0 THEN         p.mailshot_id         ELSE         p.mailshot_parent     END AS calcOrder FROM    mailshot p LEFT JOIN mailshot c    ON p.mailshot_id = c.mailshot_parent ORDER BY   calcOrder , p_id "

但它没有将孙记录(第 2 级)分组到子记录(第 1 级)附近 我认为“案例”部分一定是错误的，我需要根据级别在 mailshot_id 和 parent_id 之间建立某种关系。但我想不出来。

有什么建议吗？提前致谢。

Answer 1

不幸的是，MySQL 不支持分层查询(没有 START WITH...CONNECT BY 或 CTE 等价物)。因此，您需要以艰难而丑陋的方式来做到这一点。

以下将适用于您的 3 个级别，但如果您需要在树中有更多的深度，它会变得非常麻烦。这是 Fiddle

SELECT  C.MAILSHOT_ID
        ,C.MAILSHOT_PARENT
        ,C.MAILSHOT_NAME
        ,C.MAILSHOT_LEVEL
        ,CASE   WHEN C.MAILSHOT_LEVEL = 0 
                THEN CAST(C.MAILSHOT_ID AS CHAR(4))
                WHEN C.MAILSHOT_LEVEL = 1 
                THEN CONCAT(CAST(C.MAILSHOT_PARENT AS CHAR(4)),"..",CAST(C.MAILSHOT_ID AS CHAR(4)))
                ELSE CONCAT(CAST(P.MAILSHOT_PARENT AS CHAR(4)),"..",CAST(C.MAILSHOT_PARENT AS CHAR(4)),"..",CAST(C.MAILSHOT_ID AS CHAR(4)))
        END AS SORT_ORDER    
FROM    MAILSHOT C
LEFT OUTER JOIN
        MAILSHOT P
ON      P.MAILSHOT_ID = C.MAILSHOT_PARENT       
ORDER BY CASE   WHEN C.MAILSHOT_LEVEL = 0 
                THEN CAST(C.MAILSHOT_ID AS CHAR(4))
                WHEN C.MAILSHOT_LEVEL = 1 
                THEN CONCAT(CAST(C.MAILSHOT_PARENT AS CHAR(4)),"..",CAST(C.MAILSHOT_ID AS CHAR(4)))
                ELSE CONCAT(CAST(P.MAILSHOT_PARENT AS CHAR(4)),"..",CAST(C.MAILSHOT_PARENT AS CHAR(4)),"..",CAST(C.MAILSHOT_ID AS CHAR(4)))
        END