白水的sql需求：每个病人的对应最小诊断类别|partition|

 

diagnostic_category(diagnosis_type 诊断类型，diagnosis_no 诊断号1，主诊断，2，3，4··· 子诊断)

DIAGNOSIS_DICT（注：不是每个诊断编码都有类别的）

Select patiend_id,diagnosis_type,min(diagnosis_no) from (Select patient_id, visit_id, diagnosis_class from diagnostic_category left join

DIAGNOSIS_DICT

On diagnostic_category.diagnosis= DIAGNOSIS_DICT.diagnosis)

Where diagnosis_class is not null and diagnosis_type=’3’

Group by patiend_id,diagnosis_type

 

 

求表：

patient_id	visit_id	diagnosis_class

 

要求 进行两表联查，查询出诊断表中diagnosis_type=3中每个病人所属大类

 

首先进行diagnosis_no=1的数据进行匹配，如果1对于的 诊断类别为空，那继续进行diagnosis_no=2

进行匹配，如果还为空，继续进行diagnosis_no=3进行匹配，直到所匹配的类别不为空为止

一个病人只能有一个诊断类别 先匹配上的为准。

sql如下

Select patiend_id,diagnosis_type,min(diagnosis_no) from (Select patient_id, visit_id, diagnosis_class from diagnostic_category left join
DIAGNOSIS_DICT
On diagnostic_category.diagnosis= DIAGNOSIS_DICT.diagnosis)
Where diagnosis_class is not null and diagnosis_type=’3’
Group by patiend_id,diagnosis_type

 

 

2012-12-11 更新 ：根据上面的sql继续写出来class

select a.patient_id, a.visit_id,a.mind,b.diagnosis_code,c.diagnosis_class from (Select patiend_id,diagnosis_type,min(diagnosis_no) mind from (Select patient_id, visit_id, diagnosis_class from diagnostic_category left join
DIAGNOSIS_DICT
On diagnostic_category.diagnosis= DIAGNOSIS_DICT.diagnosis)
Where diagnosis_class is not null and diagnosis_type=’3’
Group by patiend_id,diagnosis_type) a,diagnostic_category b,DIAGNOSIS_DICT c on a.mind=b.diagnosis_code and b.diagnosis_code=c.diagnosis_code

这个sql有问题，首先会有重复行，并且null的也出来了

改进一下，略显复杂

辰影的写法：

select patiend_id,diagnosis_type,min(diagnosis_no)over(partition by patiend_id,diagnosis_type) diagnosis_no, diagnosis_class from (Select patient_id, visit_id, diagnosis_class from diagnostic_category a,DIAGNOSIS_DICT b
where a.diagnosis= b.diagnosis(+)
and diagnosis_class is not null and diagnosis_type='3'
)

 之二：据说上面的不行，果然不行

为什么加上order by也不行呢？min(diagnosis_no)over(partition by patient order by diagnosis_class ) ，xiasen这个还是三行

终于明白了，这里min(diagnosis_no)得到的是一个具体的diagnosis_no，它会和列的每一行xiasen对应，因此xiasen每行都有了，它不好去后后面的class对应，除非外面再增加一个select

这种min partition写法适用于下面提到的部门工资表，因为每个员工在每行是唯一的，可以在每行增加一个部门员工最少min。

继续：

select patiend_id, diagnosis_type, diagnosis_no
  from (select patiend_id,
               diagnosis_type,
               diagnosis_no,
               dense_rank() over(partition by patiend_id, diagnosis_type order by diagnosis_no) rn
          from (Select patient_id, visit_id, diagnosis_class
                  from diagnostic_category a, DIAGNOSIS_DICT b
                 where a.diagnosis = b.diagnosis(+)
                   and diagnosis_class is not null
                   and diagnosis_type = '3'))
 where rn = 1

select patient,visit_id,diagnosis_no, diagnosis_class
  from (select c.*, dense_rank() over(partition by patient  order by diagnosis_no) rn
          from (Select patient, visit_id,diagnosis_no, diagnosis_class
                  from diagnostic_category a, DIAGNOSIS_DICT b
                 where  a.diagonosis_code=b.diagonosis_code(+)
                   and diagnosis_class is not null )c)
 where rn = 1

注意这里的c.*

继续研究partition

 Sumsen♂  10:14:26
奇怪，为什么昨天的会很多行呢
    残忆未央  10:14:46
只要相同部门，相同工作有多少列  就会有多少行
cleak 20  要是有100 ，那就会有100个max，所以不奇怪
Sumsen♂  10:16:26
那个100个max对应的是不同的empno吗
    残忆未央  10:16:41
嗯  
Sumsen♂  10:16:48
那没有问题
    残忆未央  10:17:08
对应的就是那100个同部门同工种的

 2012-12-12 更新 itpub http://www.itpub.net/forum.php?mod=viewthread&tid=1747708&pid=20721479&page=1&extra=page%3D1#pid20721479

select a.patient,
             min(a.diagnosis_no) keep(dense_rank first order by decode(b.diagnosis_class, null, null, a.diagnosis_no) nulls last) diagnosis_no,
             min(b.diagnosis_class) keep(dense_rank first order by decode(b.diagnosis_class, null, null, a.diagnosis_no) nulls last) diagnosis_class
from diagnostic_category a,
           diagnosis_dict b
where a.diagonosis_code = b.diagonosis_code(+)
group by a.patient;

2012-12-14 更新

oracle keep(first/last)

FIRST/LAST函数按照某个字段排序后取得第一行或者最后一行,FIRST/LAST聚集函数可以按A列排序,B列聚集,避免了自连 接和子查询.分组聚合函数(min,max....)位于FIRST/LAST函数之前产生多行结果集,并且按照排序返回FIRST/LAST单个值.

要指定在每个组的顺序，FIRST/LAST函数之前加上以关键字KEEP开始即可，请注意在ORDER BY子句可以采取多种表现形式。

Returns the row ranked first using DENSE_RANK    

2种取值：
DENSE_RANK FIRST
DENSE_RANK LAST
在keep (DENSE_RANK first ORDER BY sl) 结果集中再取max、min的例子。

例子如下:oracle分析函数中，keep and over的区别
公司部门中入厂时间最早的员工的薪水最小的是多少

SELECT deptno,ename,empno,sal, 
MIN(sal) KEEP (dense_rank FIRST ORDER BY hiredate) over (PARTITION BY deptno) "min_sal"
FROM emp;

 

2012-12-12 16:57:45 补充

分析dense_rank partition，后面的order by 没有写默认asc升序，改成desc不行，如最下方的图

oracle使用dense_rank就会在每行的查询结果得到数，加上rn=1，排除其他，达到了目的。

desc