SQL话语:显示大类和该大类所对应的小类,并汇总
SQL语句:显示大类和该大类所对应的小类,并汇总
------解决方案--------------------
- SQL code
数据表中记录: Id Name ParentId 1 A 0 2 a1 1 3 B 0 4 C 0 5 a2 1 6 b1 3 1:用一条SQL语句得到这个结果(大类嵌套小类) Id Name ParentId 1 A 0 2 a1 1 5 a2 1 3 B 0 6 b1 3 4 C 0 2:用一条SQL语句得到这个结果(大类嵌套小类,小类汇总ParentId的值): Id Name ParentId 1 A 0 2 a1 1 5 a2 1 小计 2 3 B 0 6 b1 3 小计 3 4 C 0 小计 0
------解决方案--------------------
- SQL code
--大类嵌套小类 select * from test order by Name,ParentId
------解决方案--------------------
part 1
- SQL code
select id, name, pid from ( 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 a order by vpid, pid, id
------解决方案--------------------
good
------解决方案--------------------
good
------解决方案--------------------
- SQL code
if not object_id('test') is null drop table test go create table test(Id varchar(2),Name nvarchar(2),ParentId int) go insert into test select '1','A',0 union all select '2','a1',1 union all select '3','B',0 union all select '4','C',0 union all select '5','a2',1 union all select '6','b1',3 go --Part 1 select * 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 ) t order by sort,ParentId /* Id Name ParentId sort ---- ---- ----------- ---- 1 A 0 A 2 a1 1 A 5 a2 1 A 3 B 0 B 6 b1 3 B 4 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' ) tt order by tt.sort,tt.ParentId /* Id Name ParentId ---- ---- ----------- 1 A 0 2 a1 1 5 a2 1 小计 2 3 B 0 6 b1 3 小计 1 4 C 0 小计 0 */