请教两个SQL语句，解决了就给分

[yzty]

请教两个SQL语句，解决了就给分
有两个表，一个是病人基本资料表（Person），
字段有：ID,Person_ID,Person_Name,Person_Sex,Person_Age
另一个表是病人的看病记录表（ReferList）,
字段有ID,Person_ID,Com_Date,Remark
其中Com_Date是看病日期时间，根据看病日期的先后就可以知道是第几次看病；两个表关联字段是Person_ID
（1）如何查询出病人所有人第三次看病的记录（包含病人资料），也就是说查询大于2次看病记录的所有人的资料和第三次看病记录。
查询出的记录样式如下：
Person_ID,Person_Name,Person_Sex,Person_Age,Com_Date,Remark
（2）查询出所有超过两次看病人的记录，包含基本资料和看病记录。如何把这些记录按各个病人的资料+看病记录打印出来呢？

[marco08]

--1
select * from Person
inner join
(
select * from ReferList as tmp
where (select count(*) from ReferList where Person_ID=tmp.Person_ID and Com_Date<=tmp.Com_Date)=3
)ReferList on Person.Person_ID=ReferList.Person_ID

[marco08]

create table Person(ID int,Person_ID int,Person_Name varchar(10),Person_Sex bit, Person_Age int)
insert Person select 1, 1, '张三', 1, 25
insert Person select 2, 2, '李四', 0, 35
insert Person select 3, 3, '王五', 1, 29
go

create table ReferList(ID int, Person_ID int,Com_Date datetime,Remark varchar(10))
insert ReferList select 1, 1, '2005-12-5', null

insert ReferList select 2, 2, '2006-1-2', null
insert ReferList select 3, 2, '2006-1-3', null
insert ReferList select 4, 2, '2006-1-4', null
insert ReferList select 5, 2, '2006-1-5', null

insert ReferList select 6, 3, '2006-2-6', null
insert ReferList select 7, 3, '2006-2-7', null
go

--1
select * from Person
inner join
(
select * from ReferList as tmp
where (select count(*) from ReferList where Person_ID=tmp.Person_ID and Com_Date<=tmp.Com_Date)=3
)ReferList on Person.Person_ID=ReferList.Person_ID


--2
select * from Person
inner join
(
select * from ReferList as tmp
where (select count(*) from ReferList where Person_ID=tmp.Person_ID)>1
)ReferList on Person.Person_ID=ReferList.Person_ID

[akagilnc]

第一个问题
select * from Person
inner join
(
select * from ReferList as tmp
where (select count(*) from ReferList where Person_ID=tmp.Person_ID and Com_Date<=tmp.Com_Date)>=3
)ReferList on Person.Person_ID=ReferList.Person_ID
应该这样吧.楼主说的是所有大于2次的,不是只要3次的

[akagilnc]

改了下,这样似乎就是楼主想要的..没有那个Person_ID的2次出现呵呵
select p.Person_ID,p.Person_Name,p.Person_Sex,p.Person_Age,r.Com_Date,r.Remark from Person as p
join
(
select * from ReferList as tmp
where (select count(*) from ReferList where Person_ID=tmp.Person_ID and Com_Date<=tmp.Com_Date)>=3
)as R on P.Person_ID=R.Person_ID

[akagilnc]

第二个问题也改了下
select p.Person_ID,p.Person_Name,p.Person_Sex,p.Person_Age,r.Com_Date,r.Remark from Person as p
join
(
select * from ReferList as tmp
where (select count(*) from ReferList where Person_ID=tmp.Person_ID)>2
)as R on P.Person_ID=R.Person_ID

[yzty]

再问一个，怎么取得所有人的最后一次看病纪录

[yzty]

非常感谢marco08(天道酬勤) 和akagilnc() 的帮助
晚上回去就分分给你们
marco08(天道酬勤)你的名字好熟悉的，codematic是你写的吧

[xiluhua]

路过看看