USE MyDB;
GO
IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb;
GO
CREATE TABLE tb
( QX VARCHAR(10) NOT NULL ,--详细权限
  QXf VARCHAR(10) NULL,    --父权限
  CONSTRAINT pk_tb2_QX PRIMARY KEY (QX)
);
GO
--插入数据
INSERT INTO tb VALUES('F1a',NULL);
INSERT INTO tb VALUES('F1b',NULL);
INSERT INTO tb VALUES('F1c',NULL);
INSERT INTO tb VALUES('F2a','F1a');
INSERT INTO tb VALUES('F2b','F1b');
INSERT INTO tb VALUES('F2c','F1c');
INSERT INTO tb VALUES('F3a','F2a');
INSERT INTO tb VALUES('F3b','F2a');
INSERT INTO tb VALUES('F3c','F2b');
INSERT INTO tb VALUES('F3d','F2b');
--查询结果
SELECT * FROM dbo.tb
/*
QX    QXf
F1a    NULL
F1b    NULL
F1c    NULL
F2a    F1a
F2b    F1b
F2c    F1c
F3a    F2a
F3b    F2a
F3c    F2b
F3d    F2b
*/

/*
一类权限    二类权限    三类权限
F1a    F2a    F3a
F1a    F2a    F3b
F1b    F2b    F3c
F1b    F2b    F3d
F1c    F2c    NULL

*/

select tb.QX, tb1.QX, tb2.QX
from tb
left outer join tb tb1 on tb1.QXf =  tb.QX
left outer join tb tb2 on tb2.QXf =  tb1.QX
where tb.QXf is null

------解决方案--------------------
探讨


SQL code

select tb.QX, tb1.QX, tb2.QX
from tb
left outer join tb tb1 on tb1.QXf =  tb.QX
left outer join tb tb2 on tb2.QXf =  tb1.QX
where tb.QXf is null


------解决方案--------------------
SQL code
SELECT  tb.QX , tb1.QX , tb2.QX
FROM    tb
LEFT JOIN tb tb1
ON      tb1.QXf = tb.QX
LEFT JOIN tb tb2
ON      tb2.QXf = tb1.QX
WHERE   tb.QXf IS NULL

------解决方案--------------------
SQL code

CREATE TABLE tb
( QX VARCHAR(10) NOT NULL,
  QXf VARCHAR(10) NULL,
  CONSTRAINT pk_tb2_QX PRIMARY KEY (QX)
)

INSERT INTO tb VALUES('F1a',NULL);
INSERT INTO tb VALUES('F1b',NULL);
INSERT INTO tb VALUES('F1c',NULL);
INSERT INTO tb VALUES('F2a','F1a');
INSERT INTO tb VALUES('F2b','F1b');
INSERT INTO tb VALUES('F2c','F1c');
INSERT INTO tb VALUES('F3a','F2a');
INSERT INTO tb VALUES('F3b','F2a');
INSERT INTO tb VALUES('F3c','F2b');
INSERT INTO tb VALUES('F3d','F2b');


select c.QX '一类权限',
       d.QX '二类权限',
       e.QX '三类权限'
from
(select a.QX from tb a
 where not exists
 (select 1 from tb b 
 where b.QX=a.QXf)) c
left join tb d on c.QX=d.QXf
left join tb e on d.QX=e.QXf

/*
一类权限   二类权限   三类权限
---------- ---------- ----------
F1a        F2a        F3a
F1a        F2a        F3b
F1b        F2b        F3c
F1b        F2b        F3d
F1c        F2c        NULL

(5 row(s) affected)
*/