同表递归SQL查询求助：从设计不完善数据库提取关联数据（无法创建视图）

No problem at all! Since you can't create views and want to avoid the hassle of Excel's VLOOKUP, we can use multiple self-joins on tableB directly in a single SQL query to pull all the required descriptions. Here's a straightforward solution that will give you exactly the output you need:

The SQL Query #

SELECT
    a.ID,
    a.NAME,
    a.AGE,
    b_unit.DESC AS UNIT_DESC,
    b_lv1.DESC AS LV1_DESC,
    b_lv2.DESC AS LV2_DESC
FROM tableA a
JOIN tableB b_unit ON a.UNIT_ID = b_unit.ID
JOIN tableB b_lv1 ON b_unit.ID_LV1 = b_lv1.ID
JOIN tableB b_lv2 ON b_unit.ID_LV2 = b_lv2.ID;

How This Works #

Let's break down the logic to make it easy to follow:

• We start with tableA (aliased as a) to grab the core user data: ID, NAME, and AGE.
• The first join with tableB (aliased as b_unit) matches a.UNIT_ID to b_unit.ID to get the main unit's description (UNIT_DESC).
• The second join (with b_lv1) takes b_unit.ID_LV1 and matches it to b_lv1.ID to pull in the level 1 unit description.
• The third join (with b_lv2) uses b_unit.ID_LV2 to match b_lv2.ID and retrieve the level 2 unit description.

Since your sample data has no missing values in the hierarchical fields, using JOIN (inner join) works perfectly. If you ever encounter cases where ID_LV1 or ID_LV2 might be null, you could switch to LEFT JOIN to keep all rows from tableA even if some level descriptions are missing.

When you run this query, it will return exactly the result set you specified:

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