create proc Report_GetSerialNoSum
@Specialcode varchar(50) ='',
@province varchar(50) = '',
@bill_to varchar(50) ='',
@Brand varchar(200) ='',
@bu_code varchar(10) ='',
@item_no varchar(50) ='',
@sDay varchar(50) = '',
@eDay varchar(50) ='',
@outtype varchar(10)=''
as
set @item_no=replace(Rtrim(@item_no),'.','')
set @item_no=case when len(@item_no)=12 then left(@item_no,4)+'.'+SUBSTRING(@item_no,5,5)+'.'+RIGHT(@item_no,3) else left(@item_no,2)+'.'+SUBSTRING(@item_no,3,5)+'.'+RIGHT(@item_no,3) enddeclare @sql varchar(max)set @sql = 'select b.SpecialCompname, b.province,c.m01_bu_code as productline,d.wkcode,a.OutBillto,b.company ,a.OutStoreID, a.SaleType,
c.m01_brand as branch,a.OutItemNo,c.m01_prod_family as productfamily , count(a.OutSerialNo)as amount, a.OutUplUser
from CH_OutSNhistory a
join dbo.v_CustomerBasic b on a.OutBillto = b.bill_to
join Dealer.dbo.mstock c on a.OutItemNo = c.m01_item_no
join [Dealer].[dbo].dbrpt_period_Calendar d on a.cyc_id = d.wkcode group by b.SpecialCompname, b.province,c.m01_bu_code,d.wkcode,a.OutBillto,b.company ,a.OutStoreID, a.SaleType,
c.m01_brand,a.OutItemNo,c.m01_prod_family ,a.OutUplUser 'if(LEN(@Specialcode) >0)
set @sql=@sql+' and b.SpecialCompname in(''' +Replace(@Specialcode,',',''',''') +''')'
if(LEN(@province) >0)
set @sql=@sql+' and b.province in(''' +Replace(@province,',',''',''') +''')'
if(LEN(@bill_to) >0)
set @sql = @sql +' and b.bill_to = ''' +@bill_to + ''''
if(LEN(@Brand) >0)
set @sql= @sql + ' and c.branch ='''+@Brand + ''''
if(LEN(@bu_code) > 0)
set @sql =@sql+' and c.m01_bu_code in('''+Replace(@bu_code,',',''',''') +''')'
if(LEN(@item_no) >0)
set @sql=@sql+'and a.OutItemNo =''' + @item_no +''''
if(LEN(@sDay) >0)
set @sql =@sql + ' and a.OutUplDate >= ''' + @sDay + ''''
if(LEN(@eDay) > 0)
set @sql = @sql + ' and a.OutUplDate <=''' +@eday +''''
if(LEN(@outtype) >0)
set @sql=@sql + ' and a.SaleType =''' +@outtype +''''这是一个多条件查询的存储过程,我想在后面加一个group by 语句,该怎么加啊?急求!!!
@Specialcode varchar(50) ='',
@province varchar(50) = '',
@bill_to varchar(50) ='',
@Brand varchar(200) ='',
@bu_code varchar(10) ='',
@item_no varchar(50) ='',
@sDay varchar(50) = '',
@eDay varchar(50) ='',
@outtype varchar(10)=''
as
set @item_no=replace(Rtrim(@item_no),'.','')
set @item_no=case when len(@item_no)=12 then left(@item_no,4)+'.'+SUBSTRING(@item_no,5,5)+'.'+RIGHT(@item_no,3) else left(@item_no,2)+'.'+SUBSTRING(@item_no,3,5)+'.'+RIGHT(@item_no,3) enddeclare @sql varchar(max)set @sql = 'select b.SpecialCompname, b.province,c.m01_bu_code as productline,d.wkcode,a.OutBillto,b.company ,a.OutStoreID, a.SaleType,
c.m01_brand as branch,a.OutItemNo,c.m01_prod_family as productfamily , count(a.OutSerialNo)as amount, a.OutUplUser
from CH_OutSNhistory a
join dbo.v_CustomerBasic b on a.OutBillto = b.bill_to
join Dealer.dbo.mstock c on a.OutItemNo = c.m01_item_no
join [Dealer].[dbo].dbrpt_period_Calendar d on a.cyc_id = d.wkcode group by b.SpecialCompname, b.province,c.m01_bu_code,d.wkcode,a.OutBillto,b.company ,a.OutStoreID, a.SaleType,
c.m01_brand,a.OutItemNo,c.m01_prod_family ,a.OutUplUser 'if(LEN(@Specialcode) >0)
set @sql=@sql+' and b.SpecialCompname in(''' +Replace(@Specialcode,',',''',''') +''')'
if(LEN(@province) >0)
set @sql=@sql+' and b.province in(''' +Replace(@province,',',''',''') +''')'
if(LEN(@bill_to) >0)
set @sql = @sql +' and b.bill_to = ''' +@bill_to + ''''
if(LEN(@Brand) >0)
set @sql= @sql + ' and c.branch ='''+@Brand + ''''
if(LEN(@bu_code) > 0)
set @sql =@sql+' and c.m01_bu_code in('''+Replace(@bu_code,',',''',''') +''')'
if(LEN(@item_no) >0)
set @sql=@sql+'and a.OutItemNo =''' + @item_no +''''
if(LEN(@sDay) >0)
set @sql =@sql + ' and a.OutUplDate >= ''' + @sDay + ''''
if(LEN(@eDay) > 0)
set @sql = @sql + ' and a.OutUplDate <=''' +@eday +''''
if(LEN(@outtype) >0)
set @sql=@sql + ' and a.SaleType =''' +@outtype +''''这是一个多条件查询的存储过程,我想在后面加一个group by 语句,该怎么加啊?急求!!!
GROUP BY b.SpecialCompname, b.province,c.m01_bu_code ,d.wkcode,a.OutBillto,b.company ,a.OutStoreID, a.SaleType,
c.m01_brand ,a.OutItemNo,c.m01_prod_family
, a.OutUplUser
'
@Specialcode varchar(50) ='',
@province varchar(50) = '',
@bill_to varchar(50) ='',
@Brand varchar(200) ='',
@bu_code varchar(10) ='',
@item_no varchar(50) ='',
@sDay varchar(50) = '',
@eDay varchar(50) ='',
@outtype varchar(10)=''
as
set @item_no=replace(Rtrim(@item_no),'.','')
set @item_no=case when len(@item_no)=12 then left(@item_no,4)+'.'+SUBSTRING(@item_no,5,5)+'.'+RIGHT(@item_no,3) else left(@item_no,2)+'.'+SUBSTRING(@item_no,3,5)+'.'+RIGHT(@item_no,3) enddeclare @sql varchar(max)set @sql = 'select b.SpecialCompname, b.province,c.m01_bu_code as productline,d.wkcode,a.OutBillto,b.company ,a.OutStoreID, a.SaleType,
c.m01_brand as branch,a.OutItemNo,c.m01_prod_family as productfamily , count(a.OutSerialNo)as amount, a.OutUplUser
from CH_OutSNhistory a
join dbo.v_CustomerBasic b on a.OutBillto = b.bill_to
join Dealer.dbo.mstock c on a.OutItemNo = c.m01_item_no
join [Dealer].[dbo].dbrpt_period_Calendar d on a.cyc_id = d.wkcode 'if(LEN(@Specialcode) >0)
set @sql=@sql+' and b.SpecialCompname in(''' +Replace(@Specialcode,',',''',''') +''')'
if(LEN(@province) >0)
set @sql=@sql+' and b.province in(''' +Replace(@province,',',''',''') +''')'
if(LEN(@bill_to) >0)
set @sql = @sql +' and b.bill_to = ''' +@bill_to + ''''
if(LEN(@Brand) >0)
set @sql= @sql + ' and c.branch ='''+@Brand + ''''
if(LEN(@bu_code) > 0)
set @sql =@sql+' and c.m01_bu_code in('''+Replace(@bu_code,',',''',''') +''')'
if(LEN(@item_no) >0)
set @sql=@sql+'and a.OutItemNo =''' + @item_no +''''
if(LEN(@sDay) >0)
set @sql =@sql + ' and a.OutUplDate >= ''' + @sDay + ''''
if(LEN(@eDay) > 0)
set @sql = @sql + ' and a.OutUplDate <=''' +@eday +''''
if(LEN(@outtype) >0)
set @sql=@sql + ' and a.SaleType =''' +@outtype +''''
--把上面的group by去掉,然后在上面的拼接的where语句之后,再加上group by
set @sql = @sql + 'group by b.SpecialCompname, b.province,c.m01_bu_code,d.wkcode,a.OutBillto,b.company ,a.OutStoreID, a.SaleType,
c.m01_brand,a.OutItemNo,c.m01_prod_family ,a.OutUplUser '
的前面 加上
set @sql = @sql + ' group by ......'
楼主 你要明白 sql语句的执行顺序