问题:
CREATE TABLE [dbo].[Web_Patient](
[ID] [int] NOT NULL,
[UserName] [nvarchar](255) COLLATE Chinese_PRC_CI_AS NULL,
[ALT] [nvarchar](255) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(1,‘王三’,45)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(2,‘王三’,55)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(3,‘王三’,65)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(4,‘李四’,5)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(5,‘李四’,35)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(6,‘李四’,65)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(7,‘陈六’,45)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(8,‘陈六’,75)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(9,‘陈六’,65)
查询 “最后一次的结果比第一次好”注解:‘ALT’ 肝脏检查里面的一个数据,比如正常人的 ALT值,应该是 10—50之间;举例,如果‘王三’ 最近一次的查询的ALT值是65,而他上一次查询的ALT值是55,说明王三的ALT值恶化了。需求,查询条件是,最近一次和上一次比较ALT恶化的患者数据显示出的结果
-------------------------------------------
ID UserName ALT
1 王三 65
1 陈六 65
-------------------------------------------
请求各位帮忙指导。
CREATE TABLE [dbo].[Web_Patient](
[ID] [int] NOT NULL,
[UserName] [nvarchar](255) COLLATE Chinese_PRC_CI_AS NULL,
[ALT] [nvarchar](255) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(1,‘王三’,45)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(2,‘王三’,55)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(3,‘王三’,65)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(4,‘李四’,5)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(5,‘李四’,35)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(6,‘李四’,65)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(7,‘陈六’,45)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(8,‘陈六’,75)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(9,‘陈六’,65)
查询 “最后一次的结果比第一次好”注解:‘ALT’ 肝脏检查里面的一个数据,比如正常人的 ALT值,应该是 10—50之间;举例,如果‘王三’ 最近一次的查询的ALT值是65,而他上一次查询的ALT值是55,说明王三的ALT值恶化了。需求,查询条件是,最近一次和上一次比较ALT恶化的患者数据显示出的结果
-------------------------------------------
ID UserName ALT
1 王三 65
1 陈六 65
-------------------------------------------
请求各位帮忙指导。
[ID] [int] NOT NULL,
[UserName] [nvarchar](255) COLLATE Chinese_PRC_CI_AS NULL,
[ALT] [nvarchar](255) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(1,'王三',45)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(2,'王三',55)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(3,'王三',65)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(4,'李四',5)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(5,'李四',35)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(6,'李四',65)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(7,'陈六',45)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(8,'陈六',75)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(9,'陈六',65)select * from Web_Patient a
where ALT>(select top 1 ALT from Web_Patient where UserName=a.UserName and id<a.id order by id desc)
and
not exists(select 1 from Web_Patient where UserName=a.UserName and id>a.id)
/*
ID UserName
----------- -----------
3 王三
6 李四
where ALT>(select top 1 ALT from Web_Patient where UserName=a.UserName and id<a.id order by id desc)
and
not exists(select 1 from Web_Patient where UserName=a.UserName and id>a.id )
and ALT not between 10 and 50--这个条件是不是加上会符合楼主的意思?
感谢“ssp2009”的解答。
可是忽略了一个问题 ,就是正常人的 ALT值,应该是 10—50之间;CREATE TABLE [dbo].[Web_Patient](
[ID] [int] NOT NULL,
[UserName] [nvarchar](255) COLLATE Chinese_PRC_CI_AS NULL,
[ALT] [nvarchar](255) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(1,‘王三’,5)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(2,‘王三’,10)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(3,‘王三’,45)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(4,‘李四’,5)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(5,‘李四’,35)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(6,‘李四’,65)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(7,‘陈六’,10)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(8,‘陈六’,20)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(9,‘陈六’,30)以上数据判断的话,只有李四恶化了,因为正常值在10-50之间,王三,10变到45,还属于正常值,不属于恶化,陈六,一直在正常值内变换,所以也是没有恶化的,结果只能是李四恶化了--------------------------
ID UserName
6 李四
-------------------------------
感谢大家的帮助,请帮忙看看
select b.* from #Web_Patient as a,#Web_Patient as b
where a.UserName=b.UserName and a.ID+1=b.ID
and b.Alt>a.Alt
and b.ID =(select max(ID) from #Web_Patient where UserName=b.UserName)--结果
ID UserName ALT
3 王三 65
6 李四 65(2 行受影响)
select b.* from #Web_Patient as a,#Web_Patient as b
where a.UserName=b.UserName and a.ID+1=b.ID
and b.Alt>a.Alt
and b.ID =(select max(ID) from #Web_Patient where UserName=b.UserName)
and (b.Alt<10 or b.Alt>50)--结果
ID UserName ALT
3 王三 65
6 李四 65(2 行受影响)
select L.* from (select * from #Web_Patient as op1 where
not exists(select 1 from #Web_Patient as ip1 where ip1.UserName=op1.Username and ip1.id>op1.id)) as L
join
(select * from #Web_Patient as op2 where
(select count(*) from #Web_Patient as ip2 where ip2.UserName=op2.Username and ip2.id>op2.id)=1) as R
on L.UserName=R.UserName
where (case when L.ALT<=10 then 10-L.ALT
when L.ALT>=50 then L.ALT-50
else 0
end)>
(case when R.ALT<=10 then 10-R.ALT
when R.ALT>=50 then R.ALT-50
else 0
end)
[ID] [int] NOT NULL,
[UserName] [nvarchar](255) COLLATE Chinese_PRC_CI_AS NULL,
[ALT] [nvarchar](255) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(1,'王三',5)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(2,'王三',10)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(3,'王三',45)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(4,'李四',5)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(5,'李四',35)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(6,'李四',65)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(7,'陈六',10)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(8,'陈六',20)
INSERT INTO Web_Patient(ID,UserName,ALT) VALUES(9,'陈六',30)
go
--SQL:最近一次和上一次比较ALT恶化
select a.id, a.UserName, ALT = b.LastALT from
(select UserName, ID = MAX(id) from [Web_Patient] group by UserName) a
cross apply
(select LastALT = ALT from [Web_Patient] where UserName = a.UserName and ID = a.ID) b
cross apply
(select top(1) PriorALT = ALT from [Web_Patient] where UserName = a.UserName and ID < a.ID order by ID desc) C
where LastALT NOT BETWEEN 10 AND 50 AND PriorALt BETWEEN 10 AND 50
/*
id UserName ALT
6 李四 65
*/