分不够了，求教一个简单的sql写法

[liquidcn]

表：

id name value
1 a 0
2 b 0
3 b 1
4 c 0
5 c 0
6 a 1
7 a 0

要求：
列出所有value != 1的name，且不能重复

结果：
c


我用not in，但是看起来太傻了点：
select name from tb where name not in (select name from tb where value = 1 group by name) group by name
需要一个性能比较好的写法，谢谢

[饮水需思源]

SELECT [name],SUM(ISNULL(value,0)) AS Value 
FROM tablename
GROUP BY [name] HAVING SUM(ISNULL(value,0))=0

[-Tracy-McGrady-]

create table #tb
(id int,name varchar(5),value int)
insert into #tb
select 1      ,'a',         0 union all
select 2      ,'b',         0 union all
select 3      ,'b',         1 union all
select 4      ,'c',         0 union all
select 5      ,'c',         0 union all
select 6      ,'a',         1 union all
select 7      ,'a',         0

select name from #tb 
group by name having MAX(value)=0


name
-----
c

(1 行受影响)

[daiyueqiang2045]

create table #tb
(id int,name varchar(5),value int)
insert into #tb
select 1      ,'a',         0 union all
select 2      ,'b',         0 union all
select 3      ,'b',         1 union all
select 4      ,'c',         0 union all
select 5      ,'c',         0 union all
select 6      ,'a',         1 union all
select 7      ,'a',         0

select name from #tb 
group by name having MAX(value)=0


select 
name
from
(select * from #tb where value<>1) t 
group by name
having COUNT(name)=1

lz给出的结果存在问题吧 怎么会显示C呢， 4 c 0 5 c 0 这个c已经重复了

[liquidcn]

额。。。悲剧了。。。本来是想写个简单点的各位好明白我意思。。但是关键问题是，value这列的值其实是open和close，string类型的，不是int的。。肿么搞啊？

[唐诗三百首]

create table liq
(id int, name varchar(5), value varchar(5))

insert into liq
select 1, 'a', '0' union all
select 2, 'b', '0' union all
select 3, 'b', '1' union all
select 4, 'c', '0' union all
select 5, 'c', '0' union all
select 6, 'a', '1' union all
select 7, 'a', '0'


select distinct name 
 from liq a
 where not exists
 (select 1 
  from liq b where b.name=a.name and b.value='1')

/*
name
-----
c

(1 row(s) affected)
*/