MySQL嵌套集-如何查找节点的父级？

看这个问题。它与您的相似。我在那里发布了您可能需要的查询。

SELECt title, (SELECT TOP 1 title FROM tree t2 WHERe t2.lft < t1.lft AND t2.rgt > t1.rgt    ORDER BY t2.rgt-t1.rgt ASC) AS parentFROM tree t1ORDER BY rgt-lft DESC

希望您有需要。

对于下表：

+-------------+----------------------+-----+-----+| category_id | name      | lft | rgt |+-------------+----------------------+-----+-----+|1 | ELECTRonICS          |   1 |  20 ||2 | TELEVISIONS          |   2 |   9 ||3 | TUBE      |   3 |   4 ||4 | LCD       |   5 |   6 ||5 | PLASMA    |   7 |   8 ||6 | PORTABLE ELECTRonICS |  10 |  19 ||7 | MP3 PLAYERS          |  11 |  14 ||8 | FLASH     |  12 |  13 ||9 | CD PLAYERS|  15 |  16 ||          10 | 2 WAY RADIOS         |  17 |  18 |

它产生输出：

title     | parent----------------------------------------------ELECTRonICS          | NULLPORTABLE ELECTRonICS | ELECTRonICSTELEVISIONS          | ELECTRONICSMP3 PLAYERS          | PORTABLE ELECTRonICSFLASH     | MP3 PLAYERSCD PLAYERS| PORTABLE ELECTRONICS2 WAY RADIOS         | PORTABLE ELECTRonICSTUBE      | TELEVISIonSLCD       | TELEVISIonSPLASMA    | TELEVISIONS