sql分级查询

  1 USE [zxerp]
  2 GO
  3 /****** Object:  StoredProcedure [dbo].[Pro_GrossProfitCount]    Script Date: 06/26/2014 08:48:10 ******/
  4 SET ANSI_NULLS ON
  5 GO
  6 SET QUOTED_IDENTIFIER ON
  7 GO
  8 ALTER procedure [dbo].[Pro_GrossProfitCount]
  9 @fcomid int,
 10 @startrq varchar(10),
 11 @endrq varchar(10),
 12 @FStockID varchar(20),
 13 @FNumber varchar(20),
 14 @FItemID varchar(20),
 15 @FServiceID varchar(20),
 16 @FCompanynumber varchar(20),
 17 @FCompanyID varchar(20),
 18 @FDeptID varchar(20),
 19 @Ftype varchar(10),
 20 @Flevel varchar(10)
 21 as
 22 begin
 23 declare @sql varchar(3000)
 24 declare @sql1 varchar(2000)
 25 declare @sql2 varchar(2000)
 26 declare @sql3 varchar(2000)
 27 declare @sql4 varchar(2000)
 28 -----一级
 29 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 
 30 into #category
 31 from T_bill_detail a,t_bill b,t_item c,T_Item d  ,t_item e  
 32 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+‘‘‘
 33 and b.FDate <=‘‘‘+@endrq+‘‘‘ and b.FCompanyID = d.FItemID and a.fitemid = e.FItemID 
 34 
 35 ----二级(店级)
 36 
 37 set @sql2=select c.FItemID,1 as sort,
 38 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
 39 into #StoreLevel
 40 from T_bill_detail a,t_bill b,T_Item c,T_Item d ,t_item e  
 41 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+‘‘‘
 42  and b.FDate <=‘‘‘+@endrq+‘‘‘ and b.FCompanyID = d.FItemID and a.fitemid = e.FItemID 
 43 
 44 
 45 
 46 --------三级(日期级)
 47 if @Flevel = 2
 48 begin
 49 set @sql3=select 2 as sort,
 50 end
 51 else
 52 begin
 53 set @sql3=select c.FItemID,CONVERT(varchar(10), b.FDate, 23) as FDate,2 as sort,
 54 end
 55 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 
 56 into #RqLevel
 57 from T_bill_detail a,t_bill b,T_Item c,T_Item d ,t_item e   
 58 where a.Fid = b.fid and b.ftype = ‘‘‘+@Ftype+‘‘‘ and b.FCompanyID = c.FItemID and c.FItemID in (select FItemID from #StoreLevel) 
 59 and b.FDate >=‘‘‘+@startrq+‘‘‘ and b.FDate <=‘‘‘+@endrq+‘‘‘ and b.FCompanyID = d.FItemID and a.fitemid = e.FItemID  
 60 
 61 
 62 
 63 
 64 ------------四级(商品级)
 65 
 66 if @Flevel = 3
 67 begin
 68 set @sql4=select 3 as sort,
 69 end
 70 else
 71 begin
 72 set @sql4=select c.FItemID,CONVERT(varchar(10), b.FDate, 23) as FDate,3 as sort,
 73 end
 74 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 
 75 into #GoodsLevel
 76 from T_bill_detail a,t_bill b,T_Item c,T_Item d,t_item e  
 77 where a.Fid = b.fid and b.ftype = ‘‘‘+@Ftype+‘‘‘ and b.FCompanyID = c.FItemID and a.fitemid = e.FItemID 
 78 and b.FDate >=‘‘‘+@startrq+‘‘‘ and b.FDate <=‘‘‘+@endrq+‘‘‘ and b.FCompanyID = d.FItemID and e.FcomID = ‘‘‘+cast(@fcomid as varchar(4))+‘‘‘  
 79 --------------------------------
 80 if @FStockID<>zwc
 81 begin
 82 set @sql1=@sql1+ and a.FstockIDin =‘‘‘+@FStockID+‘‘‘ 
 83 set @sql2=@sql2+ and a.FstockIDin =‘‘‘+@FStockID+‘‘‘ 
 84 set @sql3=@sql3+ and a.FstockIDin =‘‘‘+@FStockID+‘‘‘ 
 85 set @sql4=@sql4+ and a.FstockIDin =‘‘‘+@FStockID+‘‘‘ 
 86 end
 87 if @FNumber<>zwc
 88 begin
 89 set @sql1=@sql1+ and e.FNumber like ‘‘%+@FNumber+%‘‘ 
 90 set @sql2=@sql2+ and e.FNumber like ‘‘%+@FNumber+%‘‘ 
 91 set @sql3=@sql3+ and e.FNumber like ‘‘%+@FNumber+%‘‘ 
 92 set @sql4=@sql4+ and e.FNumber like ‘‘%+@FNumber+%‘‘ 
 93 end
 94 if @FItemID<>zwc
 95 begin
 96 set @sql1=@sql1+ and e.FitemID =‘‘‘+@FItemID+‘‘‘ 
 97 set @sql2=@sql2+ and e.FitemID =‘‘‘+@FItemID+‘‘‘ 
 98 set @sql3=@sql3+ and e.FitemID =‘‘‘+@FItemID+‘‘‘ 
 99 set @sql4=@sql4+ and e.FitemID =‘‘‘+@FItemID+‘‘‘ 
100 end
101 if @FServiceID<>zwc
102 begin
103 set @sql1=@sql1+ and b.FServiceID =‘‘‘+@FServiceID+‘‘‘ 
104 set @sql2=@sql2+ and b.FServiceID =‘‘‘+@FServiceID+‘‘‘ 
105 set @sql3=@sql3+ and b.FServiceID =‘‘‘+@FServiceID+‘‘‘ 
106 set @sql4=@sql4+ and b.FServiceID =‘‘‘+@FServiceID+‘‘‘ 
107 end
108 if @FCompanynumber<>zwc
109 begin
110 set @sql1=@sql1+ and d.FNumber like ‘‘%+@FCompanynumber+%‘‘ 
111 set @sql2=@sql2+ and d.FNumber like ‘‘%+@FCompanynumber+%‘‘ 
112 set @sql3=@sql3+ and d.FNumber like ‘‘%+@FCompanynumber+%‘‘ 
113 set @sql4=@sql4+ and d.FNumber like ‘‘%+@FCompanynumber+%‘‘ 
114 end
115 if @FCompanyID<>zwc
116 begin
117 set @sql1=@sql1+ and b.FCompanyID =‘‘‘+@FCompanyID+‘‘‘ 
118 set @sql2=@sql2+ and b.FCompanyID =‘‘‘+@FCompanyID+‘‘‘ 
119 set @sql3=@sql3+ and b.FCompanyID =‘‘‘+@FCompanyID+‘‘‘ 
120 set @sql4=@sql4+ and b.FCompanyID =‘‘‘+@FCompanyID+‘‘‘ 
121 end
122 if @FDeptID<>zwc
123 begin
124 set @sql1=@sql1+ and b.FDeptID =‘‘‘+@FDeptID+‘‘‘ 
125 set @sql2=@sql2+ and b.FDeptID =‘‘‘+@FDeptID+‘‘‘ 
126 set @sql3=@sql3+ and b.FDeptID =‘‘‘+@FDeptID+‘‘‘ 
127 set @sql4=@sql4+ and b.FDeptID =‘‘‘+@FDeptID+‘‘‘ 
128 end
129 
130 set @sql2=@sql2+ group by c.FItemID,c.fname 
131 if @Flevel = 2
132 begin
133 set @sql3=@sql3+ group by CONVERT(varchar(10), b.FDate, 23) 
134 end
135 else
136 begin
137 set @sql3=@sql3+ group by c.FItemID,c.fname,CONVERT(varchar(10), b.FDate, 23) 
138 end
139 if @Flevel = 123
140 begin
141 set @sql4 = @sql4 + group by CONVERT(varchar(10), b.FDate, 23),c.FItemID,e.FName
142 end
143 else
144 begin
145 if @Flevel = 3
146 begin
147 set @sql4 = @sql4 + group by e.FItemID,e.FName
148 end
149 else
150 begin
151 set @sql4 = @sql4 + group by c.FItemID,e.FName,CONVERT(varchar(10), b.FDate, 23)
152 end
153 end
154 --print @sql1
155 --print @sql2
156 --print @sql3
157 --exec (@sql1)
158 --exec (@sql2)
159 --exec (@sql3)
160 set @sql = @sql1 + @sql2 + @sql3 + @sql4
161 if @Flevel = 123
162 begin
163 set @sql = @sql +
164 select FItemID,FDate,sort,category,fqty,famt,fcost,total into #three
165 from (
166 select FItemID,FDate,sort,category,fqty,famt,fcost,total from #RqLevel
167 union all
168 select FItemID,FDate,sort,category,fqty,famt,fcost,total from #GoodsLevel ) a
169  order by FItemID,FDate,sort
170 
171 select FItemID,FDate,sort,category,fqty,famt,fcost,total into #total
172 from (
173 select FItemID,‘‘2000-01-01‘‘ as FDate,sort,category,fqty,famt,fcost,total from #StoreLevel
174 union all
175 select FItemID,FDate,sort,category,fqty,famt,fcost,total from #three ) a 
176 order by FItemID,FDate,sort
177 
178 select category,fqty,famt,fcost,total from #category
179 union all
180 select category,fqty,famt,fcost,total from #total
181 end
182 if @Flevel = 12
183 begin
184 set @sql = @sql +
185 select category,fqty,famt,fcost,total into #total
186 from (
187 select FItemID,sort,category,fqty,famt,fcost,total from #StoreLevel
188 union all
189 select FItemID,sort,category,fqty,famt,fcost,total from #RqLevel) a order by FItemID,sort
190 
191 select category,fqty,famt,fcost,total from #category
192 union all
193 select category,fqty,famt,fcost,total from #total
194 end
195 if @Flevel = 1
196 begin
197 set @sql = @sql +
198 select category,fqty,famt,fcost,total from #category
199 union all
200 select category,fqty,famt,fcost,total from #StoreLevel
201 
202 end
203 if @Flevel = 13
204 begin
205 set @sql = @sql +select *  into #total from (
206 select FItemID,sort,category,fqty,famt,fcost,total from #StoreLevel
207 union all
208 select FItemID,sort,category,fqty,famt,fcost,total from #GoodsLevel ) a order by  FItemID,sort
209 
210 select category,fqty,famt,fcost,total from #category
211 union all
212 select category,fqty,famt,fcost,total from #total
213 
214 end
215 if @Flevel = 23
216 begin
217 
218 set @sql = @sql +select *  into #total from (
219 select FItemID,FDate,sort,category,fqty,famt,fcost,total from #RqLevel
220 union all
221 select FItemID,FDate,sort,category,fqty,famt,fcost,total from #GoodsLevel ) a order by  FItemID,FDate,sort
222 
223 select category,fqty,famt,fcost,total from #category
224 union all
225 select category,fqty,famt,fcost,total from #total
226 
227 end
228 if @Flevel = 2
229 begin
230 set @sql = @sql +
231 select category,fqty,famt,fcost,total from #category
232 union all
233 select category,fqty,famt,fcost,total from #RqLevel
234 end
235 if @Flevel = 3
236 begin
237 set @sql = @sql +
238 select category,fqty,famt,fcost,total from #category
239 union all
240 select category,fqty,famt,fcost,total from #GoodsLevel
241 end
242 if @Flevel = 0
243 begin
244 set @sql = @sql +
245 select category,fqty,famt,fcost,total from #category
246 end
247 set @sql = @sql +
248 drop table #category
249 drop table #StoreLevel
250 drop table #RqLevel
251 drop table #GoodsLevel
252 if @Flevel = 123 or @Flevel = 12 or @Flevel = 13 or @Flevel = 13
253 set @sql = @sql +
254 drop table #total
255 
256 if @Flevel = 123
257 set @sql = @sql +
258 drop table #three
259 --print @sql
260 exec(@sql)
261 end

 

sql分级查询,布布扣,bubuko.com

sql分级查询

上一篇:SQL Server 2008 用户SA登录失败(错误18456)之图文解决方法


下一篇:C#中数据库连接的配置文件