SQL话语：显示大类和该大类所对应的小类，并汇总

数据表中记录：Id              Name        ParentId1               A               02               a1              13               B               04               C               05               a2              16               b1              31：用一条SQL语句得到这个结果（大类嵌套小类）Id              Name        ParentId1               A               02               a1              15               a2              13               B               06               b1              34               C               02：用一条SQL语句得到这个结果（大类嵌套小类，小类汇总ParentId的值）：Id              Name        ParentId1               A               02               a1              15               a2              1                小计            23               B               06               b1              3                小计            34               C               0                小计            0

--大类嵌套小类select * from test order by Name,ParentId
------解决方案--------------------
part 1
SQL code
select id, name, pidfrom (  select id, name, pid, id as vpid  from tb  where parentid=0  union  select id, name, pid, pid as vpid  from tb  where parentid>0) as aorder by vpid, pid, id
------解决方案--------------------
good
------解决方案--------------------
good
------解决方案--------------------
SQL code
if not object_id('test') is null drop table testgocreate table test(Id varchar(2),Name nvarchar(2),ParentId int)goinsert into testselect '1','A',0 union allselect '2','a1',1 union allselect '3','B',0 union allselect '4','C',0 union allselect '5','a2',1 union allselect '6','b1',3go--Part 1select * from(    select a.Id,a.Name,a.ParentId,sort=isnull((select b.Name from test b where a.ParentId=b.Id),a.Name)     from test a) torder by sort,ParentId/*Id   Name ParentId    sort---- ---- ----------- ----1    A    0           A2    a1   1           A5    a2   1           A3    B    0           B6    b1   3           B4    C    0           C*/--Part 2;with t as(    select a.Id,a.Name,a.ParentId,sort=isnull((select b.Name from test b where a.ParentId=b.Id),a.Name) from test a)select tt.Id,tt.Name,tt.ParentId from (    select Id,Name,ParentId,sort from t    union all    select '',N'小计' ,count(sort)-1,sort+'1' from t    group by sort+'1') ttorder by tt.sort,tt.ParentId/*Id   Name ParentId---- ---- -----------1    A    02    a1   15    a2   1     小计   23    B    06    b1   3     小计   14    C    0     小计   0*/