case when 的使用

1.

扩展报表，一张收费单据中，下面分为分录

问题描述：收费单中有一个分录用于记录检测的项目名称以及标准费用。收费单有自己的主键，分录中的外键即是收费单的主键，然后分录表总的每一项检测项目有自己的id。每一项指标的费用显示在各自的下面。

SELECT detail.resource, detail.producename, SUM(mdje), SUM(wdje), SUM(zlje), SUM(yhlje)

FROM

(

SELECT master.resource, master.producename,

(CASE WHEN fenlu.item = '密度' THEN fenlu.fee ELSE 0 END) AS mdje,

(CASE WHEN fenlu.item = '温度' THEN fenlu.fee ELSE 0 END) AS wdje,

(CASE WHEN fenlu.item = '质量' THEN fenlu.fee ELSE 0 END) AS zlje,

(CASE WHEN fenlu.item = '氧含量' THEN fenlu.fee ELSE 0 END) AS yhlje

FROM changesheet AS MASTER LEFT JOIN fenlu ON fenlu.sheetid = master.id

) AS detail

GROUP BY detail.resource, detail.producename

2.计数

Case具有两种格式。简单Case函数和Case搜索函数。

 sex          sex =    sex =

来源： http://blog.itpub.net/26451903/viewspace-733526

这两种方式，可以实现相同的功能。简单Case函数的写法相对比较简洁，但是和Case搜索函数相比，功能方面会有些限制，比如写判断式。
还有一个需要注意的问题，Case函数只返回第一个符合条件的值，剩下的Case部分将会被自动忽略。

--比如说，下面这段SQL，你永远无法得到“第二类”这个结果

  col_1  ( , )   col_1  ()

 select

     case when PostLevel.fnumber = '' and PERSONTECHPOST.fishightechnical = '' and GWFL.fnumber = '' then 'g正高级'

             when PostLevel.fnumber = '' and PERSONTECHPOST.fishightechnical = ''  and GWFL.fnumber = ''  then 'g高级'

             when PostLevel.fnumber = '' and PERSONTECHPOST.fishightechnical = ''  and GWFL.fnumber = '' then 'g中级'

             when PostLevel.fnumber not in ('','','') and PERSONTECHPOST.fishightechnical = ''  and GWFL.fnumber = '' then 'g初级以下'

             when PostLevel.fnumber = '' and PERSONTECHPOST.fishightechnical = ''  and GWFL.fnumber = '' then 'z正高级'

             when PostLevel.fnumber = '' and PERSONTECHPOST.fishightechnical = ''  and GWFL.fnumber = '' then 'z高级'

             when PostLevel.fnumber = '' and PERSONTECHPOST.fishightechnical = ''  and GWFL.fnumber = '' then 'z中级'

             when PostLevel.fnumber not in ('','','') and PERSONTECHPOST.fishightechnical = ''  and GWFL.fnumber = '' then 'z初级以下'

             when PostLevel.fnumber = '' and PERSONTECHPOST.fishightechnical = ''  and GWFL.fnumber not in ('','') then 'y正高级'

             when PostLevel.fnumber = '' and PERSONTECHPOST.fishightechnical = ''  and GWFL.fnumber not in ('','') then 'y高级'

             when PostLevel.fnumber = '' and PERSONTECHPOST.fishightechnical = ''  and GWFL.fnumber not in ('','') then 'y中级'

             when PERSONTECHPOST.fishightechnical = '' and ftechnicalpostid is not null then '职称重复'

             else 'y初级以下'

             end job,

     count(*) number

 from T_BD_PERSON as PERSON

     left join T_ORG_POSITIONMEMBER as POSITIONMEMBER on PERSON.fid = POSITIONMEMBER.fpersonid

     left join T_ORG_POSITION as POSITION on POSITION.fid = POSITIONMEMBER.fpositionid

     left join T_HR_BDEMPLOYEETYPE as BDEMPLOYEETYPE on BDEMPLOYEETYPE.fid = PERSON.femployeetypeid

     left join T_BD_EmployeeModle  as EmployeeModle on EmployeeModle.fid = BDEMPLOYEETYPE.femployeemodleid

     left join ct_mp_gwfl as GWFL on GWFL.fid = POSITION.cfgwflid

     left join T_ORG_BaseUnit as BaseUnit on BaseUnit.fid = POSITION.fadminorgunitid

     left join T_HR_PERSONTECHPOST as PERSONTECHPOST  on PERSON.fid = PERSONTECHPOST.fpersonid

     left join T_HR_BDTechPostLevel as PostLevel on PERSONTECHPOST.cflevelid = PostLevel.fid

     left join T_HR_PersonPosition  as PersonPosition  on PERSON.fid = PersonPosition.fpersonid

 where POSITIONMEMBER.fisprimary = ''and EmployeeModle.fnumber = 01  and  BaseUnit.FLONGNUMBER like '%@orgnum%'

 and PersonPosition.fenterdate <= {ts '@date'} and PERSONTECHPOST.fconferdate <= {ts '@date'}

 group by

     case when PostLevel.fnumber = '' and PERSONTECHPOST.fishightechnical = '' and GWFL.fnumber = '' then 'g正高级'

             when PostLevel.fnumber = '' and PERSONTECHPOST.fishightechnical = ''  and GWFL.fnumber = ''  then 'g高级'

             when PostLevel.fnumber = '' and PERSONTECHPOST.fishightechnical = ''  and GWFL.fnumber = '' then 'g中级'

             when PostLevel.fnumber not in ('','','') and PERSONTECHPOST.fishightechnical = ''  and GWFL.fnumber = '' then 'g初级以下'

             when PostLevel.fnumber = '' and PERSONTECHPOST.fishightechnical = ''  and GWFL.fnumber = '' then 'z正高级'

             when PostLevel.fnumber = '' and PERSONTECHPOST.fishightechnical = ''  and GWFL.fnumber = '' then 'z高级'

             when PostLevel.fnumber = '' and PERSONTECHPOST.fishightechnical = ''  and GWFL.fnumber = '' then 'z中级'

             when PostLevel.fnumber not in ('','','') and PERSONTECHPOST.fishightechnical = ''  and GWFL.fnumber = '' then 'z初级以下'

             when PostLevel.fnumber = '' and PERSONTECHPOST.fishightechnical = ''  and GWFL.fnumber not in ('','') then 'y正高级'

             when PostLevel.fnumber = '' and PERSONTECHPOST.fishightechnical = ''  and GWFL.fnumber not in ('','') then 'y高级'

             when PostLevel.fnumber = '' and PERSONTECHPOST.fishightechnical = ''  and GWFL.fnumber not in ('','') then 'y中级'

             when PERSONTECHPOST.fishightechnical = '' and ftechnicalpostid is not null then '职称重复'

             else 'y初级以下'

             end

格式化

日期

convert(navarchar(10),登录时间，120) 2016-03-15 00:00:00:000 ->2016-03-15

截取月日

right(to_char(T_WFR_ASSIGN.fcreatedtime,'yyyy-MM-dd'),5) as creatime,

小数位数

convert(decimal(10,2),平均分数) 87.57800 -> 87.58 保留两位小数

获取年龄

datediff(yy,"PERSON".fbirthday,{ts'@date'}) < 29

IN运算符

需要查询多个条件中满足一个条件的数据，可以使用or运算符，但是对于有较多的条件来说，使用or运算符并不方便，可以使用in代替。

select * from 省市表 where 省份=‘吉林省’ or 省份=‘辽宁省’ or 省份=‘黑龙江省’

-》select * from 省市表 where 省份 in ('吉林省'，‘辽宁省’，‘黑龙江省’)

实际应用：

多期间：多表连接，多期间

想要的效果就是 2015年3月以及 2016年4月份的数据

eg: b203.year in (2015, 2016) and b203.month in(3, 4) and b204.year in (2015, 2016) and b204.month in(3, 4)

这种情况就会出现，20153 20154 20163 20164 并不符合要求！

应该使用下面这种：

and (B203.cfyear||B203.cfmonth) in (20163,20165)

and (B2041.cfyear||B2041.cfmonth) = (B203.cfyear||B203.cfmonth)

多表才会出现同期间数据！

ksql 中对参数日期的处理

(case when "GWFL".fname_l2 = '管理类-高层管理岗' then datediff(yy,"PERSON".fbirthday,{ts'@date'}) end) as "高层管理年龄",

KSQL中用日期常量必须用{ts'" + dateTime.ToString("yyyy-M-d HH:mm:ss") + "'} 正确写法

{ts'1091-1-1'}就代表单引号里的值是日期常量

使用形式：{ts'@date'}

to_char("T_MRM_MRAPPLY".FSTARTTIME,'YYYY-MM-DD') =to_char({d '@time'},'YYYY-MM-DD')

都转成字符串进行比较

截取

to_number(substring(@intPeriod,0,4))

4. ksql 对文本日期转化 {ts'date'}

and cfreaddate >={ts'2018-01-20'} and cfreaddate<={ts'2018-04-20'}

ksql日期范围查询

ksql：参数日期空间中sql语法

where "PROPOSERORG".flongnumber like ('@orgNumber%')

and "MATERIALRECEIVEDBILL".FBizDate >= {d '@startDate' }

and "MATERIALRECEIVEDBILL".FBizDate <= {d '@endDate' }

and "PROPOSER".fid in ('@user')

条件匹配

IF

(

    COUNTA

    (

        SELECT

        (

            分配单, LEADERNAME, , AND(FBIZOBJID=A3, LEADERNAME=H2)

        )

    ) = 0,

    " ", "△"

)

counta

　　对目标进行非空值计数。

　　如果不需要统计逻辑值、文字或错误值，使用COUNT函数。

最终效果图：

=IF(COUNTA(SELECT(分配单, LEADERNAME, , AND(FBIZOBJID = A3, LEADERNAME = H2))) = 0, " ", "△")

斑马线背景色

IF

(

    MOD(ROW(), 2) = 1, RGB(213, 217, 211), RGB(255, 255, 255)

)

Case条件判断

{

    CASE

    (

        FIELD(天津天保控股贷款情况一览表_LYC, 担保方式) = 1, "保证",

        FIELD(天津天保控股贷款情况一览表_LYC, 担保方式) = 2, "抵押",

        FIELD(天津天保控股贷款情况一览表_LYC, 担保方式) = 3, "质押",

        FIELD(天津天保控股贷款情况一览表_LYC, 担保方式) = 4, "信用",

        FIELD(天津天保控股贷款情况一览表_LYC, 担保方式) = 5, "其它",

        FIELD(天津天保控股贷款情况一览表_LYC, 担保方式) = 6, "混合",

        FIELD(天津天保控股贷款情况一览表_LYC, 担保方式) = 7, "无担保",

        ""

    )

}

行隐藏

--行隐藏

IF

(

    VALUE(M2) = 0, True, False

)

合并字符串 ARRAYTEXT

ARRAYTEXT({",", "", "", True, True}, EXS(B2))

ARRAYTEXT

(

    {",", "", "", True, True}, O2, P2, Q2

)

IF

(

    SELECT1

    (

        天津天保控股贷款情况一览表_担保抵押, 担保单位, AND(FPARENTBILLID=E2, 方式=1)

    ) = Null,

    "",

    ARRAYTEXT

    (

        {"", "担保人：", "", True, True},

        SELECT1

        (

            天津天保控股贷款情况一览表_担保抵押, 担保单位, AND(FPARENTBILLID=E2, 方式=1)

        )

    )

)

巴特西

EAS_BI(扩展报表)