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 中对参数日期的处理

1.(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’}2.to_char(“T_MRM_MRAPPLY”.FSTARTTIME,’YYYY-MM-DD’) =to_char({d ‘@time’},’YYYY-MM-DD’)都转成字符串进行比较3.截取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(扩展报表)

case when 的使用

1.

2.计数

格式化

日期

截取月日

小数位数

获取年龄

IN运算符

ksql 中对参数日期的处理

ksql日期范围查询

条件匹配

counta

斑马线背景色

Case条件判断

行隐藏

合并字符串 ARRAYTEXT

个人收藏笔记记录

case when 的使用

1.

2.计数

格式化

日期

截取月日

小数位数

获取年龄

IN运算符

ksql 中对参数日期的处理

ksql日期范围查询

条件匹配

counta

斑马线背景色

Case条件判断

行隐藏

合并字符串 ARRAYTEXT

个人收藏笔记记录

开通VIP