CASE 根据条件 筛选

 

返回结果

 select [Id] CkeckID,'' [atypeId],'' [btypeId],@etypeid [etypeId],@szKTypeID [ktypeId],[sltypeId],'' [ktypeId2],stock.Ptypeid [PtypeId],
        abs(stockQty - CheckedNumber) [Qty],
        CASE WHEN (stockpgholqty - checkpgholqty > 0) THEN stockpgholqty - checkpgholqty ELSE 0 END SideQty,
        1 [discount],dbo.f_CovTotalDivQty(total,stockQty) [discountprice],
        dbo.f_covtoTotal(abs(stockQty - CheckedNumber) * dbo.f_TotalDivQty(total,stockQty)) [costtotal],
        dbo.f_CovTotalDivQty(total,stockQty) [costPrice],JobNumber [blockno],goodsorderID [goodsorderID],
            dbo.f_CovTotalDivQty(total,stockQty) [price],dbo.f_covtoTotal(abs(stockQty - CheckedNumber) * dbo.f_TotalDivQty(total,stockQty)) [total],
            stock.outFactoryDate [Prodate],stock.UsefulEndDate,dbo.f_CovTotalDivQty(total,stockQty) [TaxPrice],
            dbo.f_covtoTotal(abs(stockQty - CheckedNumber) * dbo.f_TotalDivQty(total,stockQty)) [TaxTotal],'' [comment],@szDate [date],1 [usedtype],0 [period],
            dbo.f_covtoTotal(abs(stockQty - CheckedNumber) * dbo.f_TotalDivQty(total,stockQty)) [tax_total],0 [tax],
            dbo.f_covtoTotal(abs(stockQty - CheckedNumber) * dbo.f_TotalDivQty(total,stockQty)) [discounttotal],abs(stockQty - CheckedNumber) AssQty,
            dbo.f_CovTotalDivQty(total,stockQty) AssPrice,dbo.f_CovTotalDivQty(total,stockQty) AssDiscountPrice,dbo.f_CovTotalDivQty(total,stockQty) AssTaxPrice,
            up.URate UnitRate,p.costmode [costmode],@vchtype [vchtype],'F' [redword],up.ucode [unit],0 [OrderCode],0 [OrderDlyCode],0 [InvoceTotal],
            '' [DeptID],up.prePrice4 [RetailPrice],0 [HandZeroCost],0 [Pstutas],0 [VipCardId],0 [PromoVchCode], 
            0 FeeTotal,0 InPrice,0 InTotal,pgholqty pgholqty,ABS(stockpgholqty - checkpgholqty) pgholInqty,
            CASE WHEN CHARINDEX(',',up.barcode) > 0 THEN SUBSTRING(up.barcode,1,CHARINDEX(',',up.barcode) - 1) ELSE up.barcode END barcode,
            GoodsBatchID
     from  #StockTmp stock,ptype p,dbo.fn_GetUnitPrice('',0) up 

 

 查询条件 

SELECT

    loan.*, loan_phase.plannedTermAmount
FROM
    loan
LEFT JOIN loan_phase ON loan_phase.loanId = loan.loanId
WHERE
 (CASE loan.status when 520 then 1=1 else 'loan_phase.phaseNumber=loan.repayedTermCount+1' end)
 AND 
    loan.borrowerId = 100588
AND (
    loan. STATUS = 500
    OR loan. STATUS = 550
    OR loan. STATUS = 520
)
ORDER BY
    loan.createTime DESC

 

      SELECT p.ptypeid,pgs.cltypeid,pgs.sltypeid,pgorder,pgs.IsUsed, dbo.f_CovToQty(cast(pgDetail as NUMERIC(22,10)) * cast(pgqty as NUMERIC(22,10))) qty ,pgDetail,pgs.opgDetail, Iscut,pgIDCode,pgBarCode,pgComment,ISNULL(clfullname,'') clfullname,
                ISNULL(d.Number,'') Number,ISNULL(d.[DATE],'') Date,ISNULL(d.Vchcode,0) Vchcode,ISNULL(d.Vchtype,0) Vchtype,pgs.def1,pgs.def2,pgs.def3,pgs.def4,pgs.def5   FROM  dbo.pggoodsidcodes pgs
              LEFT JOIN FN_PTYPEALL(@Ptypeid,'N')  p ON p.ptypeid=pgs.ptypeid
              LEFT JOIN dbo.fn_GetKtypeTypeid(@Operator) K  ON K.ktypeid = pgs.ktypeid 
              LEFT JOIN dbo.ClType cl ON  cl.Cltypeid=pgs.cltypeid LEFT JOIN dbo.Dlyndx d ON d.Vchcode=pgs.ovchcode 
              WHERE pgs.ptypeid= @Ptypeid    and  pgs.ktypeid like @Ktypeid and pgs.cltypeid like @cltypeid AND pgs.SLtypeid LIKE @Sltypeid
              AND pgs.goodsorderid = CASE WHEN @nCostmode = 0 AND @nPjobMancode = 0 THEN pgs.goodsorderid else @ngoodsorderid END  AND pgDetail= @pgDetail AND Iscut = @Iscut AND pgqty <> 0 AND (IsUsed = 0 OR pgs.IsUsed=@vchcode ) 
              and d.Date<=(CASE when pgs.ovchcode=0 OR @enddate='' THEN  d.Date ELSE @enddate end)
                 AND(((@isLock=1) AND pgs.pgorder NOT in (SELECT pgsorder FROM dbo.pgDlyLock))OR ((@isLock=0) AND 1=1))
               ORDER BY pgorder

 

 where 条件 不使用cese

    CREATE TABLE #pgStocks(ID int IDENTITY,pgDetail NUMERIC(22,10),pgQty NUMERIC(22,10),IsCut int,goodsorderid INT)            
                INSERT INTO #pgStocks( pgDetail, pgQty,IsCut,goodsorderid )            
                        SELECT  CAST(pb.col AS NUMERIC(22,10)) pgDetail, COUNT( CAST(pb.col AS NUMERIC(22,10))) pgqty,( CASE  when @nvctype in(17,21) and (@IsSaveCode = 0) THEN  0 ELSE IsCut end) IsCut,goodsorderid FROM dbo.f_splitSTR(@CodeStr,@splitstr) pc
                    LEFT JOIN dbo.f_splitSTR(@QtyStr,@splitstr) pb ON pc.ID = pb.ID                    
                    LEFT JOIN dbo.pggoodsidcodes pg ON pc.col=pg.pgIDCode
                     WHERE (@nvctype=16  and goodsorderid = @ngoodsorderid ) OR (@nvctype!=16 AND 1=1)                    
                    GROUP BY CAST(pb.col AS NUMERIC(22,10)),pg.IsCut,goodsorderid    

 

条件统计

SELECT  SUM(pggoodsidcodes.PgDetail) sumQty,COUNT(1)Stockpgholqty ,SUM( CASE WHEN pggoodsidcodes.pgDetail = dbo.ptype.pgholunitrate THEN  1 ELSE 0 END) pp  FROM dbo.pggoodsidcodes  
                        LEFT JOIN tmp_PgChecked    ON pggoodsidcodes.pgBarCode=tmp_PgChecked.PgBarCode
                        LEFT JOIN Ptype on pggoodsidcodes.PtypeId=pggoodsidcodes.PtypeId　　　　WHERE       pggoodsidcodes.pgDetail<>0     AND IsUsed=0 AND tmp_PgChecked.ptypeid IS NULL