请教两个SQL语句,解决了就给分
有两个表,一个是病人基本资料表(Person),
字段有:ID,Person_ID,Person_Name,Person_Sex,Person_Age
另一个表是病人的看病记录表(ReferList),
字段有ID,Person_ID,Com_Date,Re
其中Com_Date是看病日期时间,根据看病日期的先后就可以知道是第几次看病;两个表关联字段是Person_ID
(1)如何查询出病人所有人第三次看病的记录(包含病人资料),也就是说查询大于2次看病记录的所有人的资料和第三次看病记录。
查询出的记录样式如下:
Person_ID,Person_Name,Person_Sex,Person_Age,Com_Date,Re
(2)查询出所有超过两次看病人的记录,包含基本资料和看病记录。如何把这些记录按各个病人的资料+看病记录打印出来呢?
有两个表,一个是病人基本资料表(Person),
字段有:ID,Person_ID,Person_Name,Person_Sex,Person_Age
另一个表是病人的看病记录表(ReferList),
字段有ID,Person_ID,Com_Date,Re
其中Com_Date是看病日期时间,根据看病日期的先后就可以知道是第几次看病;两个表关联字段是Person_ID
(1)如何查询出病人所有人第三次看病的记录(包含病人资料),也就是说查询大于2次看病记录的所有人的资料和第三次看病记录。
查询出的记录样式如下:
Person_ID,Person_Name,Person_Sex,Person_Age,Com_Date,Re
(2)查询出所有超过两次看病人的记录,包含基本资料和看病记录。如何把这些记录按各个病人的资料+看病记录打印出来呢?
FROM @Person A
INNER JOIN
(SELECT Person_ID From @ReferList Group By Person_ID Having Count(1)>2) B
On A.Person_ID=B.Person_ID
Insert @person Select 1,1,'a','男',20
Union all select 2,2,'b','女',21Declare @ReferList Table(ID Int,Person_ID Int,Com_Date Datetime,Re Varchar(50))
Insert @ReferList Select 1,1,'2001-8-5','aaaaaaaaa'
Union all Select 2,1,'2002-8-5','aaaaaaaaa'
Union all Select 3,1,'2002-8-4','aaaaaaaaa'
Union all Select 4,2,'2002-8-5','bbbbbbbb'
Union all Select 5,2,'2002-8-4','bbbbbbbbbb'--超过2次看病的基本信息与看病情况!
SELECT *
FROM
(
SELECT *
FROM @ReferList
WHERE Person_ID
IN
(SELECT Person_ID FROM @ReferList Group By Person_ID Having Count(1)>2)) A
LEFT JOIN @Person B
ON A.Person_ID=B.Person_ID
select top 3 * from
(
select a.id,a.person_ID,a.person_name,a.person_sex,a.person_age,
b.id as bid,b.person_ID,b.com_date,b.re
from Person a,ReferList b
Where a.person_ID=b.person_ID
) a
Where bid not in
(select top 2 bid from
(
select a.id,a.person_ID,a.person_name,a.person_sex,a.person_age,
b.id as bid,b.person_ID,b.com_date,b.re
from Person a,ReferList b
Where a.person_ID=b.person_ID
) a
)
select * from
(
select top 3 a.id,a.person_ID,a.person_name,a.person_sex,a.person_age,
b.id as bid,b.person_ID,b.com_date,b.re
from Person a,ReferList b
Where a.person_ID=b.person_ID
order by com_date
) a
where bid not in
(
select Top 2 b.id
from Person a,ReferList b
Where a.person_ID=b.person_ID
order by b.com_date
)
declare aa cursor
for
select * from
(
select a.id,a.person_ID,a.person_name,a.person_sex,a.person_age,
b.id as bid,convert(varchar(50),b.com_date) as com_date,b.re
from Person a,ReferList b
Where a.person_ID=b.person_ID
) a
where bid not in
(
select Top 2 b.id
from Person a,ReferList b
Where a.person_ID=b.person_ID
order by b.com_date
)
order by com_date
Open aa
declare @id varchar(50),@person_ID varchar(50),@person_sex varchar(50),@person_age varchar(50),
@bid varchar(50) ,@com_date varchar(50),@re varchar(50)
fetch next from aa into
@id ,@person_ID ,@person_sex ,@person_age ,
@bid ,@person_ID ,@com_date ,@re
While @@fetch_status=0
begin
fetch next from aa into
@id ,@person_ID ,@person_sex ,@person_age ,
@bid ,@person_ID ,@com_date ,@re
print @id+@person_ID+@person_sex+@person_age+
@bid +@person_ID +@com_date +@re
end
drop table person
gocreate table Person (ID Int,Person_ID Int,Person_Name Varchar(50),Person_Sex Varchar(10),Person_Age Int)
Insert person Select 1,1,'a','男',20
Union all select 2,2,'b','女',21
if object_id(N'ReferList') is not null
drop table ReferList
gocreate table ReferList (ID Int,Person_ID Int,Com_Date Datetime,Re Varchar(50))
Insert ReferList Select 1,1,'2001-8-5','aaaaaaaaa'
Union all Select 2,1,'2002-8-5','aaaaaaaaa'
Union all Select 3,1,'2002-8-4','aaaaaaaaa'
Union all Select 4,2,'2002-8-5','bbbbbbbb'
Union all Select 5,2,'2002-8-4','bbbbbbbbbb'if object_id(N'tempdb..#ReferList') is not null
drop table #ReferList
go--将业务表内容导入临时表进行处理;其中indid为用户来的次数
select identity(int,1,1) tempid, 0 indid, person_id, com_date, re
into #ReferList
from referlist
order by person_id, com_date--更新表内容;
update a
set a.indid = a.tempid - b.tempid
from #referlist a,(select person_id, min(tempid) - 1 as tempid from #referlist group by person_id ) b
where a.person_id = b.person_idselect * from #referlist
select * from #referlist where indid = 3 --