怎么查出一张表里面的员工和上级的姓名

1、如果一张表里面有编号，有姓名，有上级员工的编号，以及职位，要查出员工的姓名和上级领导的姓名，怎么查呢？

CREATE TABLE coo

(

NO VARCHAR(6) PRIMARY KEY,

NAME VARCHAR(20) NOT NULL,

fno VARCHAR(6),

manager VARCHAR(10) not null

)

GO

INSERT INTO coo

SELECT '0001','kk','0002','文员经理' UNION

SELECT '0002','ppom','0003','办公室主任' UNION

SELECT '0003','joy',NULL,'经理' UNION

SELECT '0004','yoyo','0001','文员'

GO

SELECT * FROM coo

2、SELECT 员工.NAME AS 员工姓名,经理.name AS 上级领导姓名 FROM coo as 员工,coo as  经理

WHERE 员工.fno=经理.NO