1 USE [rfiddbHFKT]
 2 GO
 3 /****** Object:  StoredProcedure [dbo].[ppque_tu]    Script Date: 07/21/2017 23:36:40 ******/
 4 SET ANSI_NULLS OFF
 5 GO
 6 SET QUOTED_IDENTIFIER OFF
 7 GO
 8 ALTER PROCEDURE [dbo].[ppque_tu]              
 9 (            
10   @fdatebegin  varchar(50),  ---开始日期            
11   @fdateend      varchar(50),  ---结束日期            
12   @linename   varchar(50),  ---生产线            
13   @zhandian   varchar(50),  ----站点            
14   @banci   varchar(50),       ---班次            
15   @strnum varchar(5)           ----数量  默认数量是10            
16 )            
17 AS                
18   declare @sqltext varchar(1500)                
19   declare @fcount int             
20   declare @sumTotal int           
21   ---缺陷饼图              
22               
23   --生成临时表              
24   create table #que_cx(              
25     class_name nvarchar(200),              
26     sernsum int,          
27      rate decimal(18,2))              
28               
29   --统计数据 添加查询条件              
30     insert into #que_cx(class_name,sernsum)              
31     select c.class_name, count(c.class_name) as sernsum                
32     from tjianyan a,tsaomiao b, z_que_class c               
33     where a.sern = b.sern and a.zd_bh = b.zd_bh and a.que_class = c.class_id               
34       and a.ddate  between convert(datetime,@fdatebegin,120)           
35     and convert(datetime,@fdateend,120)         
36     and(case when @linename=‘‘ then 1 else CHARINDEX(@linename,b.linename) end)>0               
37      and  a.zd_bh like ‘%‘+@zhandian+‘%‘    
38      and(case when @banci=‘‘ then 1 else CHARINDEX(@banci,a.bc_id) end)>0               
39      group by c.class_name order by sernsum desc                
40        
41   --如果小于设置数量 不显示其余项          
42   select @sumTotal=sum(sernsum) from #que_cx          
43   select @fcount = count(*) from #que_cx          
44    update #que_cx  set rate=sernsum*1.00/@sumTotal * 100  
45         
46   if @fcount>@strnum             
47       set  @sqltext = ‘select top ‘ + @strnum + ‘  class_name,‘‘a‘‘ as aa,sernsum,rate  from  #que_cx  ‘               
48                        +‘ union ‘              
49                        +‘ select  ‘‘其余项‘‘ as class_name ,‘‘b‘‘ as aa, isnull(sum(sernsum),0) as sernsum,sum(rate)  from  #que_cx ‘               
50                        +‘ where class_name not in ( select top ‘ + @strnum + ‘  class_name from  #que_cx ) ‘      
51                       +‘  order by aa,sernsum desc‘               
52    else             
53           set  @sqltext = ‘select  class_name,‘‘a‘‘ as aa,sernsum,rate  from  #que_cx  ‘               
54                        +‘ order by aa,sernsum,rate  desc‘               
55                                    
56 exec(@sqltext)