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 joinDIAGNOSIS_DICTOn 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 joinDIAGNOSIS_DICTOn 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 bwhere 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
2012-12-12 更新 itpub
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_classfrom diagnostic_category a, diagnosis_dict bwhere 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
