)Create Table SaleInfo ( sID varchar(3), dQuantity decimal(18,2) )insert into sType select '001', '长虹', null union select '002', '29寸', '001' union select '003', '等离子', '002' union select '004', '黑色', '003' union select '005', '红色', '003' union select '006', '背投', '002' union select '007', '红色', '006' union select '008', '黑色', '006'Insert Into SaleInfo select '004', 10 union select '005', 12 unionselect '007', 1 union select '008', 2 go--获取数量 create function f_num(@sID varchar(10)) returns int as begin declare @tb table ( sID varchar(10), ParentID varchar(10) ) insert @tb select sID,ParentID from sType where sID=@sID while @@rowcount>0 begin insert @tb select A.sID,A.ParentID from sType A join @tb B on A.ParentID=B.sID where A.sID not in(select sID from @tb) end declare @num int set @num=0 select @num=@num+isnull(B.dQuantity,0) from @tb A join SaleInfo B on A.sID=B.sID return @num end go--获取级别 create function f_level(@sID varchar(10),@level int) returns int as begin if (select ParentID from sType where sID=@sID) is null return @level return dbo.f_level((select ParentID from sType where sID=@sID),isnull(@level,0)+1) end go--查询 select (case when grouping(sName)=1 then '小计' else sID end) as sID ,isnull(sName,'') as sName ,dbo.f_num(sID) as quantity from sType group by sID,sName with rollup order by dbo.f_level(sID,0) desc --删除测试环境 drop function f_level drop function f_num drop table sType,SaleInfo--结果 /* sID sName quantity ---- -------------------- ----------- 004 黑色 10 小计 10 005 红色 12 小计 12 007 红色 1 小计 1 008 黑色 2 小计 2 006 背投 3 小计 3 003 等离子 22 小计 22 002 29寸 25 小计 25 001 长虹 25 小计 25 小计 0(17 row(s) affected) */
有点瑕疵,修改一下:select (case when grouping(sName)=1 then '小计' else sID end) as sID ,isnull(sName,'') as sName ,dbo.f_num(sID) as quantity from sType group by sID,sName with rollup having grouping(sID)<>1 order by dbo.f_level(sID,0) desc
Tree结构要求按Tree结构来分级统计其实要求是这样电视总共销了多少台
下面分:
长虹销了多少
创维销了多少
再分明细:
长虹里面等离子销了多少,背投销了多少!
(
sID varchar(3),
sName varchar(20),
ParentID varchar(3)
)Create Table SaleInfo
(
sID varchar(3),
dQuantity decimal(18,2)
)insert into sType
select '001', '长虹', null union
select '002', '29寸', '001' union
select '003', '等离子', '002' union
select '004', '黑色', '003' union
select '005', '红色', '003' union
select '006', '背投', '002' union
select '007', '红色', '006' union
select '008', '黑色', '006'Insert Into SaleInfo
select '004', 10 union
select '005', 12 unionselect '007', 1 union
select '008', 2
go--获取数量
create function f_num(@sID varchar(10))
returns int
as
begin
declare @tb table
(
sID varchar(10),
ParentID varchar(10)
) insert @tb
select sID,ParentID from sType where sID=@sID while @@rowcount>0
begin
insert @tb
select A.sID,A.ParentID
from sType A
join @tb B on A.ParentID=B.sID
where A.sID not in(select sID from @tb)
end declare @num int
set @num=0
select @num=@num+isnull(B.dQuantity,0)
from @tb A
join SaleInfo B on A.sID=B.sID return @num
end
go--获取级别
create function f_level(@sID varchar(10),@level int)
returns int
as
begin
if (select ParentID from sType where sID=@sID) is null
return @level return dbo.f_level((select ParentID from sType where sID=@sID),isnull(@level,0)+1)
end
go--查询
select (case when grouping(sName)=1 then '小计' else sID end) as sID
,isnull(sName,'') as sName
,dbo.f_num(sID) as quantity
from sType
group by sID,sName
with rollup
order by dbo.f_level(sID,0) desc
--删除测试环境
drop function f_level
drop function f_num
drop table sType,SaleInfo--结果
/*
sID sName quantity
---- -------------------- -----------
004 黑色 10
小计 10
005 红色 12
小计 12
007 红色 1
小计 1
008 黑色 2
小计 2
006 背投 3
小计 3
003 等离子 22
小计 22
002 29寸 25
小计 25
001 长虹 25
小计 25
小计 0(17 row(s) affected)
*/
,isnull(sName,'') as sName
,dbo.f_num(sID) as quantity
from sType
group by sID,sName
with rollup
having grouping(sID)<>1
order by dbo.f_level(sID,0) desc