急求一SQL查询语句,请老师们帮忙,先谢谢了!!!

qq_26071239 2015-10-09 03:40:37
我想通过SQL查询出一张住院信息表中同一病人在同一医疗机构就诊本次入院日期与上次出院日期间隔小于3天的数据。表结构大致如下:住院流水号、医保个人编码、患者姓名、性别、入院日期、出院日期、临床诊断、医疗总费用、就诊医疗机构。
...全文
139 点赞 收藏 12
写回复
12 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
qq_26071239 2015-10-09
怎么没有老师上贴解答了,此难题尚未得到解决啊。。。。
回复
qq_26071239 2015-10-09
不要,麻烦您加我的QQ47038043,谢谢!
回复
qq_26071239 2015-10-09
我加你QQ,你远程连接我的SQL数据库测试,好不好?
回复
道玄希言 2015-10-09
引用 3 楼 qq_26071239 的回复:
结果不对。
放点测试数据出来, 沒数据我们没法帮你测试了。只是在想当然額。
回复
qq_26071239 2015-10-09
结果还是不对。老师,能不能加你QQ?
回复
等不到来世 2015-10-09
select * from 住院信息表 as t
where exists(select 1 from 住院信息表 where [医保个人编码]=t.[医保个人编码] and [就诊医疗机构]=t.[就诊医疗机构]
	and [住院流水号]<>t.[住院流水号] and DATEDIFF(d,t.[出院日期],[入院日期])<=3)
or  exists(select 1 from 住院信息表 where [医保个人编码]=t.[医保个人编码] and [就诊医疗机构]=t.[就诊医疗机构] 
	and [住院流水号]<>t.[住院流水号] and DATEDIFF(d,[出院日期],t.[入院日期])<=3)
回复
引用 5 楼 qq_26071239 的回复:
t1.rn>t2.rn 报错!
这样:
;
WITH    tbl
          AS ( SELECT   ROW_NUMBER() OVER ( PARTITION BY 医保个人编码, 就诊医疗机构 ORDER BY 入院日期 DESC ) AS rn ,
                        *
               FROM     住院信息表
             )
    SELECT  t1.[医保个人编码] ,
            t1.[患者姓名] ,
            t1.[性别] ,
            t1.[临床诊断] ,
            t1.[医疗总费用] ,
            t1.[就诊医疗机构] ,
            t1.[入院日期] ,
            t2.[出院日期]
    FROM    tbl t1
            LEFT JOIN tbl t2 ON t1.[医保个人编码] = t2.[医保个人编码]
                                AND t1.[就诊医疗机构] = t2.[就诊医疗机构]
                                AND t1.rn > t2.rn
    WHERE   DATEDIFF(DAY, t2.[出院日期], t1.[入院日期]) < 3
回复
qq_26071239 2015-10-09
t1.rn>t2.rn 报错!
回复
试试这个:
WITH    tbl
          AS ( SELECT   ROW_NUMBER() OVER ( PARTITION BY 医保个人编码, 就诊医疗机构 ORDER BY 入院日期 DESC ) AS tid ,
                        *
               FROM     住院信息表
             )
    SELECT  t1.[医保个人编码] ,
            t1.[患者姓名] ,
            t1.[性别] ,
            t1.[临床诊断] ,
            t1.[医疗总费用] ,
            t1.[就诊医疗机构] ,
            t1.[入院日期] ,
            t2.[出院日期]
    FROM    tbl t1
            LEFT JOIN tbl t2 ON t1.[医保个人编码] = t2.[医保个人编码]
                                AND t1.[就诊医疗机构] = t2.[就诊医疗机构]
                                AND t1.rn > t2.rn
    WHERE   DATEDIFF(DAY, t2.[出院日期], t1.[入院日期]) < 3
回复
qq_26071239 2015-10-09
感谢老师,但结果不对啊。
回复
道玄希言 2015-10-09
額,上面有点问题。。。


;with tbl as
(
select ROW_NUMBER() OVER(partition by 医保个人编码, 就诊医疗机构 
   order by 入院日期 desc) as tid, * from 住院信息表 
)

select t1.[医保个人编码], t1.[患者姓名], 
  t1.[性别],  t1.[临床诊断],  t1.[医疗总费用],
  t1.[就诊医疗机构], 
  t1.[入院日期], t2.[出院日期] 
  from 
  (
      select * from tbl where tid = 1
  ) as t1
  left join 
  (
      select * from tbl where tid =2
  ) as t2 
  on t1.[医保个人编码] = t2.[医保个人编码]
  and t1.[就诊医疗机构] = t2.[就诊医疗机构]
  where DATEDIFF(day, t2.[出院日期], t1.[入院日期]) < 3

回复
道玄希言 2015-10-09



;with tbl as
(
select ROW_NUMBER() OVER(partition by 医保个人编码, 就诊医疗机构 
   order by 入院日期 desc) as tid, * from 住院信息表 
),
tbl2 as 
(
  select t1.[医保个人编码], t1.[患者姓名], 
  t1.[性别],  t1.[临床诊断],  t1.[医疗总费用],
  t1.[就诊医疗机构], 
  t1.[入院日期], t2.[出院日期] 
  from 
  (
      select * from tbl where tid = 1
  ) as t1
  left join 
  (
      select * from tbl where tid =2
  ) as t2 
  on t1.[医保个人编码] = t2.[医保个人编码]
  and t1.[就诊医疗机构] = t2.[就诊医疗机构]
  where DATEDIFF(day, t2.[出院日期], t1.[入院日期]) < 3

回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2015-10-09 03:40
社区公告
暂无公告