请教一个查询语句，上火啊！感谢大家帮忙！

david_anwei 2011-11-09 04:15:20

1.原来查询语句

select a.patient_id,a.visit_id,c.name,b.bed_no
from VITAL_SIGNS_REC a,PATS_IN_HOSPITAL b,PAT_MASTER_INDEX c
where a.time_point = (select max(time_point) from VITAL_SIGNS_REC where patient_id = b.patient_id and vital_signs = '体温' and b.dept_code = '35') and
a.patient_id = b.patient_id and b.patient_id = c.patient_id and a.vital_signs = '体温' and b.dept_code = '35' order by bed_no asc

1 201579 2 陈少明 16
2 220878 1 姬琳 17
3 220877 1 赵艳丽 18
4 220686 1 王媛媛 19
5 220732 1 郝振杰 20

2.现在需要在原来查询语句之上增加2个列，但是这2个列需要通过单独查询得出，让后跟原来的数据合并到一起。

select a.patient_id,a.visit_id, a.vital_signs_values,c.name,b.bed_no,

(select vital_signs_values from (select ROWNUM RECNO,vital_signs_values from (SELECT * FROM VITAL_SIGNS_REC d where d.patient_id=a.patient_id and d.vital_signs='体温' ORDER BY d.time_point desc ) where rownum<=3 order by rownum asc) where RECNO=2) as vital_signs_values2

from VITAL_SIGNS_REC a,PATS_IN_HOSPITAL b,PAT_MASTER_INDEX c
where a.time_point = (select max(time_point) from VITAL_SIGNS_REC where patient_id = b.patient_id and vital_signs = '体温' and b.dept_code = '35') and
a.patient_id = b.patient_id and b.patient_id = c.patient_id and a.vital_signs = '体温' and b.dept_code = '35' order by bed_no asc

问题出在 patient_id = b.patient_id 标示红色的地方 invalid column name ,记得以前在SQL中是可以执行的，现在oracle中不能执行，请指教！

...全文

221 11 打赏收藏转发到动态举报

写回复

用AI写文章

11 条回复

切换为时间正序

请发表友善的回复…

发表回复

david_anwei 2011-11-09

打赏
举报

大哥：不知道跟你写的东西怎么合并起来看到你写的代码不知如何下手啊！惭愧啊！还请多费心。谢谢！

hudingchen 2011-11-09

打赏
举报

嵌套查询的别名阿

david_anwei 2011-11-09

打赏
举报

hudingchen：你好，不是很明白，还请指教！

david_anwei 2011-11-09

打赏
举报

SELECT m.patient_id,
MAX(DECODE(m.rn,1,visit_id)) visit_id,（这个是什么意思）

请问 M 是哪里来的谢谢！

hudingchen 2011-11-09

打赏
举报

用分析函数写了一个，



SQL> WITH vital_signs_rec AS (

  2    SELECT '1' patient_id,'a' visit_id, '体温' vital_signs,TO_DATE('20110101','yyyymmdd') time_point,10 vital_signs_values FROM DUAL UNION ALL

  3    SELECT '1' patient_id,'b' visit_id,'体温' vital_signs,TO_DATE('20110102','yyyymmdd') time_point,20 vital_signs_values FROM DUAL UNION ALL

  4    SELECT '1' patient_id,'c' visit_id,'体温' vital_signs,TO_DATE('20110104','yyyymmdd') time_point,70 vital_signs_values FROM DUAL UNION ALL

  5    SELECT '1' patient_id,'d' visit_id,'体温' vital_signs,TO_DATE('20110103','yyyymmdd') time_point,30 vital_signs_values FROM DUAL UNION ALL

  6    SELECT '2' patient_id,'e' visit_id,'体温' vital_signs,TO_DATE('20110104','yyyymmdd') time_point,110 vital_signs_values FROM DUAL UNION ALL

  7    SELECT '2' patient_id,'f' visit_id,'体温' vital_signs,TO_DATE('20110106','yyyymmdd') time_point,120 vital_signs_values FROM DUAL UNION ALL

  8    SELECT '2' patient_id,'g' visit_id,'体温' vital_signs,TO_DATE('20110105','yyyymmdd') time_point,170 vital_signs_values FROM DUAL UNION ALL

  9    SELECT '2' patient_id,'h' visit_id,'体温' vital_signs,TO_DATE('20110103','yyyymmdd') time_point,130 vital_signs_values FROM DUAL

 10  )

 11  SELECT m.patient_id,

 12         MAX(DECODE(m.rn,1,visit_id)) visit_id,

 13         MAX(DECODE(m.rn,1,vital_signs_values)) vital_signs_values1,

 14         MAX(DECODE(m.rn,2,vital_signs_values)) vital_signs_values2,

 15         MAX(DECODE(m.rn,3,vital_signs_values)) vital_signs_values3

 16    FROM (

 17    SELECT t.*,

 18           ROW_NUMBER() OVER(PARTITION BY t.patient_id ORDER BY t.time_point DESC) rn

 19      FROM vital_signs_rec t

 20     WHERE t.vital_signs = '体温'

 21    ) m

 22  WHERE m.rn <= 3

 23  GROUP BY m.patient_id

 24  ;



PATIENT_ID VISIT_ID VITAL_SIGNS_VALUES1 VITAL_SIGNS_VALUES2 VITAL_SIGNS_VALUES3

---------- -------- ------------------- ------------------- -------------------

1          c                         70                  30                  20

2          f                        120                 170                 110

上面sql再和你的另外两个表关联下

david_anwei 2011-11-09

打赏
举报

查询结果：select patient_id（病人标识号）,visit_id（本次住院标识）,name（姓名）, 最新温度记，次新，次次新

david_anwei 2011-11-09

打赏
举报

需求：获取一个科室中病人的最后三次温度采集数据
表 VITAL_SIGNS_REC（病人体症记录）字段： PATIENT_ID(病人标识号)，TIME_POINT(日期),VITAL_SIGNS(温度)，VITAL_SIGNS_VALUES（项目数值）
表 PATS_IN_HOSPITAL（在院病人记录）字段： PATIENT_ID(病人标识号)，VISIT_ID(本次住院标识),
表 PAT_MASTER_INDEX (病人信息表）字段： PATIENT_ID(病人标识号)，NAME(姓名)

opps_zhou 2011-11-09

打赏
举报

你把表结构贴一下，弄点数据，顺便说下你的需求，现在不知道你要干嘛

david_anwei 2011-11-09

打赏
举报

where a.time_point = (select max(time_point) from VITAL_SIGNS_REC where patient_id = b.patient_id and vital_signs = '体温' and b.dept_code = '35') 获取最新的一条记录现在我需要获取次新的记录并且要一行记录中体现！

david_anwei 2011-11-09

打赏
举报

请问我应该怎么改正啊！

opps_zhou 2011-11-09

打赏
举报

(select ROWNUM RECNO,vital_signs_values from (SELECT * FROM VITAL_SIGNS_REC d where d.patient_id=a.patient_id and d.vital_signs='体温' ORDER BY d.time_point desc ) where rownum<=3 order by rownum asc) where RECNO=2) as vital_signs_values2

你这里面又嵌套了一层查询，这样就有问题了