在数据库中行转列,再拼where条件
ALTER procedure [dbo].[ppSM_GetNJinfo] @fcpsern varchar(50), --成品条码 @fline varchar(50), --线体 @forder varchar(50), --订单号 @fzyh varchar(50), --专用号 @fexp varchar(50), --型号 @fworkDateFrom DATETIME, --开始时间 @fworkDateTo DATETIME --结束时间 as DECLARE @fstrSql varchar(max); BEGIN SET @fstrSql=''; --------------------------------------------------- IF(@fcpsern<>'') BEGIN SET @fcpsern=QUOTENAME(@fcpsern,''''); SET @fstrSql=' where sern ='+@fcpsern; end ------------------------------------------------- IF(@fline<>'') BEGIN SET @fline=QUOTENAME(@fline,''''); IF @fstrSql <>'' BEGIN SET @fstrSql=@fstrSql+' and linename ='+@fline; END ELSE BEGIN SET @fstrSql=' where linename ='+@fline; END END ----------------------------------------------------- IF(@forder<>'') BEGIN SET @forder=QUOTENAME(@forder,''''); IF @fstrSql <>'' BEGIN SET @fstrSql=@fstrSql+' and orderNum ='+@forder; END ELSE BEGIN SET @fstrSql=' where orderNum ='+@forder; END END ---------------------------------------------------- IF(@fzyh<>'') BEGIN SET @fzyh=QUOTENAME(@fzyh,''''); IF @fstrSql <>'' BEGIN SET @fstrSql=@fstrSql+' and zyh ='+@fzyh; END ELSE BEGIN SET @fstrSql=' where zyh ='+@fzyh; END END -------------------------------------------------- IF(@fexp<>'') BEGIN --SET @fexp=QUOTENAME(@fexp,''''); IF @fstrSql <>'' BEGIN SET @fstrSql=@fstrSql+' and wuliaoDescribe like'+''''+'%'+@fexp+'%'''; END ELSE BEGIN SET @fstrSql=' where wuliaoDescribe like'+''''+'%'+@fexp+'%'''; END END ---------------------------------------------------- SELECT sern,linename,orderNum,zyh,wuliaoDescribe,workdate,banciCode, isnull(MAX(NDJ),0) AS NDJ, isnull(MAX(NDK),0) AS NDK, isnull(MAX(NZFQ),0) AS NZFQ, isnull(MAX(NSMS),0) AS NSMS, isnull(MAX(NYKQ),0) AS NYKQ, isnull(MAX(NNXBS),0) AS NNXBS, isnull(MAX(NZX),0) AS NZXM INTO #T FROM (SELECT sern,linename,orderNum,zyh,wuliaoDescribe, CONVERT(varchar(100), createDate, 23) AS workdate ,banciCode, CASE pplace1 WHEN '电机' THEN bujianzyh1 ELSE '' END AS 'NDJ',------- CASE pplace1 WHEN '说明书' THEN bujianzyh1 ELSE '' END AS 'NSMS', CASE pplace1 WHEN '内纸箱' THEN bujianzyh1 ELSE '' END AS 'NZX', CASE pplace2 WHEN '电控' THEN bujianzyh2 ELSE '' END AS 'NDK',-------- CASE pplace2 WHEN '遥控器' THEN bujianzyh2 ELSE '' END AS 'NYKQ', CASE pplace3 WHEN '蒸发器' THEN bujianzyh3 ELSE '' END AS 'NZFQ',-------- CASE pplace3 WHEN '能效标识' THEN bujianzyh3 ELSE '' END AS 'NNXBS'-------- FROM dbo.g_chengpinSM_NJInfo WHERE CONVERT(varchar(10), createDate, 23) >=CONVERT(varchar(10),@fworkDateFrom, 23) AND CONVERT(varchar(10), createDate, 23)<=CONVERT(varchar(10),@fworkDateTo, 23) GROUP BY sern,linename,orderNum,zyh,wuliaoDescribe ,CONVERT(varchar(100), createDate, 23),bujianzyh1,bujianzyh2,bujianzyh3,pplace1,pplace2,pplace3,banciCode ) T GROUP BY sern,linename,orderNum,zyh,wuliaoDescribe,workdate,banciCode EXEC ('select *from #T'+@fstrSql) DROP TABLE #T end
还有一种在数据库加where条件的方法:
select a.* from dbo.pp_order a where (a.workdate between CONVERT(DATETIME,@fdatebegin,120) and CONVERT(DATETIME,@fdateend,120) ) and a.ln_xt =@fline and(case when @order='' then 1 else CHARINDEX(@order,a.order_sern) end)>0 and(case when @zyh='' then 1 else CHARINDEX(@zyh,a.zyh) end)>0 and(case when @fexp='' then 1 else CHARINDEX(@fexp,a.exp_pcode) end)>0 and(case when @fsale_kind='' then 1 else CHARINDEX(@fsale_kind,a.sale_kind) end)>0 order by a.linename,a.workdate,a.order_sern