实习知识点记录（待）

SQL 笔记

wm_concat()：多字段拼接

简单描述：

• 小明，小红，小光都是一班的学生
• 现在要查询所有一班的学生姓名（上面3人）
• 正常结果：出现3条结果
• 使用 wm_concat（student.xm）：出现一条结果
  字段 xm 值为 “小明，小红，小光”

replace()：替换字符

可与 wm_concat() 合并使用，将合并的结果中的 ‘，’替换成其他

// distinct 去重
REPLACE(wm_concat(distinct student.xm),',','、') AS "XM"

结果：字段 xm 值为 “小明、小红、小光”

codedetail()：字典转换

将某些值（01，02，03…）替换成数据库对应某个字典的值

codedetail('字典名称',SJ.SJLX) SJLXMC

to_char()：字符类型转换（日期）

to_char(SJBD.DJSJ , 'yyyy-mm-dd') SQSJ,

decode()：switch_case 操作

nvl()：null值赋值

nvl(SJBD.ZXZT,'0') = '0'

综合例子

SELECT
            SJBD.DQZT,
            SJBD.SYSTEMID,SJ.SJMC AS "SJMC",SJ.SJLX AS "SJLX",
            codedetail('PJ_BDGL_SJLX',SJ.SJLX) SJLXMC,
            b1.XM AS "BDRY",c.CLPH AS "BDCL",
            SJBD.DJRXM AS "SQR",SJBD.DJDWMC AS "SQDW",SJBD.SQSY AS "SQSY",
            to_char(SJBD.DJSJ , 'yyyy-mm-dd') SQSJ,
            to_char(SJBD.BDJSSJ,'yyyy-mm-dd') JSSJ
            FROM B_PJ_BDGL_SJXX SJ
            LEFT JOIN
            B_PJ_BDGL_SJBDXX SJBD
            ON
            SJ.SYSTEMID = SJBD.SJXXID
            LEFT JOIN
            (select BDXX.SJBDXXID,REPLACE(wm_concat(distinct RYXX.XM),',','、') AS "XM"
            from B_PJ_BDGL_RYXX RYXX
            LEFT JOIN
            B_PJ_BDGL_RYBDXX RYBD
            ON RYBD.RYXXID = RYXX.SYSTEMID
            LEFT JOIN
            B_PJ_BDGL_BDXXGX BDXX
            ON BDXX.RYBDXXID = RYBD.SYSTEMID
            GROUP BY
            BDXX.SJBDXXID) b1
        ON SJBD.systemid = b1.SJBDXXID

        LEFT JOIN
        (
         select REPLACE(wm_concat(distinct CLXX.CLPH),',','、') AS "CLPH",BDXX.SJBDXXID
            from B_PJ_BDGL_CLBDXX CLBD
            LEFT JOIN B_PJ_BDGL_CLXX CLXX
            ON CLBD.CLXXID = CLXX.SYSTEMID
            LEFT JOIN
            B_PJ_BDGL_BDXXGX BDXX
            ON BDXX.CLBDXXID = CLBD.SYSTEMID
            GROUP BY BDXX.SJBDXXID
         ) c
          ON c.SJBDXXID = SJBD.SYSTEMID
          WHERE nvl(SJBD.ZXZT,'0') = '0'
          #if("$!{sjmc}" !="")
            and SJ.SJMC like '%'||:{sjmc}||'%'
          #end
          #if("$!{sjlx}" !="")
            and SJ.SJLX =:{sjlx}
          #end
          #if("$!{bdry}" !="")
            and b1.XM like '%'||:{bdry}||'%'
          #end
          #if("$!{bdcl}" !="")
            and c.CLPH like '%'||:{bdcl}||'%'
          #end
           #if("$!{sqr}" !="")
            and SJBD.DJRXM like '%'||:{sqr}||'%'
          #end
           #if("$!{sqdw}" !="")
            and SJBD.DJDWDM =:{sqdw}
          #end
          #if("$!{sqsj_START}" != "")
		    AND SJBD.DJSJ >= to_date(:{sqsj_START},'yyyy-mm-dd')
		  #end
	      #if("$!{sqsj_END}" != "")
	   	    AND SJBD.DJSJ <= to_date(:{sqsj_END},'yyyy-mm-dd')
	      #end
          ORDER BY SJBD.DJSJ DESC

Resource_URL

JReble 2020

JReble插件 热部署，减少大项目修改代码后重启时间

生成 GUID ： https://www.guidgen.com/

网址 + GUID

https://jrebel.qekang.com/

例:

https://jrebel.qekang.com/cb2546bb-9d43-4115-bf4b-10539349efed

设置离线模式 来防止失效
File -> Settings -> JRebel -> [Work offline]按钮

WebStorm && Idea

lookdiv.com