存储里case when用法和and 后加条件

最新推荐文章于 2024-08-06 12:05:05 发布

nimeide1234567890

最新推荐文章于 2024-08-06 12:05:05 发布

阅读量7.2k

点赞数

分类专栏：龙卷风之感悟篇文章标签：存储 join null c

本文链接：https://blog.csdn.net/luojianfeng/article/details/1551564

版权

龙卷风之感悟篇专栏收录该内容

52 篇文章 0 订阅

订阅专栏

存储问题：

case q9 when 0 then '+''''+'新單'+''''+' when 1 then '+''''+'再協商'+''''+' end as jb

假如我要换成　q9<=100 && q9>=85　的判断怎么改？

方法一：

set @strSql = 'select case when (q9<=100 and q9>=85) then '+''''+'A'+''''+' when (q9<=84 and q9>=70) then '+''''+'B'+''''+' when (q9<=69 and q9>=55) then '+''''+'C' +''''+' when (q9<=54 and q9>=40) then '+''''+'D'+''''+' when (q9<40) then '+''''+'E'+''''+' end as jb,* from jdkh'

方法二：

case when q9 between 85 and 100 then '+''''+'新單'+''''+' when 1 then '+''''+'再協商'+''''+' end as jb

-------------------------

case when用法和and 后加条件
SELECT A.OrderSID as InvId,
       E.BizInvType as TypeCode,
       E.TypeName as TypeName,
       B.SimpleName as CorpName,
       C.StorName as StorName,
       CASE
         WHEN F_BS001QRYIONOTICE.UnitLevel_ = '1' THEN
          -A.TotalDefQty / 250
         WHEN F_BS001QRYIONOTICE.UnitLevel_ = '2' THEN
          -A.TotalDefQty / 0
         WHEN F_BS001QRYIONOTICE.UnitLevel_ = '3' THEN
          -A.TotalDefQty
         WHEN F_BS001QRYIONOTICE.UnitLevel_ = '4' THEN
          -A.TotalDefQty * 10
         WHEN F_BS001QRYIONOTICE.UnitLevel_ = '5' THEN
          -A.TotalDefQty * 200
         ELSE
          -A.TotalDefQty
       END as TotalDefQty,
       A.DetailCount,
       D.PersonName,
       A.InputTime,
       A.Remark,
       F.Enterprise as Sourcename
FROM B_OD_OrderMaster A
INNER JOIN G_OG_CorpInfo B ON A.CorpSID = B.CorpSID
INNER JOIN B_ST_BSRealStorAttr C ON A.RealStorSID = C.StorSID
INNER JOIN G_OG_PersonInfo D ON D.PersonSID = A.InputOperSID
INNER JOIN G_PB_BizInvType E ON E.BizInvType = '02'
LEFT JOIN G_CS_CUSTOMER F ON A.CUSTOMERSID = F.CUSTOMERSID

WHERE A.IsActive = '1'
   AND A.IsReturn = '1'
   AND A.IsStorAudit = '0'
   AND A.IsBizAudit = '0'
   AND SUBSTR(A.InputTime, 1, 8) >= F_BS001QRYIONOTICE.BeginDate_

   AND SUBSTR(A.InputTime, 1, 8) <= F_BS001QRYIONOTICE.EndDate_

   AND A.CorpSID = (CASE WHEN F_BS001QRYIONOTICE.CorpSID_ = '' OR
        F_BS001QRYIONOTICE.CorpSID_ IS NULL THEN A.CorpSID ELSE
        F_BS001QRYIONOTICE.CorpSID_ END)

   AND A.RealStorSID = (CASE WHEN F_BS001QRYIONOTICE.StorSID_ = '' OR
        F_BS001QRYIONOTICE.StorSID_ IS NULL THEN
        A.RealStorSID ELSE F_BS001QRYIONOTICE.StorSID_ END)

   AND E.IsInStor =
       (CASE WHEN F_BS001QRYIONOTICE.State_ = '1' THEN '1' ELSE E.IsInStor END)

   AND E.IsOutStor =
       (CASE WHEN F_BS001QRYIONOTICE.State_ = '2' THEN '1' ELSE E.IsOutStor END)