部门用款汇总统计

Question

select left(部门,2) as 部门,
  sum(用款) as 用款总额
from tab
group by left(部门,2) 

Answer 1

--建一个函数
create function fn_部门(
@id int
)
returns int
as
begin
   declare @r int
   declare @fid int
   set @r=@id
   select @fid=FatherId from viewOwner where id=@id
   while @fid not in (1,-1)
   begin
      set @r=@fid
      select @fid=FatherId from viewOwner where id=@r
   end
   return @r
end
go--查询
select dbo.fn_部门(Owner) as 部门,
  sum(Amount) as 用款总额
from Expense
group by dbo.fn_部门(Owner)

Answer 2

重新叙述一下,带了演示数据,请各位朋友指教一下哈。
汇总统计总公司下的二级部门及其子部门的用款, 
要求显示到   二级部门(二级部门的子部门用款自动汇总到二级部门下) 
比如, 
部门结构为: 总公司
 zhb             20
        zhbgs    10
        st       5
 bjglb           10
 jls             10
 wcglb
       wcglblp   1
显示结果应该为: 
  部门                       用款总额 
  zhb                        35 
  bjglb                      10
  jls                        10
  wcglb                      1请问有没有朋友知道   SQL   查询语句怎么写?   谢谢!表结构和演示数据:create   table   Expense(Owner   varchar(256),Amount   decimal(9)) 
insert   into   Expense   select   'SD!','10' 
insert   into   Expense   select   'SD2','10' 
insert   into   Expense   select   'SD17','10' 
insert   into   Expense   select   'SD18','5' 
insert   into   Expense   select   'SD11','10' 
insert   into   Expense   select   'CY000002','1' 
insert   into   Expense   select   'SD2','10' 
create   table   viewOwner(Name varchar(256),Id   varchar(256),FatherId   varchar(256)) 
insert   into   viewOwner   select   'bjglb','SD11','C' 
insert   into   viewOwner   select   'jls','SD1','C' insert   into   viewOwner   select   'zhb','SD2','C' insert   into   viewOwner   select   'zhbgs','SD17','SD2' 
insert   into   viewOwner   select   'st','SD18','SD2' 
insert   into   viewOwner   select   'wcglb','SD10','C' 
insert   into   viewOwner   select   'wcglblp','CY000002','SD10' 

Answer 3

--建一个函数
create function fn_部门(
@id varchar(256)
)
returns varchar(256)
as
begin
   declare @r varchar(256)
   declare @fid varchar(256)
   declare @Name varchar(256)
   set @r=@id
   select @Name=Name,@fid=FatherId from viewOwner where id=@id
   while @fid <>'C'
   begin
      set @r=@fid
      select @fid=FatherId from viewOwner where id=@r
   end
   return @Name
end
go--查询
select dbo.fn_部门(Owner) as 部门,
  sum(Amount) as 用款总额
from Expense
group by dbo.fn_部门(Owner) 
 
 

Answer 4

谢谢 Haiwer 
但是你的方法没有满足这个需求：
“ 要求显示到 二级部门(二级部门的子部门用款自动汇总到二级部门下)"
显示结果应该为:   
    部门                                               用款总额   
    zhb                                                35   
    bjglb                                             10 
    jls                                               10 
    wcglb                                             1 你帮忙再看看呢,谢谢.

Answer 5

更正了函数，
你的数据有错误，SD!应该改为SD1create               table               Expense(Owner               varchar(256),Amount               decimal(9))       
insert               into               Expense               select               'SD1','10'       
insert               into               Expense               select               'SD2','10'       
insert               into               Expense               select               'SD17','10'       
insert               into               Expense               select               'SD18','5'       
insert               into               Expense               select               'SD11','10'       
insert               into               Expense               select               'CY000002','1'       
insert               into               Expense               select               'SD2','10'       
create               table               viewOwner(Name       varchar(256),Id               varchar(256),FatherId               varchar(256))       
insert               into               viewOwner               select               'bjglb','SD11','C'       
insert               into               viewOwner               select               'jls','SD1','C'       insert               into               viewOwner               select               'zhb','SD2','C'       insert               into               viewOwner               select               'zhbgs','SD17','SD2'       
insert               into               viewOwner               select               'st','SD18','SD2'       
insert               into               viewOwner               select               'wcglb','SD10','C'       
insert               into               viewOwner               select               'wcglblp','CY000002','SD10'   
go
--建一个函数
Create function fn_部门(
@id varchar(256)
)
returns varchar(256)
as
begin
   declare @r varchar(256)
   declare @fid varchar(256)
   declare @Name varchar(256)
   set @r=@id
   select @Name=Name,@fid=FatherId from viewOwner where id=@id
   while @fid <>'C'
   begin
      set @r=@fid
      select @Name=Name,@fid=FatherId from viewOwner where id=@r
   end
   return @Name
end
go--查询
select dbo.fn_部门(Owner) as 部门,
  sum(Amount) as 用款总额
from Expense
group by dbo.fn_部门(Owner)--结果
部门                                                                                                                                                                                                                                                               用款总额                                     
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------- 
bjglb                                                                                                                                                                                                                                                            10
jls                                                                                                                                                                                                                                                              10
wcglb                                                                                                                                                                                                                                                            1
zhb                                                                                                                                                                                                                                                              35（所影响的行数为 4 行）