select case when 视图一列 = 'check' then 0 else 1 end from 视图
谢谢了!好还想问一下,视图中如何统计每一列0的个数 例如 a b c 1 2 0 0 0 1 0 1 0 1 1 1 0 0 0 2 2 1 统计的结果是 a b c 3 2 2
select count(a) a,count(b) b,count(c) c from 表这个和视图无关,跟表操作是一样的
select sum(case when a=0 then 1 else 0 end) as a sum(case when b=0 then 1 else 0 end) as b, sum(case when c=0 then 1 else 0 end) as c from tb
忘了count会统计0,谢谢阿汤哥提醒CREATE TABLE #tb (a int ,b int ,c int ) INSERT INTO #TB select '1', '2', '0' union all select '0', '0', '1' union all select '0', '1', '0' union all select '1', '1', '1' union all select '0', '0', '0' union all select '2', '2', '1'
select sum(case when a=0 then 1 else 0 end) AS a, sum(case when b=0 then 1 else 0 end) as b, sum(case when c=0 then 1 else 0 end) as c from #tb /* a b c ----------- ----------- ----------- 3 2 3
例如
a b c
1 2 0
0 0 1
0 1 0
1 1 1
0 0 0
2 2 1
统计的结果是
a b c
3 2 2
from 表这个和视图无关,跟表操作是一样的
sum(case when b=0 then 1 else 0 end) as b,
sum(case when c=0 then 1 else 0 end) as c
from tb
INSERT INTO #TB select '1', '2', '0'
union all select '0', '0', '1'
union all select '0', '1', '0'
union all select '1', '1', '1'
union all select '0', '0', '0'
union all select '2', '2', '1'
select sum(case when a=0 then 1 else 0 end) AS a,
sum(case when b=0 then 1 else 0 end) as b,
sum(case when c=0 then 1 else 0 end) as c
from #tb
/*
a b c
----------- ----------- -----------
3 2 3
(1 行受影响)
*/