22,209
社区成员
发帖
与我相关
我的任务
分享
--查询
select *
from
(select t.*,s.ResultID,s.Suggestion from ExamineData t
left join StandardData s on EyesightType='s'
and t.age >=case when FromAgeUnit ='月' then FromAge *1./12 else FromAge end
and t.age <= case when toage is null then t.age else toage end
and (t.LSValue>= (case when LeftInterZone ='[' then FromData else null end) or t.LSValue> (case when LeftInterZone ='(' then FromData else null end) )
and (t.LSValue<= (case when RightInterZone =']' then toData else null end) or t.LSValue< (case when RightInterZone =')' then toData else null end) )
union all
select t.*,s.ResultID,s.Suggestion from ExamineData t
left join StandardData s on EyesightType='c'
and t.age >=case when FromAgeUnit ='月' then FromAge *1./12 else FromAge end
and t.age <= case when toage is null then t.age else toage end
and (t.LcValue>= (case when LeftInterZone ='[' then FromData else null end) or t.LcValue> (case when LeftInterZone ='(' then FromData else null end) )
and (t.LcValue<= (case when RightInterZone =']' then toData else null end) or t.LcValue< (case when RightInterZone =')' then toData else null end) )
union all
select t.*,s.ResultID,s.Suggestion from ExamineData t
left join StandardData s on EyesightType='s'
and t.age >=case when FromAgeUnit ='月' then FromAge *1./12 else FromAge end
and t.age <= case when toage is null then t.age else toage end
and (t.RSValue>= (case when LeftInterZone ='[' then FromData else null end) or t.RSValue> (case when LeftInterZone ='(' then FromData else null end) )
and (t.RSValue<= (case when RightInterZone =']' then toData else null end) or t.RSValue< (case when RightInterZone =')' then toData else null end) )
union all
select t.*,s.ResultID,s.Suggestion from ExamineData t
left join StandardData s on EyesightType='s'
and t.age >=case when FromAgeUnit ='月' then FromAge *1./12 else FromAge end
and t.age <= case when toage is null then t.age else toage end
and (t.RcValue>= (case when LeftInterZone ='[' then FromData else null end) or t.RcValue> (case when LeftInterZone ='(' then FromData else null end) )
and (t.RcValue<= (case when RightInterZone =']' then toData else null end) or t.RcValue< (case when RightInterZone =')' then toData else null end) )
) t
where
t.ResultID = (select max(ResultID) from
(select t.*,s.ResultID,s.Suggestion from ExamineData t
left join StandardData s on EyesightType='s'
and t.age >=case when FromAgeUnit ='月' then FromAge *1./12 else FromAge end
and t.age <= case when toage is null then t.age else toage end
and (t.LSValue>= (case when LeftInterZone ='[' then FromData else null end) or t.LSValue> (case when LeftInterZone ='(' then FromData else null end) )
and (t.LSValue<= (case when RightInterZone =']' then toData else null end) or t.LSValue< (case when RightInterZone =')' then toData else null end) )
union all
select t.*,s.ResultID,s.Suggestion from ExamineData t
left join StandardData s on EyesightType='c'
and t.age >=case when FromAgeUnit ='月' then FromAge *1./12 else FromAge end
and t.age <= case when toage is null then t.age else toage end
and (t.LcValue>= (case when LeftInterZone ='[' then FromData else null end) or t.LcValue> (case when LeftInterZone ='(' then FromData else null end) )
and (t.LcValue<= (case when RightInterZone =']' then toData else null end) or t.LcValue< (case when RightInterZone =')' then toData else null end) )
union all
select t.*,s.ResultID,s.Suggestion from ExamineData t
left join StandardData s on EyesightType='s'
and t.age >=case when FromAgeUnit ='月' then FromAge *1./12 else FromAge end
and t.age <= case when toage is null then t.age else toage end
and (t.RSValue>= (case when LeftInterZone ='[' then FromData else null end) or t.RSValue> (case when LeftInterZone ='(' then FromData else null end) )
and (t.RSValue<= (case when RightInterZone =']' then toData else null end) or t.RSValue< (case when RightInterZone =')' then toData else null end) )
union all
select t.*,s.ResultID,s.Suggestion from ExamineData t
left join StandardData s on EyesightType='s'
and t.age >=case when FromAgeUnit ='月' then FromAge *1./12 else FromAge end
and t.age <= case when toage is null then t.age else toage end
and (t.RcValue>= (case when LeftInterZone ='[' then FromData else null end) or t.RcValue> (case when LeftInterZone ='(' then FromData else null end) )
and (t.RcValue<= (case when RightInterZone =']' then toData else null end) or t.RcValue< (case when RightInterZone =')' then toData else null end) )
) b where t.id=b.id)
--结果
/*
(4 行受影响)
(34 行受影响)
(4 行受影响)
ID Age LSValue LCValue RSValue RCValue ResultID Suggestion
----------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ----------- --------------------
2 3.0 4.00 4.00 4.00 4.00 4 异常
3 5.0 3.50 2.00 3.50 2.00 4 异常
1 0.5 2.50 -1.20 3.00 -1.00 4 到眼科诊治
2 3.0 4.00 4.00 4.00 4.00 4 到眼科诊治
3 5.0 3.50 2.00 3.50 2.00 4 到眼科诊治
4 11.0 1.00 1.00 1.00 1.00 4 到眼科诊治
2 3.0 4.00 4.00 4.00 4.00 4 异常
3 5.0 3.50 2.00 3.50 2.00 4 异常
1 0.5 2.50 -1.20 3.00 -1.00 4 到眼科诊治
2 3.0 4.00 4.00 4.00 4.00 4 异常
(10 行受影响)
*/
--建立测试环境
IF OBJECT_ID('Result') IS NOT NULL DROP TABLE Result
GO
CREATE TABLE Result
(
ResultID int ,
Result varchar(10),
Seriouslevel int
)
GO
INSERT Result
select '1','正常','1' union all
select '2','保健','2' union all
select '3','复查','3' union all
select '4','异常','4'
go
IF OBJECT_ID('StandardData') IS NOT NULL DROP TABLE StandardData
GO
CREATE TABLE StandardData
(
EyesightType varchar(1) ,
FromAge int ,
FromAgeUnit varchar(2) ,
ToAge int ,
ToAgeUnit varchar(2) ,
LeftInterZone varchar(1) ,
FromData numeric(9,2) ,
ToData numeric(9,2) ,
RightInterZone varchar(1) ,
ResultID int ,
Suggestion varchar(20)
)
GO
INSERT StandardData
select 'c','6','月','2','年','[','-9.99','-1',']','4','到眼科诊治' union all
select 'c','2','年','4','年','[','-9.99','-1',']','4','到眼科诊治' union all
select 'c','4','年','6','年','[','-9.99','-1',']','4','到眼科诊治' union all
select 'c','6','年','10','年','[','-9.99','-1',']','4','到眼科诊治' union all
select 'c','10','年',null,null,'[','-9.99','-1',']','4','到眼科诊治' union all
select 'c','6','月','2','年','(','-1','1',')','1','正常' union all
select 'c','2','年','4','年','(','-1','1',')','1','正常' union all
select 'c','4','年','6','年','(','-1','1',')','1','正常' union all
select 'c','6','年','10','年','(','-1','1',')','1','正常' union all
select 'c','10','年',null,null,'(','-1','1',')','1','正常' union all
select 'c','6','月','2','年','[','1','9.99',']','4','到眼科诊治' union all
select 'c','2','年','4','年','[','1','9.99',']','4','到眼科诊治' union all
select 'c','4','年','6','年','[','1','9.99',']','4','到眼科诊治' union all
select 'c','6','年','10','年','[','1','9.99',']','4','到眼科诊治' union all
select 'c','10','年',null,null,'[','1','9.99',']','4','到眼科诊治' union all
select 's','6','月','2','年','[','-9.99','-1',']','4','到眼科诊治' union all
select 's','2','年','4','年','[','-9.99','-1',']','4','到眼科诊治' union all
select 's','4','年','6','年','[','-9.99','-1',']','4','到眼科诊治' union all
select 's','6','年','10','年','[','-9.99','-1',']','4','到眼科诊治' union all
select 's','10','年',null,null,'[','-9.99','-1.5',']','4','到眼科诊治' union all
select 's','6','月','2','年','[','-1','2.5',')','2','眼保健' union all
select 's','2','年','4','年','(','-1','2',')','2','眼保健' union all
select 's','4','年','6','年','(','-1','2',')','2','眼保健' union all
select 's','6','年','10','年','(','-1','1.5',')','2','眼保健' union all
select 's','6','月','2','年','[','2.5','3.5',']','1','正常' union all
select 's','2','年','4','年','[','2','3',')','1','正常' union all
select 's','4','年','6','年','[','2','2.5',')','1','正常' union all
select 's','6','年','10','年','[','1.5','2',')','1','正常' union all
select 's','10','年',null,null,'(','-1.5','1.5',')','1','正常' union all
select 's','6','月','2','年','(','3.5','9.99',']','4','异常' union all
select 's','2','年','4','年','[','3','9.99',']','4','异常' union all
select 's','4','年','6','年','[','2.5','9.99',']','4','异常' union all
select 's','6','年','10','年','[','2','9.99',']','4','异常' union all
select 's','10','年',null,null,'[','1.5','9.99',']','4','异常'
go
IF OBJECT_ID('ExamineData') IS NOT NULL DROP TABLE ExamineData
GO
CREATE TABLE ExamineData
(
ID int,
Age numeric(4,1),
LSValue numeric(9,2),
LCValue numeric(9,2),
RSValue numeric(9,2),
RCValue numeric(9,2)
)
GO
INSERT ExamineData
select 1,0.5,2.5,-1.2,3,-1 union all
select 2,3,4,4,4,4 union all
select 3,5,3.5,2,3.5,2 union all
select 4,11,1,1,1,1
go
--try
;with china as
(
select ResultID,Suggestion,fromage=(case when FromAgeUnit = 'y' then FromAge*12 else FromAge end),
toage=(case when ToAgeUnit='y' then ToAge*12 else ToAge end),
leftinterzone=(case when LeftInterZone='[' then FromData else FromData+0.0000001 end),
rightinterzone=(case when RightInterZone='[' then ToDate else ToDate-0.0000001 end) from
CM_EyesightStandardData where HospitalID=41
)
select ResultID,Suggestion from china where fromage<=5*12
and toage>5*12
and ((leftinterzone<=2.0 and rightinterzone>=2.0)
or(leftinterzone>=2.0 and rightinterzone<=2.0))
select ResultID,Suggestion
from CM_EyesightStandardData
where 5*12>= case when FromAgeUnit = 'y' then FromAge*12 else FromAge end
and 5*12 < case when ToAgeUnit='y' then ToAge*12 else ToAge end
and -2.0 between case when LeftInterZone='[' then FromData else FromData+0.0000001 end
and case when RightInterZone='[' then ToDate else ToDate-0.0000001 end
and HospitalID=41