这是一张基本的数据表(属性不完整),现在的需求是:根据某单位的最初年度的的某项指标值为1进行更新,例如:95年武汉中医医院的B超指标为1,则要对96-14年这家单位的B超指标值全部更新为1;再如,99年武汉中医医院的XX(表中某属性)指标为1,则要对99-14年这家单位的XX指标值全部更新为1,95-98年这家单位的XX指标值不变(即为0)。该如何实现?
--create table HospitalInfo( --Years int, --Code nvarchar(10), --Name nvarchar(100), --Prop1 bit, --Prop2 bit, --Prop3 bit, --Prop4 bit) --insert into HospitalInfo values --(1990,'900001','Hospital1',0,0,0,0), --(1991,'900001','Hospital1',0,0,0,0), --(1992,'900001','Hospital1',1,0,0,0), --(1993,'900001','Hospital1',0,1,0,0), --(1994,'900001','Hospital1',0,0,1,0), --(1995,'900001','Hospital1',0,0,0,1), --(1990,'900002','Hospital2',0,0,0,0), --(1991,'900002','Hospital2',0,0,0,1), --(1992,'900002','Hospital2',0,0,1,0), --(1993,'900002','Hospital2',0,1,0,0), --(1994,'900002','Hospital2',1,0,0,0), --(1995,'900002','Hospital2',0,0,0,0), --(1990,'900003','Hospital3',0,0,0,0), --(1991,'900003','Hospital3',1,1,0,0), --(1992,'900003','Hospital3',0,0,1,1), --(1993,'900003','Hospital3',0,1,0,0), --(1994,'900003','Hospital3',1,0,0,0), --(1995,'900003','Hospital3',0,0,0,0) select * from HospitalInfo --先计算有多少家医院 select ROW_NUMBER() over (order by code) as id,Code into #Hospital from HospitalInfo group by Code declare @MinID int select @MinID=MIN(id) from #Hospital print @MinID while @MinID is not null begin declare @Code nvarchar(10) select @Code=Code from #Hospital where id=@MinID--当前医院 declare @MinColumnID int select @MinColumnID=MIN(column_id) from sys.columns where object_id=object_id('HospitalInfo') and name not in ('Years','Code','Name')--要计算的列(属性) while @MinColumnID is not null begin declare @ColumnName nvarchar(100) select @ColumnName=name from sys.columns where object_id=object_id('HospitalInfo') and name not in ('Years','Code','Name') and column_id=@MinColumnID--当前列 --拿到最小为1的年份 declare @Sql nvarchar(1000) declare @Paramery nvarchar(1000) declare @MinYear int set @Sql='select @Yearout=MIN(Years) from HospitalInfo where Code='+@Code+' and '+@ColumnName+'=1' set @Paramery='@Yearout int output' exec sp_executesql @Sql,@Paramery,@Yearout=@MinYear output --更新 set @Sql='update HospitalInfo set '+@ColumnName+'=1 where Code='+@Code+' and Years>='+Convert(varchar(4),@MinYear) print @Sql exec(@Sql) select @MinColumnID=MIN(column_id) from sys.columns where object_id=object_id('HospitalInfo') and name not in ('Years','Code','Name') and column_id>@MinColumnID--下个计算列 end select @MinID=MIN(id) from #Hospital where id>@MinID--下家医院 end drop table #Hospital go
谢谢!
帮顶
好像只能用游标比较简单点
怎么写?
DECLARE @bchao INT DECLARE @date DATETIME DECLARE @chaju INT DECLARE @chushi INT SET @chushi=0 SELECT @bchao=b超 FROM 表 WHERE 医院名称=武汉中医院 AND 年份=1995 IF (@bchao=1) SET @chaju=2014-1999 WHILE @chushi<@chaju BEGIN SET @date=1999 UPDATE 表 SET b超=1 WHERE 医院名称=武汉中医院 AND 年份=@date SET @date=DATEADD(yyyy,1,@date) SET @chushi=@chushi+1 END
谢谢华仔,不过你好像没有明白的题目的意思……