在sql表中插入另一个层次结构

Question

我用TSQL写了以下代码:

SELECT 
    x.PARENT, 
    x.ELEMENT,    
    MAX(CASE SPRAS WHEN 'D' THEN MAKTX  ELSE '' END ) DCol,     
    MAX(CASE SPRAS WHEN 'E' THEN MAKTX  ELSE '' END ) ECol,  
    MAX(CASE SPRAS WHEN 'F' THEN MAKTX  ELSE '' END ) FCol  
FROM 
  (
    SELECT  
        a.PARENT, 
        (CASE WHEN a.ELEMENT = a.PARENT THEN '' ELSE a.ELEMENT END) as ELEMENT, 
        b.MATNR, b.SPRAS, b.MAKTX       
    FROM 
      (
        SELECT DISTINCT 
            'ALL' AS PARENT, 
            LEFT(PRODH, 1) AS ELEMENT 
        FROM sap_T179 
        WHERE PRODH != ''             
        UNION 
        SELECT DISTINCT 
            LEFT(PRODH, 1),
            LEFT(PRODH, 2) AS ELEMENT 
        FROM sap_T179 
        WHERE PRODH != ''          
        UNION
        SELECT DISTINCT 
            LEFT(PRODH, 2), 
            LEFT(PRODH, 3) AS ELEMENT 
        FROM sap_T179 
        WHERE PRODH != ''             
        UNION 
        SELECT DISTINCT 
            LEFT(PRODH, 3),
            LEFT(PRODH, 4) AS ELEMENT 
        FROM sap_T179 
        WHERE PRODH != ''          
        UNION
        SELECT DISTINCT 
            LEFT(PRODH, 4), 
            LEFT(PRODH, 5) AS ELEMENT 
        FROM sap_T179 
        WHERE PRODH != ''             
        UNION 
        SELECT DISTINCT 
            LEFT(PRODH, 5),
            LEFT(PRODH, 6) AS ELEMENT 
        FROM sap_T179 
        WHERE PRODH != ''          
        UNION
        SELECT DISTINCT 
            LEFT(PRODH, 6), 
            LEFT(PRODH, 7) AS ELEMENT 
        FROM sap_T179 
        WHERE PRODH != ''             
        UNION 
        SELECT 
            'ALL', 
            'Without Element'
      ) AS a          
     LEFT JOIN 
    sap_MARAV AS b ON 
        b.PRDHA = a.ELEMENT
  ) AS x 
GROUP BY x.PARENT, x.ELEMENT 
ORDER BY 1,2

现在我要在section

下创建另一个层次结构

UNION
SELECT DISTINCT 
    LEFT(PRODH, 6), 
    LEFT(PRODH, 7) AS ELEMENT 
FROM sap_T179 
WHERE PRODH != '' 

就像

UNION 
SELECT DISTINCT  
    LEFT(PRODH, 7), 
    MatNR AS ELEMENT 
FROM sap_T179 
WHERE PRODH != ''

我试过连接和很多其他的东西，但它不工作。

Answer 1

根据您的评论，您希望从两个不同的表中检索数据作为另一个UNION语句。您声明两者之间的关系是共享相同的PRODH列。

在此基础上，尝试以下操作(使用您的表名编辑):

UNION 
SELECT DISTINCT  
    LEFT(t1.PRODH, 7), 
    t2.MatNR AS ELEMENT 
FROM 
    sap_T179 t1
     INNER JOIN 
    OtherTable t2 ON -- rename this!
        t1.PRODH = t2.PRODH
WHERE T1.PRODH != ''