复杂的SQL自连接

Question

我有一个存储员工信息的表（EmpInfo）。以下是字段：

EMPLID - varchar(11) (PK)
名称 - varchar(50)
POSITION_NBR - varchar(8)
CREPORTS5 - char(8)
CREPORTS6 - char(8)
CREPORTS7 - char(8)

CREPORTS 字段引用命令链中的 POSITION_NBR 字段。

我正在尝试编写一个 SQL 查询，在其中可以返回 CREPORTS 的名称而不是 POSITION_NBR

层次结构：

Jack Jones - CEO
约翰·史密斯 - 经理
      莎拉测试 - 主管
        Suzy Blerg - 工人
        Mike Martin - Worker

-------------------------------------------------------------------------------- 
|  EMPLID  |     NAME      | POSITION_NBR  | CREPORTS5 | CREPORTS6 | CREPORTS7 | 
-------------------------------------------------------------------------------- 
|   1234   |  John Smith   |    11111      |   22222   |   NULL    |   NULL    |
-------------------------------------------------------------------------------- 
|   2345   |  Jack Jones   |    22222      |   NULL    |   NULL    |   NULL    |
--------------------------------------------------------------------------------  
|   3456   |  Sarah Test   |    33333      |   22222   |   11111   |   NULL    |
--------------------------------------------------------------------------------  
|   4567   |  Suzy Blerg   |    44444      |   22222   |   11111   |   33333   |
-------------------------------------------------------------------------------- 
|   5678   |  Mike Martin  |    55555      |   22222   |   11111   |   33333   |
--------------------------------------------------------------------------------

我不是一个 SQL 专家，所以我的第一次尝试是这样的：

SELECT     EmpInfo.EMPLID    
               EmpInfo.CREPORTS5,
                  CAST
                    ((SELECT     TOP (1) NAME
                      FROM         EmpInfo AS subInfo
                      WHERE     (subInfo.POSITION_NBR = EmpInfo.CREPORTS5)) AS varchar(50)) AS CREPORTS5_NAME
FROM EmpInfo

这个查询有效，但速度很慢当针对 300k 条记录运行时。

Answer 1

不确定，但我认为这就是你想要的；只需多次对表进行自连接：

DECLARE @t TABLE
    (
      EMPLID VARCHAR(11)
    , NAME VARCHAR(50)
    , POSITION_NBR VARCHAR(8)
    , CREPORTS5 CHAR(8)
    , CREPORTS6 CHAR(8)
    , CREPORTS7 CHAR(8)
    )


INSERT  INTO @t
        ( EMPLID, NAME, POSITION_NBR, CREPORTS5, CREPORTS6, CREPORTS7 )
VALUES  ( '1234', 'John Smith', '11111', '22222', NULL, NULL ),
        ( '2345', 'Jack Jones', '22222', NULL, NULL, NULL ),
        ( '3456', 'Sarah Test', '33333', '22222', '11111', NULL ) ,
        ( '4567', 'Suzy Blerg', '44444', '22222', '11111', '33333' ),
        ( '5678', 'Mike Martin', '55555', '22222', '11111', '33333' ) 


SELECT  t.EMPLID
      , t.Name
      , t2.NAME AS CDR5Name
      , t3.NAME AS CDR6Name
      , t4.NAME AS CDR7Name
FROM    @t t
        LEFT JOIN @t t2 ON t.CREPORTS5 = t2.POSITION_NBR
        LEFT JOIN @t t3 ON t.CREPORTS6 = t3.POSITION_NBR
        LEFT JOIN @t t4 ON t.CREPORTS7 = t4.POSITION_NBR

此外，您可能希望更改数据类型以匹配（对数字数据使用数字类型等）。如果您可以发布示例脚本而不是描述，那就太好了:)