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 =

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

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

 

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

  col_1  ( , )   col_1  ()

select
    case when PostLevel.fnumber = '01' and PERSONTECHPOST.fishightechnical = '1' and GWFL.fnumber = '0101' then 'g正高级'
            when PostLevel.fnumber = '02' and PERSONTECHPOST.fishightechnical = '1'  and GWFL.fnumber = '0101'  then 'g高级'
            when PostLevel.fnumber = '03' and PERSONTECHPOST.fishightechnical = '1'  and GWFL.fnumber = '0101' then 'g中级'
            when PostLevel.fnumber not in ('03','02','01') and PERSONTECHPOST.fishightechnical = '1'  and GWFL.fnumber = '0101' then 'g初级以下'
            when PostLevel.fnumber = '01' and PERSONTECHPOST.fishightechnical = '1'  and GWFL.fnumber = '0102' then 'z正高级'
            when PostLevel.fnumber = '02' and PERSONTECHPOST.fishightechnical = '1'  and GWFL.fnumber = '0102' then 'z高级'
            when PostLevel.fnumber = '03' and PERSONTECHPOST.fishightechnical = '1'  and GWFL.fnumber = '0102' then 'z中级'
            when PostLevel.fnumber not in ('03','02','01') and PERSONTECHPOST.fishightechnical = '1'  and GWFL.fnumber = '0102' then 'z初级以下'
            when PostLevel.fnumber = '01' and PERSONTECHPOST.fishightechnical = '1'  and GWFL.fnumber not in ('0102','0101') then 'y正高级'
            when PostLevel.fnumber = '02' and PERSONTECHPOST.fishightechnical = '1'  and GWFL.fnumber not in ('0102','0101') then 'y高级'
            when PostLevel.fnumber = '03' and PERSONTECHPOST.fishightechnical = '1'  and GWFL.fnumber not in ('0102','0101') then 'y中级'
            when PERSONTECHPOST.fishightechnical = '0' 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 = '1'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 = '01' and PERSONTECHPOST.fishightechnical = '1' and GWFL.fnumber = '0101' then 'g正高级'
            when PostLevel.fnumber = '02' and PERSONTECHPOST.fishightechnical = '1'  and GWFL.fnumber = '0101'  then 'g高级'
            when PostLevel.fnumber = '03' and PERSONTECHPOST.fishightechnical = '1'  and GWFL.fnumber = '0101' then 'g中级'
            when PostLevel.fnumber not in ('03','02','01') and PERSONTECHPOST.fishightechnical = '1'  and GWFL.fnumber = '0101' then 'g初级以下'
            when PostLevel.fnumber = '01' and PERSONTECHPOST.fishightechnical = '1'  and GWFL.fnumber = '0102' then 'z正高级'
            when PostLevel.fnumber = '02' and PERSONTECHPOST.fishightechnical = '1'  and GWFL.fnumber = '0102' then 'z高级'
            when PostLevel.fnumber = '03' and PERSONTECHPOST.fishightechnical = '1'  and GWFL.fnumber = '0102' then 'z中级'
            when PostLevel.fnumber not in ('03','02','01') and PERSONTECHPOST.fishightechnical = '1'  and GWFL.fnumber = '0102' then 'z初级以下'
            when PostLevel.fnumber = '01' and PERSONTECHPOST.fishightechnical = '1'  and GWFL.fnumber not in ('0102','0101') then 'y正高级'
            when PostLevel.fnumber = '02' and PERSONTECHPOST.fishightechnical = '1'  and GWFL.fnumber not in ('0102','0101') then 'y高级'
            when PostLevel.fnumber = '03' and PERSONTECHPOST.fishightechnical = '1'  and GWFL.fnumber not in ('0102','0101') then 'y中级'
            when PERSONTECHPOST.fishightechnical = '0' 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)
        )
    )
)