EAS_BI(扩展报表)
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函数只返回第一个符合条件的值，剩下的Case部分将会被自动忽略。
还有一个需要注意的问题，Case函数只返回第一个符合条件的值，剩下的Case部分将会被自动忽略。
--比如说，下面这段SQL，你永远无法得到“第二类”这个结果
col_1 ( , ) col_1 ()
1 select 2 case when PostLevel.fnumber = '01' and PERSONTECHPOST.fishightechnical = '1' and GWFL.fnumber = '0101' then 'g正高级' 3 when PostLevel.fnumber = '02' and PERSONTECHPOST.fishightechnical = '1' and GWFL.fnumber = '0101' then 'g高级' 4 when PostLevel.fnumber = '03' and PERSONTECHPOST.fishightechnical = '1' and GWFL.fnumber = '0101' then 'g中级' 5 when PostLevel.fnumber not in ('03','02','01') and PERSONTECHPOST.fishightechnical = '1' and GWFL.fnumber = '0101' then 'g初级以下' 6 when PostLevel.fnumber = '01' and PERSONTECHPOST.fishightechnical = '1' and GWFL.fnumber = '0102' then 'z正高级' 7 when PostLevel.fnumber = '02' and PERSONTECHPOST.fishightechnical = '1' and GWFL.fnumber = '0102' then 'z高级' 8 when PostLevel.fnumber = '03' and PERSONTECHPOST.fishightechnical = '1' and GWFL.fnumber = '0102' then 'z中级' 9 when PostLevel.fnumber not in ('03','02','01') and PERSONTECHPOST.fishightechnical = '1' and GWFL.fnumber = '0102' then 'z初级以下' 10 when PostLevel.fnumber = '01' and PERSONTECHPOST.fishightechnical = '1' and GWFL.fnumber not in ('0102','0101') then 'y正高级' 11 when PostLevel.fnumber = '02' and PERSONTECHPOST.fishightechnical = '1' and GWFL.fnumber not in ('0102','0101') then 'y高级' 12 when PostLevel.fnumber = '03' and PERSONTECHPOST.fishightechnical = '1' and GWFL.fnumber not in ('0102','0101') then 'y中级' 13 when PERSONTECHPOST.fishightechnical = '0' and ftechnicalpostid is not null then '职称重复' 14 else 'y初级以下' 15 end job, 16 count(*) number 17 from T_BD_PERSON as PERSON 18 left join T_ORG_POSITIONMEMBER as POSITIONMEMBER on PERSON.fid = POSITIONMEMBER.fpersonid 19 left join T_ORG_POSITION as POSITION on POSITION.fid = POSITIONMEMBER.fpositionid 20 21 left join T_HR_BDEMPLOYEETYPE as BDEMPLOYEETYPE on BDEMPLOYEETYPE.fid = PERSON.femployeetypeid 22 left join T_BD_EmployeeModle as EmployeeModle on EmployeeModle.fid = BDEMPLOYEETYPE.femployeemodleid 23 left join ct_mp_gwfl as GWFL on GWFL.fid = POSITION.cfgwflid 24 left join T_ORG_BaseUnit as BaseUnit on BaseUnit.fid = POSITION.fadminorgunitid 25 left join T_HR_PERSONTECHPOST as PERSONTECHPOST on PERSON.fid = PERSONTECHPOST.fpersonid 26 left join T_HR_BDTechPostLevel as PostLevel on PERSONTECHPOST.cflevelid = PostLevel.fid 27 left join T_HR_PersonPosition as PersonPosition on PERSON.fid = PersonPosition.fpersonid 28 where POSITIONMEMBER.fisprimary = '1'and EmployeeModle.fnumber = 01 and BaseUnit.FLONGNUMBER like '%@orgnum%' 29 and PersonPosition.fenterdate <= {ts '@date'} and PERSONTECHPOST.fconferdate <= {ts '@date'} 30 group by 31 case when PostLevel.fnumber = '01' and PERSONTECHPOST.fishightechnical = '1' and GWFL.fnumber = '0101' then 'g正高级' 32 when PostLevel.fnumber = '02' and PERSONTECHPOST.fishightechnical = '1' and GWFL.fnumber = '0101' then 'g高级' 33 when PostLevel.fnumber = '03' and PERSONTECHPOST.fishightechnical = '1' and GWFL.fnumber = '0101' then 'g中级' 34 when PostLevel.fnumber not in ('03','02','01') and PERSONTECHPOST.fishightechnical = '1' and GWFL.fnumber = '0101' then 'g初级以下' 35 when PostLevel.fnumber = '01' and PERSONTECHPOST.fishightechnical = '1' and GWFL.fnumber = '0102' then 'z正高级' 36 when PostLevel.fnumber = '02' and PERSONTECHPOST.fishightechnical = '1' and GWFL.fnumber = '0102' then 'z高级' 37 when PostLevel.fnumber = '03' and PERSONTECHPOST.fishightechnical = '1' and GWFL.fnumber = '0102' then 'z中级' 38 when PostLevel.fnumber not in ('03','02','01') and PERSONTECHPOST.fishightechnical = '1' and GWFL.fnumber = '0102' then 'z初级以下' 39 when PostLevel.fnumber = '01' and PERSONTECHPOST.fishightechnical = '1' and GWFL.fnumber not in ('0102','0101') then 'y正高级' 40 when PostLevel.fnumber = '02' and PERSONTECHPOST.fishightechnical = '1' and GWFL.fnumber not in ('0102','0101') then 'y高级' 41 when PostLevel.fnumber = '03' and PERSONTECHPOST.fishightechnical = '1' and GWFL.fnumber not in ('0102','0101') then 'y中级' 42 when PERSONTECHPOST.fishightechnical = '0' and ftechnicalpostid is not null then '职称重复' 43 else 'y初级以下' 44 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)
)
)
)
