Update语句求助,急!!!
模型如下:
A、B两个传感器在几个时间点内采上来的数据如下:
| t1 t2 t3 ...
--|---------------------
A | 1 2 3 ...
B | 20 40 80 ...
有两个指标C、D依赖于A、B的数值,公式:C=A+B,D=A*B,最终希望获得
| t1 t2 t3
--|-----------------
A | 1 2 3
B | 20 40 80
C | 21 42 83
D | 20 80 240
create table tbl(cID char(8), cTime char(8), nValue int)
insert tbl
select 'A ', 't1 ', 1 union
select 'A ', 't2 ', 2 union
select 'A ', 't3 ', 3 union
select 'B ', 't1 ', 20 union
select 'B ', 't2 ', 40 union
select 'B ', 't3 ', 80 union
select 'C ', 't1 ', NULL union
select 'C ', 't2 ', NULL union
select 'C ', 't3 ', NULL union
select 'D ', 't1 ', NULL union
select 'D ', 't2 ', NULL union
select 'D ', 't3 ', NULL
下面求Update语句:
update tbl set nValue= ....where cID= 'C '
update tbl set nValue= ....where cID= 'D '
问题:
1、如何写update语句? (10分)
2、能否用一条update完成而不是两条?(10分)
3、在不限定的情况下是否有比update更好的方法?(10分)
4、假设公式放在另一张表里,如何组合出SQL语句?即增加一张表:(20分)
create table tbl_Formula(cID char(8), cFormula char(120))
insert tbl_Formula select 'C ', 'A+B ' union
select 'D ', 'A*B '
[解决办法]
1、如何写update语句? (10分)
2、能否用一条update完成而不是两条?(10分)
update tablename
set t1=case when 指标= 'c ' then (select sum(t1) from tablename where 指标 in ( 'a ', 'b '))
when 指标= 'd ' then (select t1 from tablename where 指标 = 'a ')*(select t1 from tablename where 指标 = 'b ')
else t1
end,
t2=case when 指标= 'c ' then (select sum(t2) from tablename where 指标 in ( 'a ', 'b '))
when 指标= 'd ' then (select t2 from tablename where 指标 = 'a ')*(select t2 from tablename where 指标 = 'b ')
else t2
end,
t3=case when 指标= 'c ' then (select sum(t3) from tablename where 指标 in ( 'a ', 'b '))
when 指标= 'd ' then (select t3 from tablename where 指标 = 'a ')*(select t3 from tablename where 指标 = 'b ')
else t3
end,
...
[解决办法]
qqdd21cn(驴在天涯) ( ) 信誉:100 Blog 2007-03-23 08:43:12 得分: 0
请问如何把表转过来?也就是如何构建这张表?麻烦用create table,insert语句描述一下,谢谢~~~
-------------
Yang_(扬帆破浪) 的應該是這個意思
create table tbl(T Varchar(10), A Int, B Int, C Int, D Int)
insert tbl
select 't1 ', 1, 20, NULL, NULL union
select 't2 ', 2, 40, NULL, NULL union
select 't3 ', 3, 80, NULL, NULL
GO
Update tbl Set C= A + B, D = A * B
Select * from tbl
GO
Drop Table tbl
--Result
/*
TABCD
t11202120
t22404280
t338083240
*/