后台临时表处理前端第三方控件不能处理的问题
有时候在前端利用第三方控件不能很好处理的事情，或许的后台利用临时表替代前端控件所产生的临时数据集。如下是近段时间碰到的现象，部分记录如下：
/******************************************* * Author : lxh * Date : 2018-01-26 * Description : 制板单款号分析报表(设计部) * * exec uP_BanAnalysisReport '''2018冬季''',null,'''休闲一''','''女装''',null, null,null,null,null,null, null,null,null,null,null, null,null,null,null,null, null,null * modify:增加临时表处理桌面AllData信息20180227 *******************************************/ alter PROC [dbo].[uP_BanAnalysisReport] @Season VARCHAR(30), @responsible VARCHAR(30), @series VARCHAR(30), @sex VARCHAR(30), @goods_category VARCHAR(30), @Styleno VARCHAR(30), @stylenametype VARCHAR(30), @styletype VARCHAR(30), @styletype2 VARCHAR(30), @fabname VARCHAR(30), --No.10 @fabcharacter VARCHAR(30), @Design_style VARCHAR(30), @thickness VARCHAR(30), @Collartype VARCHAR(30), @Waist_type VARCHAR(30), @BX_length VARCHAR(30), @Pants_type VARCHAR(30), @outsidesleeve VARCHAR(30), @UnitpriceFrom INT, @UnitpriceTo INT, --No.20 @color VARCHAR(30), @loginid VARCHAR(20) as begin declare @sql varchar(8000),@charenter varchar(10) set @charenter=char(13)+char(10) set @SQL = '' set @SQL = @SQL+' IF EXISTS(SELECT 1 FROM sysobjects WHERE id = OBJECT_ID(N''[AllData]'') AND xtype=''U'' ) DROP TABLE AllData] SELECT DISTINCT CASE WHEN SUBSTRING(REPLACE(a.salesSeason,'' '',''''),1,4)<''2019'' THEN a.styleno ELSE h2.styleno2 END styleno ,m.responsible --设计部总负债人 ,a.salesSeason --季度 ,h2.goods_category --商品类别 ,a.styletype --款式类型 ,a.styletype2 --类型 ,a.stylenametype --款式大类 ,a.fabname --面料名称 ,a.fabcharacter --面料特性 ,a.thickness --厚度 ,a.Collartype --领型 ,a.Design_style --款式 ,a.Pants_type --袖型/裤型 ,a.Waist_type --腰型 ,a.BX_length --长度/衫长 ,a.outsidesleeve --袖长 ,a.Unitprice --建议价 ,a.series --品牌 ,a.sex --性别 ,a.banxing --板型20180131_add ,'''' AS color --颜色,暂时置空,后续再关联新表 into AllData--20180227_add FROM dsg_bi.dbo.Ban_Lastver_MBT07 b(NOLOCK) LEFT JOIN ban_makebill_head a(NOLOCK) ON a.mb_number=b.mb_number LEFT JOIN ban_makebill_head2 h2(NOLOCK) ON h2.mb_number=a.mb_number LEFT JOIN MC_dsgdept m(NOLOCK) ON a.dsgdeptname=m.dsgdeptname WHERE 1=1 ' + @charenter IF @Season IS NOT NULL SET @sql=@sql+' and a.salesSeason like ' + @Season + @charenter IF @responsible IS NOT NULL SET @sql=@sql+' and m.responsible like ' + @responsible + @charenter IF @series IS NOT NULL SET @sql=@sql+' and a.series like ' + @series + @charenter IF @sex IS NOT NULL SET @sql=@sql+' and a.sex like '+ @sex + @charenter IF @goods_category IS NOT NULL SET @sql=@sql+' and h2.goods_category like ' + @goods_category + @charenter IF @Styleno IS NOT NULL SET @sql=@sql+' and a.Styleno like ' + @Styleno + @charenter IF @stylenametype IS NOT NULL SET @sql=@sql+' and a.stylenametype like ' + @stylenametype + @charenter IF @styletype IS NOT NULL SET @sql=@sql+' and a.styletype like ' + @styletype + @charenter IF @styletype2 IS NOT NULL SET @sql=@sql+' and a.styletype2 like ' + @styletype2 + @charenter IF @fabname IS NOT NULL SET @sql=@sql+' and a.fabname like ' + @fabname + @charenter IF @fabcharacter IS NOT NULL SET @sql=@sql+' and a.fabcharacter like ' + @fabcharacter + @charenter IF @Design_style IS NOT NULL SET @sql=@sql+' and a.Design_style like ' + @Design_style + @charenter IF @thickness IS NOT NULL SET @sql=@sql+' and a.thickness like ' + @thickness + @charenter IF @Collartype IS NOT NULL SET @sql=@sql+' and a.Collartype like ' + @Collartype + @charenter IF @Waist_type IS NOT NULL SET @sql=@sql+' and a.Waist_type like ' + @Waist_type + @charenter IF @BX_length IS NOT NULL SET @sql=@sql+' and a.BX_length like ' + @BX_length + @charenter IF @Pants_type IS NOT NULL SET @sql=@sql+' and a.Pants_type like ' + @Pants_type + @charenter IF @outsidesleeve IS NOT NULL SET @sql=@sql+' and a.outsidesleeve like ' + @outsidesleeve + @charenter IF @UnitpriceFrom IS NOT NULL SET @sql=@sql+' and a.Unitprice >= ' + @UnitpriceFrom + @charenter IF @UnitpriceTo IS NOT NULL SET @sql=@sql+' and a.Unitprice <= ' + @UnitpriceTo + @charenter --2018-02-27_add SET @sql=@sql + ' select * from AllData' +@charenter print @SQL exec(@SQL) end
在前端有些第三方控件，比如有些数据集控件不支持SQL的一些运算符(PIVOT等），如下示例：
SELECT responsible,Design_style,series,sex,[2001] as '针织',[2002] as '梭织',[2003] as '牛仔' ,[2004] as '毛织' FROM ( SELECT responsible,Design_style,series,sex ,case when fabname='针织' then 2001 WHEN fabname='梭织' then 2002 WHEN fabname='牛仔' then 2003 WHEN fabname='毛织' then 2004 end as fabname FROM AllData WHERE stylenametype LIKE '%外套类%' ) vip PIVOT ( COUNT(fabname) FOR fabname IN ([2001],[2002],[2003],[2004]) ) AS sFabname ORDER BY Design_style
再有些系统数据集（ADO相关控件）支持特殊运算符，但不能处理第三方插件的其他功能，比如上示例中'AllData'是别名而非实体表对象。那就有些功能就不能很好地实现了。此时就可以
利用后台来处理，把查询结果的数据集变为临时表(诸如AllData是实体表)来处理。
