首页新闻找找看学习计划

SQL更新多列的问题

0
悬赏园豆:100 [已解决问题] 解决于 2014-04-07 10:46

    这是一张基本的数据表(属性不完整),现在的需求是:根据某单位的最初年度的的某项指标值为1进行更新,例如:95年武汉中医医院的B超指标为1,则要对96-14年这家单位的B超指标值全部更新为1;再如,99年武汉中医医院的XX(表中某属性)指标为1,则要对99-14年这家单位的XX指标值全部更新为1,95-98年这家单位的XX指标值不变(即为0)。该如何实现?

天堂的鸽子的主页 天堂的鸽子 | 初学一级 | 园豆:138
提问于:2014-04-04 13:03
< >
分享
最佳答案
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
收获园豆:90
Giant Liu | 小虾三级 |园豆:888 | 2014-04-05 23:49

谢谢!

 

天堂的鸽子 | 园豆:138 (初学一级) | 2014-04-07 10:45
其他回答(3)
0

帮顶

【秦时明月】 | 园豆:801 (小虾三级) | 2014-04-04 13:12
0

好像只能用游标比较简单点

收获园豆:10
清海扬波 | 园豆:845 (小虾三级) | 2014-04-04 14:09

怎么写?

 

支持(0) 反对(0) 天堂的鸽子 | 园豆:138 (初学一级) | 2014-04-04 18:23
0
DECLARE @bchao INT
DECLARE @date DATETIME
DECLARE @chaju INT
DECLARE @chushi INT
SET @chushi=0
SELECT @bchao=b超 FROMWHERE 医院名称=武汉中医院 AND 年份=1995
IF (@bchao=1)
SET @chaju=2014-1999
WHILE @chushi<@chaju
BEGIN
SET @date=1999
UPDATESET b超=1 WHERE  医院名称=武汉中医院 AND 年份=@date
SET @date=DATEADD(yyyy,1,@date)
SET @chushi=@chushi+1
END
桦仔 | 园豆:62 (初学一级) | 2014-04-05 01:07

谢谢华仔,不过你好像没有明白的题目的意思……

支持(0) 反对(0) 天堂的鸽子 | 园豆:138 (初学一级) | 2014-04-05 20:16
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册