分不够了，求教一个简单的sql写法
表：
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
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 行受影响)
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
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)
*/
