sql分级查询

USE [zxerp]
GO
/****** Object:  StoredProcedure [dbo].[Pro_GrossProfitCount]    Script Date: 06/26/2014 08:48:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[Pro_GrossProfitCount]
@fcomid int,
@startrq varchar(10),
@endrq varchar(10),
@FStockID varchar(20),
@FNumber varchar(20),
@FItemID varchar(20),
@FServiceID varchar(20),
@FCompanynumber varchar(20),
@FCompanyID varchar(20),
@FDeptID varchar(20),
@Ftype varchar(10),
@Flevel varchar(10)
as
begin
declare @sql varchar(3000)
declare @sql1 varchar(2000)
declare @sql2 varchar(2000)
declare @sql3 varchar(2000)
declare @sql4 varchar(2000)
-----一级
set @sql1='select ''总计'' as category,sum(a.fqty) as fqty,sum(a.famt) as famt,SUM(a.fcost) as fcost,SUM(a.famt-a.fcost) as total 
into #category
from T_bill_detail a,t_bill b,t_item c,T_Item d  ,t_item e  
where a.Fid = b.fid and b.ftype = '''+@Ftype+''' and a.FitemID = c.FItemID and c.FcomID = '''+cast(@fcomid as varchar(4))+''' and b.FDate >='''+@startrq+'''
and b.FDate <='''+@endrq+''' and b.FCompanyID = d.FItemID and a.fitemid = e.FItemID '

----二级（店级）

set @sql2='select c.FItemID,1 as sort,'
set @sql2=@sql2+'''　　''+c.FName as category,sum(a.fqty) as fqty,sum(a.famt) as famt,SUM(a.fcost) as fcost,SUM(a.famt-a.fcost) as total
into #StoreLevel
from T_bill_detail a,t_bill b,T_Item c,T_Item d ,t_item e  
where a.Fid = b.fid and b.ftype = '''+@Ftype+''' and b.FCompanyID = c.FItemID and c.FcomID = '''+cast(@fcomid as varchar(4))+''' and b.FDate >='''+@startrq+'''
 and b.FDate <='''+@endrq+''' and b.FCompanyID = d.FItemID and a.fitemid = e.FItemID '



--------三级（日期级）
if @Flevel = '2'
begin
set @sql3='select 2 as sort,'
end
else
begin
set @sql3='select c.FItemID,CONVERT(varchar(10), b.FDate, 23) as FDate,2 as sort,'
end
set @sql3=@sql3+'''　　　　''+CONVERT(varchar(10), b.FDate, 23) as category,sum(a.fqty) as fqty,sum(a.famt) as famt,SUM(a.fcost) as fcost,SUM(a.famt-a.fcost) as total 
into #RqLevel
from T_bill_detail a,t_bill b,T_Item c,T_Item d ,t_item e   
where a.Fid = b.fid and b.ftype = '''+@Ftype+''' and b.FCompanyID = c.FItemID and c.FItemID in (select FItemID from #StoreLevel) 
and b.FDate >='''+@startrq+''' and b.FDate <='''+@endrq+''' and b.FCompanyID = d.FItemID and a.fitemid = e.FItemID ' 




------------四级（商品级）

if @Flevel = '3'
begin
set @sql4='select 3 as sort,'
end
else
begin
set @sql4='select c.FItemID,CONVERT(varchar(10), b.FDate, 23) as FDate,3 as sort,'
end
set @sql4=@sql4+'''　　　　　　''+e.Fname as category,sum(a.fqty) as fqty,sum(a.famt) as famt,SUM(a.fcost) as fcost,SUM(a.famt-a.fcost) as total 
into #GoodsLevel
from T_bill_detail a,t_bill b,T_Item c,T_Item d,t_item e  
where a.Fid = b.fid and b.ftype = '''+@Ftype+''' and b.FCompanyID = c.FItemID and a.fitemid = e.FItemID 
and b.FDate >='''+@startrq+''' and b.FDate <='''+@endrq+''' and b.FCompanyID = d.FItemID and e.FcomID = '''+cast(@fcomid as varchar(4))+''' ' 
--------------------------------
if @FStockID<>'zwc'
begin
set @sql1=@sql1+' and a.FstockIDin ='''+@FStockID+''' '
set @sql2=@sql2+' and a.FstockIDin ='''+@FStockID+''' '
set @sql3=@sql3+' and a.FstockIDin ='''+@FStockID+''' '
set @sql4=@sql4+' and a.FstockIDin ='''+@FStockID+''' '
end
if @FNumber<>'zwc'
begin
set @sql1=@sql1+' and e.FNumber like ''%'+@FNumber+'%'' '
set @sql2=@sql2+' and e.FNumber like ''%'+@FNumber+'%'' '
set @sql3=@sql3+' and e.FNumber like ''%'+@FNumber+'%'' '
set @sql4=@sql4+' and e.FNumber like ''%'+@FNumber+'%'' '
end
if @FItemID<>'zwc'
begin
set @sql1=@sql1+' and e.FitemID ='''+@FItemID+''' '
set @sql2=@sql2+' and e.FitemID ='''+@FItemID+''' '
set @sql3=@sql3+' and e.FitemID ='''+@FItemID+''' '
set @sql4=@sql4+' and e.FitemID ='''+@FItemID+''' '
end
if @FServiceID<>'zwc'
begin
set @sql1=@sql1+' and b.FServiceID ='''+@FServiceID+''' '
set @sql2=@sql2+' and b.FServiceID ='''+@FServiceID+''' '
set @sql3=@sql3+' and b.FServiceID ='''+@FServiceID+''' '
set @sql4=@sql4+' and b.FServiceID ='''+@FServiceID+''' '
end
if @FCompanynumber<>'zwc'
begin
set @sql1=@sql1+' and d.FNumber like ''%'+@FCompanynumber+'%'' '
set @sql2=@sql2+' and d.FNumber like ''%'+@FCompanynumber+'%'' '
set @sql3=@sql3+' and d.FNumber like ''%'+@FCompanynumber+'%'' '
set @sql4=@sql4+' and d.FNumber like ''%'+@FCompanynumber+'%'' '
end
if @FCompanyID<>'zwc'
begin
set @sql1=@sql1+' and b.FCompanyID ='''+@FCompanyID+''' '
set @sql2=@sql2+' and b.FCompanyID ='''+@FCompanyID+''' '
set @sql3=@sql3+' and b.FCompanyID ='''+@FCompanyID+''' '
set @sql4=@sql4+' and b.FCompanyID ='''+@FCompanyID+''' '
end
if @FDeptID<>'zwc'
begin
set @sql1=@sql1+' and b.FDeptID ='''+@FDeptID+''' '
set @sql2=@sql2+' and b.FDeptID ='''+@FDeptID+''' '
set @sql3=@sql3+' and b.FDeptID ='''+@FDeptID+''' '
set @sql4=@sql4+' and b.FDeptID ='''+@FDeptID+''' '
end

set @sql2=@sql2+' group by c.FItemID,c.fname '
if @Flevel = '2'
begin
set @sql3=@sql3+' group by CONVERT(varchar(10), b.FDate, 23) '
end
else
begin
set @sql3=@sql3+' group by c.FItemID,c.fname,CONVERT(varchar(10), b.FDate, 23) '
end
if @Flevel = '123'
begin
set @sql4 = @sql4 +' group by CONVERT(varchar(10), b.FDate, 23),c.FItemID,e.FName'
end
else
begin
if @Flevel = '3'
begin
set @sql4 = @sql4 +' group by e.FItemID,e.FName'
end
else
begin
set @sql4 = @sql4 +' group by c.FItemID,e.FName,CONVERT(varchar(10), b.FDate, 23)'
end
end
--print @sql1
--print @sql2
--print @sql3
--exec (@sql1)
--exec (@sql2)
--exec (@sql3)
set @sql = @sql1 + @sql2 + @sql3 + @sql4
if @Flevel = '123'
begin
set @sql = @sql +'
select FItemID,FDate,sort,category,fqty,famt,fcost,total into #three
from (
select FItemID,FDate,sort,category,fqty,famt,fcost,total from #RqLevel
union all
select FItemID,FDate,sort,category,fqty,famt,fcost,total from #GoodsLevel ) a
 order by FItemID,FDate,sort

select FItemID,FDate,sort,category,fqty,famt,fcost,total into #total
from (
select FItemID,''2000-01-01'' as FDate,sort,category,fqty,famt,fcost,total from #StoreLevel
union all
select FItemID,FDate,sort,category,fqty,famt,fcost,total from #three ) a 
order by FItemID,FDate,sort

select category,fqty,famt,fcost,total from #category
union all
select category,fqty,famt,fcost,total from #total'
end
if @Flevel = '12'
begin
set @sql = @sql +'
select category,fqty,famt,fcost,total into #total
from (
select FItemID,sort,category,fqty,famt,fcost,total from #StoreLevel
union all
select FItemID,sort,category,fqty,famt,fcost,total from #RqLevel) a order by FItemID,sort

select category,fqty,famt,fcost,total from #category
union all
select category,fqty,famt,fcost,total from #total'
end
if @Flevel = '1'
begin
set @sql = @sql +'
select category,fqty,famt,fcost,total from #category
union all
select category,fqty,famt,fcost,total from #StoreLevel
'
end
if @Flevel = '13'
begin
set @sql = @sql +'select *  into #total from (
select FItemID,sort,category,fqty,famt,fcost,total from #StoreLevel
union all
select FItemID,sort,category,fqty,famt,fcost,total from #GoodsLevel ) a order by  FItemID,sort

select category,fqty,famt,fcost,total from #category
union all
select category,fqty,famt,fcost,total from #total
'
end
if @Flevel = '23'
begin

set @sql = @sql +'select *  into #total from (
select FItemID,FDate,sort,category,fqty,famt,fcost,total from #RqLevel
union all
select FItemID,FDate,sort,category,fqty,famt,fcost,total from #GoodsLevel ) a order by  FItemID,FDate,sort

select category,fqty,famt,fcost,total from #category
union all
select category,fqty,famt,fcost,total from #total
'
end
if @Flevel = '2'
begin
set @sql = @sql +'
select category,fqty,famt,fcost,total from #category
union all
select category,fqty,famt,fcost,total from #RqLevel
'end
if @Flevel = '3'
begin
set @sql = @sql +'
select category,fqty,famt,fcost,total from #category
union all
select category,fqty,famt,fcost,total from #GoodsLevel
'end
if @Flevel = '0'
begin
set @sql = @sql +'
select category,fqty,famt,fcost,total from #category
'end
set @sql = @sql +'
drop table #category
drop table #StoreLevel
drop table #RqLevel
drop table #GoodsLevel'
if @Flevel = '123' or @Flevel = '12' or @Flevel = '13' or @Flevel = '13'
set @sql = @sql +'
drop table #total'

if @Flevel = '123'
set @sql = @sql +'
drop table #three'
--print @sql
exec(@sql)
end