create table 表(a int,no1 varchar(10),no2 varchar(10),no3 varchar(10),no4 varchar(10)
,total as case no1 when 1 then 1 else 0 end
+case no2 when 1 then 1 else 0 end
+case no3 when 1 then 1 else 0 end
+case no4 when 1 then 1 else 0 end
)
,total as case no1 when 1 then 1 else 0 end
+case no2 when 1 then 1 else 0 end
+case no3 when 1 then 1 else 0 end
+case no4 when 1 then 1 else 0 end
)
case no1 when 1 then 1 else 0 end
+case no2 when 1 then 1 else 0 end
+case no3 when 1 then 1 else 0 end
+case no4 when 1 then 1 else 0 end
--如果是在企业管理器中设计表,则直接在total字段的公式中输入:
case no1 when 1 then 1 else 0 end
+case no2 when 1 then 1 else 0 end
+case no3 when 1 then 1 else 0 end
+case no4 when 1 then 1 else 0 end
create table t5 (c1 varchar(1),c2 varchar(1),c3 varchar(1),xc as (case when c1='x' then 1 else 0 end)+(case when c2='x' then 1 else 0 end)+(case when c3='x' then 1 else 0 end))
go
insert into t5(c2,c3) select 'x','x' union all select 'x',''
insert into t5(c1,c3) select 'x','x' union all select 'x',''
select * from t5
c1 c2 c3 xc
NULL x x 2
NULL x 1
x NULL x 2
x NULL 1
create table 表(a int,no1 varchar(10),no2 varchar(10),no3 varchar(10),no4 varchar(10)
,total as case no1 when 'X' then 1 else 0 end
+case no2 when 'X' then 1 else 0 end
+case no3 when 'X' then 1 else 0 end
+case no4 when 'X' then 1 else 0 end--2.在现有表上添加字段
alter table 表 add total as
case no1 when 'x' then 1 else 0 end
+case no2 when 'x' then 1 else 0 end
+case no3 when 'x' then 1 else 0 end
+case no4 when 'x' then 1 else 0 end
--如果是在企业管理器中设计表,则直接在total字段的公式中输入:
case no1 when 'x' then 1 else 0 end
+case no2 when 'x' then 1 else 0 end
+case no3 when 'x' then 1 else 0 end
+case no4 when 'x' then 1 else 0 end
create table 表(a int,no1 varchar(10),no2 varchar(10),no3 varchar(10),no4 varchar(10)
,total as case no1 when 'X' then 1 else 0 end
+case no2 when 'X' then 1 else 0 end
+case no3 when 'X' then 1 else 0 end
+case no4 when 'X' then 1 else 0 end
)--插入数据
insert 表(a,no1,no2,no3,no4)
select 1,'X','X','X','X'
union all select 2,'','X','X',''
go--显示结果
select * from 表--删除测试
drop table 表/*--测试结果
a no1 no2 no3 no4 total
----------- ---------- ---------- ---------- ---------- -----------
1 X X X X 4
2 X X 2(所影响的行数为 2 行)--*/
则应在
alter table 表 add total as
case no1 when 'x' then 1 else 0 end
+case no2 when 'x' then 1 else 0 end
+case no3 when 'x' then 1 else 0 end
+case no4 when 'x' then 1 else 0 end
之前
删除total字段
alter table 表 drop column total