表内连接的SQL语句,该怎么解决

表内连接的SQL语句
ID   USERNAME     UNITNAME    CREATEUSERNAME
1         ZH                          JD1              301
2        LS                            JD2             302
3        301                        JD3               ZH
4        302                       JD4                LS


我想到得 
ID    USERNAME         UNITNAME           CREATEER
1         ZH                          JD1                       301-JD3
2        LS                            JD2                      302-JD4
3        301                        JD3                        ZH-JD1
4        302                       JD4                         LS-JD2    
------解决方案--------------------

WITH a1 (ID,USERNAME,UNITNAME,CREATEUSERNAME) AS
(
SELECT 1,'ZH','JD1','301' UNION ALL
SELECT 2,'LS','JD2','302' UNION ALL
SELECT 3,'301','JD3','ZH' UNION ALL
SELECT 4,'302','JD4','LS'
)
SELECT ID,USERNAME,UNITNAME,(SELECT USERNAME+'-'+UNITNAME FROM a1 WHERE CREATEUSERNAME=a.USERNAME) CREATEER
FROM a1 a

------解决方案--------------------

引用:

WITH a1 (ID,USERNAME,UNITNAME,CREATEUSERNAME) AS
(
SELECT 1,'ZH','JD1','301' UNION ALL
SELECT 2,'LS','JD2','302' UNION ALL
SELECT 3,'301','JD3','ZH' UNION ALL
SELECT 4,'302','JD4','LS'
)
SELECT ID,USERNAME,UNITNAME,(SELECT USERNAME+'-'+UNITNAME FROM a1 WHERE CREATEUSERNAME=a.USERNAME) CREATEER
FROM a1 a

WITH a1 (ID,USERNAME,UNITNAME,CREATEUSERNAME) AS
(
SELECT 1,'ZH','JD1','301' UNION ALL
SELECT 2,'LS','JD2','302' UNION ALL
SELECT 3,'301','JD3','ZH' UNION ALL
SELECT 4,'302','JD4','LS'
)
SELECT ID,USERNAME,UNITNAME,(SELECT top 1  USERNAME+'-'+UNITNAME FROM a1 WHERE CREATEUSERNAME=a.USERNAME) CREATEER
FROM a1 a