--try SELECT DERIVEDTBL.usid, DERIVEDTBL.xxdm, DERIVEDTBL.cvalue, DERIVEDTBL.date1, DERIVEDTBL.pd1, tj_store_grade.PD AS pd2 FROM (SELECT YKB.studyid usid, YKB.XXDM xxdm, YKB.CValue cvalue, tj_grade.PD pd1, CONVERT(varchar(10), YKB.StudyDate, 121) date1 FROM YKB LEFT OUTER JOIN tj_grade ON CASE ISNUMERIC(YKB.CValue) WHEN 1 THEN CAST(ROUND(YKB.CValue,0) as int) else tj_grade.MINVAL-1 end between tj_grade.MINVAL and tj_grade.MAXVAL AND YKB.XXDM = tj_grade.XXDM WHERE (YKB.StudyID = '1211180001')) DERIVEDTBL LEFT OUTER JOIN tj_store_grade ON DERIVEDTBL.date1 = tj_store_grade.TJDATE AND tj_store_grade.KSBH = '0003' AND DERIVEDTBL.usid = tj_store_grade.USERID
--如果要过滤掉不是数字的行,可以在where后面加上条件ISNUMERIC(YKB.CValue)=1 SELECT DERIVEDTBL.usid, DERIVEDTBL.xxdm, DERIVEDTBL.cvalue, DERIVEDTBL.date1, DERIVEDTBL.pd1, tj_store_grade.PD AS pd2 FROM (SELECT YKB.studyid usid, YKB.XXDM xxdm, YKB.CValue cvalue, tj_grade.PD pd1, CONVERT(varchar(10), YKB.StudyDate, 121) date1 FROM YKB LEFT OUTER JOIN tj_grade ON CASE ISNUMERIC(YKB.CValue) WHEN 1 THEN CAST(ROUND(YKB.CValue,0) as int) else tj_grade.MINVAL-1 end between tj_grade.MINVAL and tj_grade.MAXVAL AND YKB.XXDM = tj_grade.XXDM WHERE ISNUMERIC(YKB.CValue)=1 and (YKB.StudyID = '1211180001')) DERIVEDTBL LEFT OUTER JOIN tj_store_grade ON DERIVEDTBL.date1 = tj_store_grade.TJDATE AND tj_store_grade.KSBH = '0003' AND DERIVEDTBL.usid = tj_store_grade.USERID
YKB.CValue >= tj_grade.MINVAL AND
YKB.CValue <= tj_grade.MAXVAL AND YKB.XXDM = tj_grade.XXDM这个几个字段里没有INT型?
SELECT DERIVEDTBL.usid, DERIVEDTBL.xxdm, DERIVEDTBL.cvalue, DERIVEDTBL.date1,
DERIVEDTBL.pd1, tj_store_grade.PD AS pd2
FROM (SELECT YKB.studyid usid, YKB.XXDM xxdm, YKB.CValue cvalue, tj_grade.PD pd1,
CONVERT(varchar(10), YKB.StudyDate, 121) date1
FROM YKB LEFT OUTER JOIN tj_grade
ON CASE ISNUMERIC(YKB.CValue) WHEN 1 THEN CAST(ROUND(YKB.CValue,0) as int) else tj_grade.MINVAL-1 end
between tj_grade.MINVAL and tj_grade.MAXVAL
AND YKB.XXDM = tj_grade.XXDM
WHERE (YKB.StudyID = '1211180001')) DERIVEDTBL LEFT OUTER JOIN
tj_store_grade ON DERIVEDTBL.date1 = tj_store_grade.TJDATE AND
tj_store_grade.KSBH = '0003' AND DERIVEDTBL.usid = tj_store_grade.USERID
SELECT DERIVEDTBL.usid, DERIVEDTBL.xxdm, DERIVEDTBL.cvalue, DERIVEDTBL.date1,
DERIVEDTBL.pd1, tj_store_grade.PD AS pd2
FROM (SELECT YKB.studyid usid, YKB.XXDM xxdm, YKB.CValue cvalue, tj_grade.PD pd1,
CONVERT(varchar(10), YKB.StudyDate, 121) date1
FROM YKB LEFT OUTER JOIN tj_grade
ON CASE ISNUMERIC(YKB.CValue) WHEN 1 THEN CAST(ROUND(YKB.CValue,0) as int) else tj_grade.MINVAL-1 end
between tj_grade.MINVAL and tj_grade.MAXVAL
AND YKB.XXDM = tj_grade.XXDM
WHERE ISNUMERIC(YKB.CValue)=1 and (YKB.StudyID = '1211180001')) DERIVEDTBL LEFT OUTER JOIN
tj_store_grade ON DERIVEDTBL.date1 = tj_store_grade.TJDATE AND
tj_store_grade.KSBH = '0003' AND DERIVEDTBL.usid = tj_store_grade.USERID