可以de declare @sql varchar(8000) declare @where varchar(8000)SET @where=" where (既往血压=''1级高血压'' ) And ... ' SET @sql='SELECT @s1=count(*) FROM 高血压临床评估病史询问 ' + @where Exec (@sql) --使用动态SQL就可以了
为什么不呢? SELECT @s='男' SELECT @s1=count(*) FROM 高血压临床评估病史询问 WHERE (既往血压='1级高血压' ) AND (出生日期>=YEAR(GETDATE())-44 OR 出生日期<=YEAR(GETDATE())-35) AND (MONTH(询问日期)+1=MONTH(GETDATE())) AND (性别=@s)
SET @where=" where (既往血压=''1级高血压'' ) And ... ' /\ 字符串里面的单引号用2个转义一下。
CREATE PROCEDURE T_BOYXYAS DECLARE @age1 varchar(50),@s1 int,@s2 int,@s3 int,@scount int DECLARE @f1 int,@f2 int,@f3 int,@fcount int declare @sql varchar(8000) declare @where varchar(8000)set @where ="WHERE (既往血压='1级高血压' ) AND (出生日期>=YEAR(GETDATE())-44 OR 出生日期<=YEAR(GETDATE())-35) AND (MONTH(询问日期)+1=MONTH(GETDATE())) AND (YEAR(询问日期)=YEAR(GETDATE())) AND (性别='男')" set @sql='@s1=count(*) FROM 高血压临床评估病史询问' + @where ---男性上月管理数 exec @sqlSELECT @s2=count(*) FROM 高血压临床评估病史询问 WHERE (既往血压='2级高血压' ) AND (出生日期>=YEAR(GETDATE())-44 OR 出生日期<=YEAR(GETDATE())-35) AND (MONTH(询问日期)+1=MONTH(GETDATE())) AND (性别='男') AND (YEAR(询问日期)=YEAR(GETDATE())) SELECT @s3=count(*) FROM 高血压临床评估病史询问 WHERE (既往血压='3级高血压' ) AND (出生日期>=YEAR(GETDATE())-44 OR 出生日期<=YEAR(GETDATE())-35) AND (MONTH(询问日期)+1=MONTH(GETDATE())) AND (性别='男') AND (YEAR(询问日期)=YEAR(GETDATE())) SET @age1='35-44' SET @scount=@s1+@s2+@s3 ---男性上月发病人数 SELECT @f1=count(*) FROM 高血压临床评估病史询问 WHERE (既往血压='1级高血压' ) AND (出生日期>=YEAR(GETDATE())-44 OR 出生日期<=YEAR(GETDATE())-35) AND (MONTH(询问日期)=MONTH(GETDATE())) AND (性别='男') AND (YEAR(询问日期)=YEAR(GETDATE())) SELECT @f2=count(*) FROM 高血压临床评估病史询问 WHERE (既往血压='2级高血压' ) AND (出生日期>=YEAR(GETDATE())-44 OR 出生日期<=YEAR(GETDATE())-35) AND (MONTH(询问日期)=MONTH(GETDATE())) AND (性别='男') AND (YEAR(询问日期)=YEAR(GETDATE())) SELECT @f3=count(*) FROM 高血压临床评估病史询问 WHERE (既往血压='3级高血压' ) AND (出生日期>=YEAR(GETDATE())-44 OR 出生日期<=YEAR(GETDATE())-35) AND (MONTH(询问日期)=MONTH(GETDATE())) AND (性别='男') AND (YEAR(询问日期)=YEAR(GETDATE())) SET @fcount=@f1+@f2+@f3INSERT INTO 高血压管理(年龄,上月一级,上月二级,上月三级,上月合计,新发一级,新发二级,新发三级,新发合计) VALUES(@age1,@s1,@s2,@s3,@scount,@f1,@f2,@f3,@fcount) 我有这么多呀
你的Sql语句应该可以简化一些,可以用group by
完全可以,设置BOOL变量,获得你那些比较得指就可以了
---男性上月发病人数 SELECT @f1=count(*) FROM 高血压临床评估病史询问 WHERE (既往血压='1级高血压' ) AND (出生日期>=YEAR(GETDATE())-44 OR 出生日期<=YEAR(GETDATE())-35) AND (MONTH(询问日期)=MONTH(GETDATE())) AND (性别='男') AND (YEAR(询问日期)=YEAR(GETDATE())) SELECT @f2=count(*) FROM 高血压临床评估病史询问 WHERE (既往血压='2级高血压' ) AND (出生日期>=YEAR(GETDATE())-44 OR 出生日期<=YEAR(GETDATE())-35) AND (MONTH(询问日期)=MONTH(GETDATE())) AND (性别='男') AND (YEAR(询问日期)=YEAR(GETDATE())) SELECT @f3=count(*) FROM 高血压临床评估病史询问 WHERE (既往血压='3级高血压' ) AND (出生日期>=YEAR(GETDATE())-44 OR 出生日期<=YEAR(GETDATE())-35) AND (MONTH(询问日期)=MONTH(GETDATE())) AND (性别='男') AND (YEAR(询问日期)=YEAR(GETDATE())) SET @fcount=@f1+@f2+@f3--××××××××××用下面的可以吗?××××××××××× ---男性上月发病人数 SELECT @fcount=count(*) FROM 高血压临床评估病史询问 WHERE 既往血压 in ('1级高血压','2级高血压','2级高血压') AND (出生日期>=YEAR(GETDATE())-44 OR 出生日期<=YEAR(GETDATE())-35) AND (MONTH(询问日期)=MONTH(GETDATE())) AND (性别='男') AND (YEAR(询问日期)=YEAR(GETDATE()))
--查询年龄在35-44之间的既往血压在1-3级的男性的 上月发病人数SELECT @fcount=count(*) FROM 高血压临床评估病史询问 WHERE 既往血压 in ('1级高血压','2级高血压','3级高血压') And datediff(year,出生日期,getdate()) between 35 And 44 And datediff(month,询问日期,getdate())=1 And 性别='男'
declare @where varchar(8000)SET @where=" where (既往血压=''1级高血压'' ) And ... '
SET @sql='SELECT @s1=count(*) FROM 高血压临床评估病史询问 ' + @where
Exec (@sql) --使用动态SQL就可以了
SELECT @s='男'
SELECT @s1=count(*) FROM 高血压临床评估病史询问
WHERE (既往血压='1级高血压' ) AND (出生日期>=YEAR(GETDATE())-44 OR 出生日期<=YEAR(GETDATE())-35) AND (MONTH(询问日期)+1=MONTH(GETDATE())) AND (性别=@s)
/\
字符串里面的单引号用2个转义一下。
DECLARE @age1 varchar(50),@s1 int,@s2 int,@s3 int,@scount int
DECLARE @f1 int,@f2 int,@f3 int,@fcount int
declare @sql varchar(8000)
declare @where varchar(8000)set @where ="WHERE (既往血压='1级高血压' ) AND (出生日期>=YEAR(GETDATE())-44 OR 出生日期<=YEAR(GETDATE())-35) AND (MONTH(询问日期)+1=MONTH(GETDATE())) AND (YEAR(询问日期)=YEAR(GETDATE())) AND (性别='男')"
set @sql='@s1=count(*) FROM 高血压临床评估病史询问' + @where
---男性上月管理数
exec @sqlSELECT @s2=count(*) FROM 高血压临床评估病史询问
WHERE (既往血压='2级高血压' ) AND (出生日期>=YEAR(GETDATE())-44 OR 出生日期<=YEAR(GETDATE())-35) AND (MONTH(询问日期)+1=MONTH(GETDATE())) AND (性别='男') AND (YEAR(询问日期)=YEAR(GETDATE()))
SELECT @s3=count(*) FROM 高血压临床评估病史询问
WHERE (既往血压='3级高血压' ) AND (出生日期>=YEAR(GETDATE())-44 OR 出生日期<=YEAR(GETDATE())-35) AND (MONTH(询问日期)+1=MONTH(GETDATE())) AND (性别='男') AND (YEAR(询问日期)=YEAR(GETDATE()))
SET @age1='35-44'
SET @scount=@s1+@s2+@s3
---男性上月发病人数
SELECT @f1=count(*) FROM 高血压临床评估病史询问
WHERE (既往血压='1级高血压' ) AND (出生日期>=YEAR(GETDATE())-44 OR 出生日期<=YEAR(GETDATE())-35) AND (MONTH(询问日期)=MONTH(GETDATE())) AND (性别='男') AND (YEAR(询问日期)=YEAR(GETDATE()))
SELECT @f2=count(*) FROM 高血压临床评估病史询问
WHERE (既往血压='2级高血压' ) AND (出生日期>=YEAR(GETDATE())-44 OR 出生日期<=YEAR(GETDATE())-35) AND (MONTH(询问日期)=MONTH(GETDATE())) AND (性别='男') AND (YEAR(询问日期)=YEAR(GETDATE()))
SELECT @f3=count(*) FROM 高血压临床评估病史询问
WHERE (既往血压='3级高血压' ) AND (出生日期>=YEAR(GETDATE())-44 OR 出生日期<=YEAR(GETDATE())-35) AND (MONTH(询问日期)=MONTH(GETDATE())) AND (性别='男') AND (YEAR(询问日期)=YEAR(GETDATE()))
SET @fcount=@f1+@f2+@f3INSERT INTO 高血压管理(年龄,上月一级,上月二级,上月三级,上月合计,新发一级,新发二级,新发三级,新发合计)
VALUES(@age1,@s1,@s2,@s3,@scount,@f1,@f2,@f3,@fcount)
我有这么多呀
SELECT @f1=count(*) FROM 高血压临床评估病史询问
WHERE (既往血压='1级高血压' ) AND (出生日期>=YEAR(GETDATE())-44 OR 出生日期<=YEAR(GETDATE())-35) AND (MONTH(询问日期)=MONTH(GETDATE())) AND (性别='男') AND (YEAR(询问日期)=YEAR(GETDATE()))
SELECT @f2=count(*) FROM 高血压临床评估病史询问
WHERE (既往血压='2级高血压' ) AND (出生日期>=YEAR(GETDATE())-44 OR 出生日期<=YEAR(GETDATE())-35) AND (MONTH(询问日期)=MONTH(GETDATE())) AND (性别='男') AND (YEAR(询问日期)=YEAR(GETDATE()))
SELECT @f3=count(*) FROM 高血压临床评估病史询问
WHERE (既往血压='3级高血压' ) AND (出生日期>=YEAR(GETDATE())-44 OR 出生日期<=YEAR(GETDATE())-35) AND (MONTH(询问日期)=MONTH(GETDATE())) AND (性别='男') AND (YEAR(询问日期)=YEAR(GETDATE()))
SET @fcount=@f1+@f2+@f3--××××××××××用下面的可以吗?×××××××××××
---男性上月发病人数
SELECT @fcount=count(*) FROM 高血压临床评估病史询问
WHERE 既往血压 in ('1级高血压','2级高血压','2级高血压') AND (出生日期>=YEAR(GETDATE())-44 OR 出生日期<=YEAR(GETDATE())-35) AND (MONTH(询问日期)=MONTH(GETDATE())) AND (性别='男') AND (YEAR(询问日期)=YEAR(GETDATE()))
WHERE 既往血压 in ('1级高血压','2级高血压','3级高血压')
And datediff(year,出生日期,getdate()) between 35 And 44
And datediff(month,询问日期,getdate())=1 And 性别='男'