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搜索函数。 
 sex          sex =    sex =

这两种方式，可以实现相同的功能。简单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
View Code

格式化

日期

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)
        )
    )
)
 
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/lyc-smile/p/7489735.html