求助，如何建出这样一张表？ 在UNION 之后 ORDER？

[Sieg1978]

首先建立TABLE和数据：

CREATE TABLE EMPLOYEE
(
EmployeeId int,
ManagerId int,
EmployeeName varchar(10)
)
INSERT EMPLOYEE SELECT 1,2,'Peter'
INSERT EMPLOYEE SELECT 2,3,'David'
INSERT EMPLOYEE SELECT 3,NULL,'Jane'
INSERT EMPLOYEE SELECT 4,2,'Mary'
INSERT EMPLOYEE SELECT 5,2,'Henry'


运行
SELECT * FROM EMPLOYEE
结果如下：

EmployeeId ManagerId EmployeeName
----------- ----------- ------------
1 2 Peter
2 3 David
3 NULL Jane
4 2 Mary
5 2 Henry

现在要求得到这样一张表，注意是按Employee_Id来排序了的

Employee Manager
-------- --------
Peter David
David Jane
Jane No Manager
Mary David
Henry David


我写的查询：
SELECT EMPLOYEE.EmployeeName AS Employee, MANAGER.EmployeeName AS Manager
FROM EMPLOYEE,
EMPLOYEE AS MANAGER
WHERE EMPLOYEE.ManagerId = MANAGER.EmployeeId
UNION
SELECT EMPLOYEE.EmployeeName AS Employee, 'No Manager' AS Manager
FROM EMPLOYEE
WHERE EMPLOYEE.ManagerId IS NULL

运行之后
Employee Manager
---------- ----------
David Jane
Henry David
Jane No Manager
Mary David
Peter David

没有按照Employee_Id来排序。请问有什么办法能在Union之后，按Employee_Id来排序，而又不显示出Employee_Id么？

谢谢！

[Sieg1978]

如果有好的思路也不一定用UNION来做，谢谢！

[GoAwayZ]

CREATE TABLE EMPLOYEE
(
EmployeeId int,
ManagerId int,
EmployeeName varchar(10)
)
INSERT EMPLOYEE SELECT 1,2,'Peter'
INSERT EMPLOYEE SELECT 2,3,'David'
INSERT EMPLOYEE SELECT 3,NULL,'Jane'
INSERT EMPLOYEE SELECT 4,2,'Mary'
INSERT EMPLOYEE SELECT 5,2,'Henry'

select a.EmployeeName,isnull(b.EmployeeName,'No Manager') as Manageranme
from EMPLOYEE a left join EMPLOYEE b on a.ManagerId=b.EmployeeId

/*
EmployeeName Manageranme
------------ -----------
Peter        David
David        Jane
Jane         No Manager
Mary         David
Henry        David

(5 行受影响)

[cd731107]

select Employee,Manager
from 
(SELECT EMPLOYEE.EmployeeId,EMPLOYEE.EmployeeName AS Employee, MANAGER.EmployeeName AS Manager
FROM EMPLOYEE,  
  EMPLOYEE AS MANAGER
WHERE EMPLOYEE.ManagerId = MANAGER.EmployeeId
UNION
SELECT EMPLOYEE.EmployeeId,EMPLOYEE.EmployeeName AS Employee, 'No Manager' AS Manager
FROM EMPLOYEE
WHERE EMPLOYEE.ManagerId IS NULL) tb

SELECT * FROM EMPLOYEE  
运行之后

[cd731107]

[Quote=引用 2 楼 zy112429 的回复:]
SQL code
CREATE TABLE EMPLOYEE
(
EmployeeId int,
ManagerId int,
EmployeeName varchar(10)
)
INSERT EMPLOYEE SELECT 1,2,'Peter'
INSERT EMPLOYEE SELECT 2,3,'David'
INSERT EMPLOYEE SELECT 3,NULL,……
[/Quote]
准备再写一个左连接的，１楼已经写好了，就这个

[--小F--]

select
 a.EmployeeName,isnull(b.EmployeeName,'No Manager') as Manageranme
from
 EMPLOYEE a left join EMPLOYEE b 
on
 a.ManagerId=b.EmployeeId

[Sieg1978]

[Quote=引用 3 楼 cd731107 的回复:]

SQL code
select Employee,Manager
from
(SELECT EMPLOYEE.EmployeeId,EMPLOYEE.EmployeeName AS Employee, MANAGER.EmployeeName AS Manager
FROM EMPLOYEE,
EMPLOYEE AS MANAGER
WHERE EMPLOYEE.ManagerId ……
[/Quote]

如果要求按EMPLOYEE_ID降序排列，也就是形成这样一张表：
Employee Manager
-------- --------
Henry David
Mary David
Jane No Manager
David Jane
Peter David

用UNION可以做到么？

select Employee,Manager
from
(SELECT EMPLOYEE.EmployeeId,EMPLOYEE.EmployeeName AS Employee, MANAGER.EmployeeName AS Manager
FROM EMPLOYEE,
EMPLOYEE AS MANAGER
WHERE EMPLOYEE.ManagerId = MANAGER.EmployeeId
UNION
SELECT EMPLOYEE.EmployeeId,EMPLOYEE.EmployeeName AS Employee, 'No Manager' AS Manager
FROM EMPLOYEE
WHERE EMPLOYEE.ManagerId IS NULL
ORDER BY EMPLOYEE.EmployeeId DESC) tb
会出错。

LEFT JOIN可以直接SORT BY没有被SELECT的Variable还是很有优势啊。

[cd731107]

把ORDER BY EMPLOYEE.EmployeeId DESC放在外面，表名要去掉，如下：
select Employee,Manager
from  
(SELECT EMPLOYEE.EmployeeId,EMPLOYEE.EmployeeName AS Employee, MANAGER.EmployeeName AS Manager
FROM EMPLOYEE,   
  EMPLOYEE AS MANAGER
WHERE EMPLOYEE.ManagerId = MANAGER.EmployeeId
UNION
SELECT EMPLOYEE.EmployeeId,EMPLOYEE.EmployeeName AS Employee, 'No Manager' AS Manager
FROM EMPLOYEE
WHERE EMPLOYEE.ManagerId IS NULL
) tb ORDER BY EmployeeId DESC

[Sieg1978]

谢谢大家的解答，真的学到不少知识！