看这个问题。它与您的相似。我在那里发布了您可能需要的查询。
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
