这样一个查询语句如何写啊

2012-11-03

【求助】这样一个查询语句怎么写啊？？先有如下一张表，看源代码：SQL codeUSE MyDBGOIF OBJECT_ID(tb) IS NO

【求助】这样一个查询语句怎么写啊？？
先有如下一张表，看源代码：

SQL code

USE MyDB;GOIF OBJECT_ID('tb') IS NOT NULLDROP TABLE tb;GOCREATE 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    QXfF1a    NULLF1b    NULLF1c    NULLF2a    F1aF2b    F1bF2c    F1cF3a    F2aF3b    F2aF3c    F2bF3d    F2b*/

其中字段QXf是指的父权限也就是上级权限，希望写一条查询语句得到如下表：

SQL code

/*一类权限    二类权限    三类权限F1a    F2a    F3aF1a    F2a    F3bF1b    F2b    F3cF1b    F2b    F3dF1c    F2c    NULL*/

高手们来帮帮忙啊？？小弟在此谢谢了。。

[解决办法]

SQL code

select tb.QX, tb1.QX, tb2.QXfrom tbleft outer join tb tb1 on tb1.QXf =  tb.QXleft outer join tb tb2 on tb2.QXf =  tb1.QXwhere 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 codeSELECT  tb.QX , tb1.QX , tb2.QXFROM    tbLEFT JOIN tb tb1ON      tb1.QXf = tb.QXLEFT JOIN tb tb2ON      tb2.QXf = tb1.QXWHERE   tb.QXf IS NULL
[解决办法]
SQL codeCREATE 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)) cleft join tb d on c.QX=d.QXfleft join tb e on d.QX=e.QXf/*一类权限   二类权限   三类权限---------- ---------- ----------F1a        F2a        F3aF1a        F2a        F3bF1b        F2b        F3cF1b        F2b        F3dF1c        F2c        NULL(5 row(s) affected)*/

热点排行

Bad Request.

SQL Server

这样一个查询语句如何写啊