刚接触MSSQL，现在想做个view不会做，求帮助,该如何处理

刚接触MSSQL，现在想做个view不会做，求帮助
现在有个这样的表
ID aid name parentID iddegree
1 a1 1 0 0
2 a2 2 a1 1
3 a3 3 a1 1
4 a4 4 a1 1
5 a5 5 a2 2
6 a6 6 a2 2
7 a7 7 a3 2
8 a8 8 a5 3
9 a9 9 a8 4
...

这一类似这样的表每个name 都有唯一的aid，parentId对应的是属于哪个父ID，iddegree表示层级，类似的人员表，应该很常见。我初接触这个，很多查询都不是很熟练，想做个以 iddegree为1 的分别的检视表，就是a1,a2,a3,a4的下层的人员表。要怎么弄呢？（我一开始的需求是给个参数如a2判断选出所有属于a2的部门人员，显示出a2这样的表）

[解决办法]

SQL code

--ID aid name parentID iddegreeif OBJECT_ID('tf_getchildInfo','tf') is not null drop function tf_getchildInfogocreate function tf_getchildInfo(@AID varchar(10))returns @table table(    ID int,    AID varchar(10),    Name int,    ParentID varchar(10),    Iddegree int)asBegin    with tb1 as    (        --虚拟出一张表            select 1 as id,'a1' as aid,1 as name,'' as parentID,0 as iddegree union all            select 2,'a2',2,'a1',1 union all             select 3,'a3',3,'a1',1 union all             select 4,'a4',4,'a1',1 union all             select 5,'a5',5,'a2',2 union all             select 6,'a6',6,'a2',2 union all             select 7,'a7',7,'a3',2 union all             select 8,'a8',8,'a5',3 union all             select 9,'a9',9,'a8',4     ),    cte as     (        --使用递归功能        select id,aid,name,parentID,iddegree from tb1        where aid=@AID-- 使用传递的参数        union all        select tb1.id,tb1.aid,tb1.name,tb1.parentID,tb1.iddegree  from cte         join tb1 on tb1.parentID=cte.aid    )        --把数据放到一张变量表    insert into @table(id,aid,name,parentID,iddegree)    select * from cte    returnendgoselect * from dbo.tf_getchildInfo('a1')go/*ID          AID        Name        ParentID   Iddegree----------- ---------- ----------- ---------- -----------1           a1         1                      02           a2         2           a1         13           a3         3           a1         14           a4         4           a1         17           a7         7           a3         25           a5         5           a2         26           a6         6           a2         28           a8         8           a5         39           a9         9           a8         4(9 row(s) affected)*/
[解决办法]
SQL codeif OBJECT_ID('v_childInfo','V') is not null drop view v_childInfogocreate view v_childInfoaswith tb1 as    (        --虚拟出一张表            select 1 as id,'a1' as aid,1 as name,'' as parentID,0 as iddegree union all            select 2,'a2',2,'a1',1 union all             select 3,'a3',3,'a1',1 union all             select 4,'a4',4,'a1',1 union all             select 5,'a5',5,'a2',2 union all             select 6,'a6',6,'a2',2 union all             select 7,'a7',7,'a3',2 union all             select 8,'a8',8,'a5',3 union all             select 9,'a9',9,'a8',4     ),    cte as     (        --使用递归功能        select id,aid,name,parentID,iddegree from tb1        where aid='a5'-- 使用传递的参数        union all        select tb1.id,tb1.aid,tb1.name,tb1.parentID,tb1.iddegree  from cte         join tb1 on tb1.parentID=cte.aid    )    select * from ctegoselect * from v_childInfogo/*id          aid  name        parentID iddegree----------- ---- ----------- -------- -----------5           a5   5           a2       28           a8   8           a5       39           a9   9           a8       4(3 row(s) affected)*/

刚接触MSSQL现在想做个view不会做

热点推荐