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

[qq_26071239]

我想通过SQL查询出一张住院信息表中同一病人在同一医疗机构就诊本次入院日期与上次出院日期间隔小于3天的数据。表结构大致如下：住院流水号、医保个人编码、患者姓名、性别、入院日期、出院日期、临床诊断、医疗总费用、就诊医疗机构。

[道玄希言]

;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

[道玄希言]

額，上面有点问题。。。

;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

[qq_26071239]

感谢老师，但结果不对啊。

[LongRui888]

试试这个：

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]

t1.rn>t2.rn 报错！

[LongRui888]

引用 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

[等不到来世]

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)

[qq_26071239]

结果还是不对。老师，能不能加你QQ？

[道玄希言]

引用 3 楼 qq_26071239 的回复:

结果不对。

放点测试数据出来， 沒数据我们没法帮你测试了。只是在想当然額。

[qq_26071239]

我加你QQ，你远程连接我的SQL数据库测试，好不好？

[qq_26071239]

不要，麻烦您加我的QQ47038043,谢谢！

[qq_26071239]

怎么没有老师上贴解答了，此难题尚未得到解决啊。。。。