--测试数据
create table #table1 (a1 int,a2 int,b1 int,b2 int,A组差 int,B组差 int,两组差的和 int)
insert into #table1 values (22,12,18,13,NULL,NULL,NULL)
insert into #table1 values (33,41,21,11,NULL,NULL,NULL)
相要的结果
/*
a1 a2 b1 b2 A组差 B组差 两组差的和
----------- ----------- ----------- ----------- ----------- ----------- -----------
22 12 18 13 10 5 15
41 10 21 11 31 10 41
*/
insert into #table1 values (22,12,18,13,NULL,NULL,NULL)
insert into #table1 values (33,41,21,11,NULL,NULL,NULL)update #table1 set A组差=abs(a1-a2),
B组差=abs(b1-b2),
两组差的和=abs(a1-a2)+abs(b1-b2)
select * from #table1 /*
a1 a2 b1 b2 A组差 B组差 两组差的和
----------- ----------- ----------- ----------- ----------- ----------- -----------
22 12 18 13 10 5 15
33 41 21 11 8 10 18
set A组差=abs(a1-a2),B组差=abs(b1-b2),两组差的和=abs(a1-a2)+abs(b1-b2) 这样?
insert into #table1 values (33,41,21,11)SELECT * FROM #table1
DROP TABLE #table1
/*
a1 a2 b1 b2 A组差 B组差 两组差的和
----------- ----------- ----------- ----------- ----------- ----------- -----------
22 12 18 13 10 5 15
33 41 21 11 8 10 18(2 行受影响)*/
换个思路,参考
create table #table1
(
a1 int,
a2 int,
b1 int,
b2 int,
A组差 as a1-a2,
B组差 as b1-b2,
两组差的和 as a1-a2+b1-b2 )
insert into #table1 values (22,12,18,13)
insert into #table1 values (33,41,21,11)
对每行都做相同的操作,不需要where条件来过滤,除非你指定只更新其中某些行
计算列是不错.谢谢
哪计算列在PowerDesigner怎么建?