在数据库中行转列，再拼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 

 

转载于:https://www.cnblogs.com/kaifang1028/p/8350881.html