sql 应用记录

SELECT * FROM (select aa.*,bb.mentalvisitid,
case when bb.BCSFXS='1' then '门诊'  when bb.BCSFXS='2' then '家庭访视' else '电话' end as BCSFXS ,bb.visitdate,
case when bb.Hospitalstatus='1' then '目前正在住院' when bb.Hospitalstatus='2' then '曾住院,现未住院' else '未住院' end as Hospitalstatus,
case when bb.visittype='1' then '不稳定'  when bb.visittype='2' then '基本稳定' else '稳定' end as visittype ,
case when bb.Followmedicine='0' then '按医嘱规律用药'   when bb.Followmedicine='1' then '间断用药'  when bb.Followmedicine='2' then '不用药' else '医嘱无需用药' end as Followmedicine,
case when bb.Treatment='1' then '痊愈'   when bb.Treatment='2' then '好转'  when bb.Treatment='3' then '无变化' when bb.Treatment='4' then '加重' else '此项不适用' end as Treatment,
case when bb.Labcheck ='2' then '有' else '无' end as Labcheck,    
  
bb.labcheckother,

(CASE WHEN bb.SYMPTOMS like '%1%' then '幻觉  ' end ||
 CASE WHEN bb.SYMPTOMS like '%2%' then '交流困难  ' end ||
    CASE WHEN bb.SYMPTOMS like '%3%' then '猜疑  ' end ||
       CASE WHEN bb.SYMPTOMS like '%4%' then '喜怒无常  ' end ||
          CASE WHEN bb.SYMPTOMS like '%5%' then '行为怪异  ' end ||
             CASE WHEN bb.SYMPTOMS like '%6%' then '兴奋话多  ' end ||
               CASE WHEN bb.SYMPTOMS like '%7%' then '伤人毁物  ' end ||
                 CASE WHEN bb.SYMPTOMS like '%8%' then '悲观厌世  ' end ||
                   CASE WHEN bb.SYMPTOMS like '%9%' then '无故外走  ' end ||
                     CASE WHEN bb.SYMPTOMS like '%10%' then '自语自笑  ' end ||
                        CASE WHEN bb.SYMPTOMS like '%11%' then '孤僻懒散  ' end ||
                           CASE WHEN bb.SYMPTOMS like '%12%' then '其他  ' end 
) as SYMPTOMS,
(CASE WHEN bb.RECOVERYMANNER like '%1%' then '生活劳动能力  ' end ||
 CASE WHEN bb.RECOVERYMANNER like '%2%' then '职业训练  ' end ||
    CASE WHEN bb.RECOVERYMANNER like '%3%' then '学习能力  ' end ||
       CASE WHEN bb.RECOVERYMANNER like '%4%' then '社会交往  ' end ||
          CASE WHEN bb.RECOVERYMANNER like '%5%' then '其他  ' end 
) as RECOVERYMANNER,


(select  wm_concat(to_char(medname)  || '(' || '每日' || to_char(usage) || '次 , ' || to_char(amount) || ')' )  
from MB_MentalVisit_Medicine 
where ISSTATE = '1' 
and Mentalvisitid  = bb.mentalvisitid) AS MEDICINE,
                      
case when bb.DANGERSTATUS='0' then '0级'   when bb.DANGERSTATUS='1' then '1级'  when bb.DANGERSTATUS='2' then '2级' when bb.DANGERSTATUS='3' then '3级' when bb.DANGERSTATUS='4' then '4级' when bb.DANGERSTATUS='5' then '5级' end as DANGERSTATUS,
case when bb.SELFKNOW='1' then '自知力完全'   when bb.SELFKNOW='2' then '自知力不全'  when bb.SELFKNOW='3' then '自知力缺失' end as SELFKNOW,
case when bb.SLEEP='1' then '良好'   when bb.SLEEP='2' then '一般'  when bb.SLEEP='3' then '较差' end as SLEEP,
case when bb.EATING='1' then '良好'   when bb.EATING='2' then '一般'  when bb.EATING='3' then '较差' end as EATING,
case when bb.PERSONALLIFE='1' then '良好'   when bb.PERSONALLIFE='2' then '一般'  when bb.PERSONALLIFE='3' then '较差' end as PERSONALLIFE,
case when bb.FAMILY='1' then '良好'   when bb.FAMILY='2' then '一般'  when bb.FAMILY='3' then '较差' end as FAMILY,
case when bb.PRODUCTWROK='1' then '良好'   when bb.PRODUCTWROK='2' then '一般'  when bb.PRODUCTWROK='3' then '较差' end as PRODUCTWROK, 
case when bb.STUDY='1' then '良好'   when bb.STUDY='2' then '一般'  when bb.STUDY='3' then '较差' end as STUDY,
case when bb.SOCIAL='1' then '良好'   when bb.SOCIAL='2' then '一般'  when bb.SOCIAL='3' then '较差' end as SOCIAL,
bb.VISITDOCTOR,
case when  (select Count(*) from MB_Autograph where Sfid=bb.mentalvisitid and Sf_type ='kf_mentalvisit')=1 then '电子签名' else '' end as DZQM,
bb.HZJSQM,
ROW_NUMBER() OVER(PARTITION BY aa.personid ORDER BY bb.Mentalvisitid DESC) RNO from (SELECT PERSONID,
               TOWNNAME,
               PERSONCODE,
               PNAME,
               PHONENO,
               BIRTHDAY,
               RESIDENCEADDRESS,
               CREATEDATE,
               GENDERCODE,
               GENDERNAME,
                DISEASE1,
                DISDATE1,
                DISEASE2,
                DISDATE2,
                DISEASE3,
                DISDATE3,
                DISEASE4,
                DISDATE4,
                DISEASE5,
                DISDATE5,
                DISEASE6,
                DISDATE6,
               IDCARDNO,VILLAGENAME,relationname,
               (SELECT COUNT(A.MENTALVISITID)
                  FROM KF_MENTALVISIT A
                 WHERE A.PERSONID = P.PERSONID
                   AND A.ISDELETED = '0'  and Visitdate >= to_date('2015-05-14','yyyy-MM-dd')  and Visitdate <= to_date('2018-07-24','yyyy-MM-dd')) AS VISITEDTIMES,
                (SELECT B.RELATION
                          FROM KF_MENTAL B
                         WHERE B.ISDELETED = '0'
                           AND B.PERSONID = P.PERSONID
                           AND ROWNUM <= 1) AS RELATION,
                (SELECT B.LOCKSITUATION
                          FROM KF_MENTAL B
                         WHERE B.ISDELETED = '0'
                           AND B.PERSONID = P.PERSONID
                           AND ROWNUM <= 1) AS LOCKSITUATION,
                (SELECT B.RECENTDIAGNOSE
                          FROM KF_MENTAL B
                         WHERE B.ISDELETED = '0'
                           AND B.PERSONID = P.PERSONID
                           AND ROWNUM <= 1) AS RECENTDIAGNOSE,
 (SELECT GUARDIAN
          FROM KF_MENTAL B
         WHERE B.ISDELETED = '0'
           AND B.PERSONID = P.PERSONID
           AND ROWNUM <= 1) AS GUARDIAN,
 (SELECT FIRSTTIME
          FROM KF_MENTAL B
         WHERE B.ISDELETED = '0'
           AND B.PERSONID = P.PERSONID
           AND ROWNUM <= 1) AS FIRSTTIME,ROW_NUMBER() OVER(ORDER BY PERSONCODE DESC)  AS RN
          FROM DAGL_PERSON P  WHERE p.isdeleted = '0'
                                                        and p.status = '正常'
                                                        and p.diseasecode like '%,8,%' and ( p.villagecode like '370783%' )  and   (  p.villagecode like '370783112%'  )  and (select count(t.MENTALVISITID) as visitedTimes
                                      from KF_MENTALVISIT t
                                     where t.isdeleted = '0'  and t.personid = p.personid  and Visitdate >= to_date('2015-05-14','yyyy-MM-dd')  and Visitdate <= to_date('2018-07-24','yyyy-MM-dd')) >= 1 and exists(select 1 from kf_mental where kf_mental.personid=p.personid) ) aa left join kf_mentalvisit bb ON aa.personid = bb.personid)C
where 1=1  and Visitdate >= to_date('2015-05-14','yyyy-MM-dd')  and Visitdate <= to_date('2017-07-24','yyyy-MM-dd')
      

解析：

1.Case When Then end

　添加的 if else的逻辑

2. wm_concat()   , wm_concat(to_char())

　　多条记录合并成一条，类似于for

3.  ||   拼接字符串用

　　类似于操作符了

4. () 可以连表查字段

　　类似于函数执行一部分操作

5.PARTITION BY ... ORDER BY ... DESC

　　多表合并时使用的操作

6.where 条件中  exists()

7.left join  on  注意小括号的使用

8. group by  分组求和

        select RETIREMENTUNIT, COMPETENTDEPTNAME, sum(money) as money
          from LYDX_ONEPENSIONSUBSIDY t
         group by rollup(RETIREMENTUNIT, COMPETENTDEPTNAME)

GROUPING函数可以接受一列，返回0或者1。如果列值为空，那么GROUPING()返回1；如果列值非空，那么返回0。GROUPING只能在使用ROLLUP或CUBE的查询中使用。当需要在返回空值的地方显示某个值时，GROUPING()就非常有用。ROLLUP分组函数可以理解为Group By分组函数封装后的精简用法，它的本质是在分组计算后再union all一个统计所有数据的行。例如：
select group_id,sum(salary) from group_test group by rollup(group_id);
它就等价于：
select group_id,sum(salary) from group_test group by group_id
union all
select null, sum(salary) from group_test
order by 1;