APEX开发过程中需要注意的小细节6

最新推荐文章于 2024-05-30 14:59:09 发布

王小小鸭

最新推荐文章于 2024-05-30 14:59:09 发布

阅读量603

点赞数 13

本文链接：https://blog.csdn.net/clover_oreo/article/details/137865113

版权

oracle apex 同时被 3 个专栏收录

43 篇文章 1 订阅

订阅专栏

PL/SQL

41 篇文章 0 订阅

订阅专栏

oracle

26 篇文章 1 订阅

订阅专栏

【展示1.0】合到一个项

【展示2.0】按星级划分

【展示3.0】拼接分号

【展示4.0 】按技能筛选

从技能维度找人，再从人的维度展示对应技能

【展示5.0】技能+星级筛选

select NAME,
       EMP_ID,
       TEAM,
       BASE_NAME,
       TITLE,
       ROLES,
       nvl(listagg(score1, ','), '无') s1,
       nvl(listagg(score2, ','), '无') s2,
       nvl(listagg(score3, ','), '无') s3,
       nvl(listagg(score4, ','), '无') s4,
       nvl(listagg(score5, ','), '无') s5
from (
         select B.NAME,
                B.EMP_ID,
                B.TEAM,
                B.BASE_NAME,
                B.TITLE,
                B.ROLES,
                A.SCORE,
--        C.SKILL_NAME,
                case
                    when SCORE = 1 then
                        LISTAGG(C.SKILL_NAME ||  ';', CHR(10)) WITHIN GROUP (ORDER BY SCORE)
                    else
                        null end as score1,
                case
                    when SCORE = 2 then
                        LISTAGG(C.SKILL_NAME ||  ';', CHR(10)) WITHIN GROUP (ORDER BY SCORE)
                    else
                        null end as score2,
                case
                    when SCORE = 3 then
                        LISTAGG(C.SKILL_NAME ||  ';', CHR(10)) WITHIN GROUP (ORDER BY SCORE)
                    else
                        null end as score3,
                case
                    when SCORE = 4 then
                        LISTAGG(C.SKILL_NAME ||  ';', CHR(10)) WITHIN GROUP (ORDER BY SCORE)
                    else
                        null end as score4,
                case
                    when SCORE = 5 then
                        LISTAGG(C.SKILL_NAME ||  ';', CHR(10)) WITHIN GROUP (ORDER BY SCORE)
                    else
                        null end as score5
         from JAS_EMP_SKILL_SCORE_NEW A
                  join JAS_IT_EMP B on B.EMP_ID = A.EMP_CODE and B.IS_LEAVE = 0
                  join JAS_EMP_SKILL C on C.SKILL_ID = A.SKILL_ID
         where B.EMP_ID = A.EMP_CODE
           and A.SKILL_ID = C.SKILL_ID
           and A.SCORE <> 0
           and (B.EMP_ID in
                (select D.EMP_CODE
                 from JAS_EMP_SKILL_SCORE_NEW D
                 where d.SCORE > 0
                   and D.SKILL_ID in (select to_number(COLUMN_VALUE) from table (SPLITSTR(:P314_SKILLS, ':')))
                --    and (A.SCORE = :P314_STAR or :P314_STAR is null)
                and (decode(:P314_STAR ,'ALL',1,0)=1 or A.SCORE = :P314_STAR)
                 )
             or
                NVL(:P314_SKILLS, '1') = '1'
             )
         group by B.NAME, B.EMP_ID, B.TEAM, B.BASE_NAME, B.TITLE, B.ROLES, A.SCORE)
group by NAME, EMP_ID, TEAM, BASE_NAME, TITLE, ROLES;

问题：只展示对应技能和星级内容，其他技能看不到；

【展示6.0】修改查询逻辑

效果：

代码：

select NAME,
       EMP_ID,
       TEAM,
       BASE_NAME,
       TITLE,
       ROLES,
       nvl(listagg(score1, ','), '无') s1,
       nvl(listagg(score2, ','), '无') s2,
       nvl(listagg(score3, ','), '无') s3,
       nvl(listagg(score4, ','), '无') s4,
       nvl(listagg(score5, ','), '无') s5
from (
         select B.NAME,
                B.EMP_ID,
                B.TEAM,
                B.BASE_NAME,
                B.TITLE,
                B.ROLES,
                A.SCORE,
--        C.SKILL_NAME,
                case
                    when SCORE = 1 then
                        -- LISTAGG(C.SKILL_NAME ||  '; ', CHR(10)) WITHIN GROUP (ORDER BY SCORE)
                        LISTAGG(C.SKILL_NAME, CHR(10)) WITHIN GROUP (ORDER BY SCORE)
                    else
                        null end as score1,
                case
                    when SCORE = 2 then
                        -- LISTAGG(C.SKILL_NAME ||  '; ', CHR(10)) WITHIN GROUP (ORDER BY SCORE)
                        LISTAGG(C.SKILL_NAME, CHR(10)) WITHIN GROUP (ORDER BY SCORE)
                    else
                        null end as score2,
                case
                    when SCORE = 3 then
                        -- LISTAGG(C.SKILL_NAME ||  '; ', CHR(10)) WITHIN GROUP (ORDER BY SCORE)
                        LISTAGG(C.SKILL_NAME, CHR(10)) WITHIN GROUP (ORDER BY SCORE)
                    else
                        null end as score3,
                case
                    when SCORE = 4 then
                        -- LISTAGG(C.SKILL_NAME ||  '; ', CHR(10)) WITHIN GROUP (ORDER BY SCORE)
                        LISTAGG(C.SKILL_NAME, CHR(10)) WITHIN GROUP (ORDER BY SCORE)
                    else
                        null end as score4,
                case
                    when SCORE = 5 then
                        -- LISTAGG(C.SKILL_NAME ||  '; ', CHR(10)) WITHIN GROUP (ORDER BY SCORE)
                        LISTAGG(C.SKILL_NAME, CHR(10)) WITHIN GROUP (ORDER BY SCORE)
                    else
                        null end as score5
         from JAS_EMP_SKILL_SCORE_NEW A
                  join JAS_IT_EMP B on B.EMP_ID = A.EMP_CODE and B.IS_LEAVE = 0
                  join JAS_EMP_SKILL C on C.SKILL_ID = A.SKILL_ID
         where B.EMP_ID = A.EMP_CODE
           and A.SKILL_ID = C.SKILL_ID
           and A.SCORE <> 0
           and (B.EMP_ID in
                (select D.EMP_CODE
                 from JAS_EMP_SKILL_SCORE_NEW D
                 where d.SCORE > 0
                   and D.SKILL_ID in (select to_number(COLUMN_VALUE) from table (SPLITSTR(:P314_SKILLS, ':')))
                --    and (A.SCORE = :P314_STAR or :P314_STAR is null)
                and (decode(:P314_STAR ,-1,1,0)=1 or D.SCORE = :P314_STAR)
                 )
             or
                NVL(:P314_SKILLS, '1') = '1'
             )
         group by B.NAME, B.EMP_ID, B.TEAM, B.BASE_NAME, B.TITLE, B.ROLES, A.SCORE)
group by NAME, EMP_ID, TEAM, BASE_NAME, TITLE, ROLES;

【排序】

开发环境

正式环境

SELECT
'内部专项' LABLE,
COUNT(1) VAULE
FROM jas_project_master
where
to_char(start_date,'yyyy') = '2024'
and project_class = '内部专项'
and PROJECT_OWNER is not null
-- and DEL_FLAG is null

-- SELECT
-- '专项数量' LABLE,
-- COUNT(1) VAULE
-- FROM jas_project_master
-- where
-- to_char("start_date",'yyyy') = '2024'

UNION ALL
  SELECT
'外部专项' LABLE,
COUNT(1) VAULE
FROM jas_project_master
where
to_char(start_date,'yyyy') = '2024'
and project_class = '外部专项'
and PROJECT_OWNER is not null
-- and DEL_FLAG is null

-- UNION ALL
--   SELECT
-- '本月新增' LABLE,
-- COUNT(1) VAULE
-- FROM jas_project_master
-- where
-- to_char("start_date",'MM') = TO_CHAR(SYSDATE,'MM')

UNION ALL
SELECT
important_grade||'级' LABLE,
COUNT(important_grade) VAULE
FROM jas_project_master
where
to_char(start_date,'yyyy')='2024'
and PROJECT_OWNER is not null
and important_grade is not null
-- and DEL_FLAG is null
GROUP BY important_grade
/*ORDER BY LABLE ASC*/

排序

select id, name
from (select 5 id, 'a' name
      from dual
      union
      select 12 id, 'd' name
      from dual
      union
      select 3 id, 'c' name
      from dual)
order by decode(name, 'a', 1, 'c', 2, 'd', 3);

改好后

select LABLE, VAULE
from (SELECT '内部专项' LABLE,
             COUNT(1)   VAULE
      FROM jas_project_master
      where to_char(start_date, 'yyyy') = '2024'
        and project_class = '内部专项'
        and PROJECT_OWNER is not null

      UNION ALL
      SELECT '外部专项' LABLE,
             COUNT(1)   VAULE
      FROM jas_project_master
      where to_char(start_date, 'yyyy') = '2024'
        and project_class = '外部专项'
        and PROJECT_OWNER is not null

      UNION ALL
      SELECT important_grade || '级' LABLE,
             COUNT(important_grade)  VAULE
      FROM jas_project_master
      where to_char(start_date, 'yyyy') = '2024'
        and PROJECT_OWNER is not null
        and important_grade is not null GROUP BY important_grade)

/*ORDER BY LABLE ASC*/
order by decode(LABLE,'内部专项',1,'外部专项',2, 'P0级', 3, 'P1级', 4, 'P2级', 5, 'P3级', 6);

【样式冲突】

APEX22迁移到APEX23环境下，原本正常显示的看板无法无法展示，经验证数据本身没有问题，以json形式返回，但是无法返回对应颜色，排查后是样式冲突引起的

解决方法：在样式后加上!important 使该条样式属性声明具有最高优先级

【拓展】!important的用法及作用

定义及语法

  !important，作用是提高指定样式规则的应用优先权（优先级）。语法格式{ cssRule !important }，即写在定义的最后面，例如：box{color:red !important;}。

在CSS中，通过对某一样式声明! important ，可以更改默认的CSS样式优先级规则，使该条样式属性声明具有最高优先级。
浏览器识别
ie7及ie7+,firefox,chrome等浏览器下，已经可以识别 !important属性，但是IE 6.0IE6及更早浏览器下仍然不能完全识别。important的样式属性和覆盖它的样式属性单独使用时(不在一个{}里)，IE 6.0认为! important优先级较高，否则当含! important的样式属性被同一个{}里的样式覆盖时，IE 6.0认为! important较低!。
提高指定样式规则的应用优先权（优先级）

【在列过滤中展示对应列值】

列-列过滤器-打开启用-LOV类型 '不同列-可根据需要关闭/开启精准查询，默认是开启（大多时候不用开启）

开启前：

开启后：

【小数点前的0不显示】

m.WEIGHT_COEFFICIENT WEIGHT_COEFFICIENT, --权重系数

修改后

to_char(m.WEIGHT_COEFFICIENT,'fm99990.09') WEIGHT_COEFFICIENT, --权重系数

王小小鸭

关注

13
点赞
踩
5

收藏

觉得还不错? 一键收藏
0
评论
APEX开发过程中需要注意的小细节6

important，作用是提高指定样式规则的应用优先权（优先级）。语法格式{ cssRule!important }，即写在定义的最后面，例如：box{color:red!important;在CSS中，通过对某一样式声明!important ，可以更改默认的CSS样式优先级规则，使该条样式属性声明具有最高优先级。浏览器识别ie7及ie7+,firefox,chrome等浏览器下，已经可以识别!important属性，但是IE 6.0IE6及更早浏览器下仍然不能完全识别。
复制链接

扫一扫