如表有ID和状态两个字段
ID State
1 1
1 1
2 1
2 0
2 1
3 1
3 0
4 1
5 0
6 1
6 1查找出同一ID状态都是1的所有ID(1,4,6)
先谢谢大家了。
ID State
1 1
1 1
2 1
2 0
2 1
3 1
3 0
4 1
5 0
6 1
6 1查找出同一ID状态都是1的所有ID(1,4,6)
先谢谢大家了。
解决方案 »
- 求助,请看代码段,按钮连续点击3次就WCF通信错误,为何?
- 请教正则表达式遇到的问题
- Gridview 怎么随着我表的改编自动更新啊,急!!!!
- 微软企业库使用ODP.net访问数据库
- 100分帖子 要全部翻译过来 废话请不要留 VB 改C# 在线等待!!!
- trackBar控件的用法,怎么去和打开的音乐绑定起来
- 如何定义自己类型的文件
- VC#写的程序,如何才能在没有安装.net 2.0的电脑上运行呢
- .net中调用存储过程,其中存储过程调用一个局部临时表
- 我是一个刚接触C#的学生,水平非常的低,有1断代码,需要大家帮我改进1下,谢了:)
- Winform中调用计算文件
- c# 问个比较弱的问题 "20090319"这种格式的字符串如何转换为合法的日期格式?
不过如果实在没的用了, 就尝试下这个吧:select id from idAndState
where id=1 and state =1
except
select id from idAndState
where id=1 and state<>1
insert into @t
select '1','1' union all
select '1','1' union all
select '2','1' union all
select '2','0' union all
select '2','1' union all
select '3','1' union all
select '3','0' union all
select '4','1' union all
select '5','0' union all
select '6','1' union all
select '6','1'select distinct id from @t where id not in (select distinct id from @t where state<>'1')
当存在 '6', null这样一行数据的时候,
6依然会被选出来。
(
select id from (select id,state from a group by id, state) as a
group by id having count(*)=1
)
and state=1
FROM table
WHERE (ID NOT IN
(SELECT ID
FROM table
WHERE (State = 0)))
insert into @t
select '1','1' union all
select '1','1' union all
select '2','1' union all
select '2','0' union all
select '2','1' union all
select '3','1' union all
select '3','0' union all
select '4','1' union all
select '5','0' union all
select '6','1' union all
select '6',nullselect distinct id from @t where id not in (select distinct id from @t where isnull(state,'0')<>'1')那稍微改一下不就得了。你之前又没说,我哪里知道你还有这种需求
select id from idAndState
where id=1 and state =1
except
select id from idAndState
where id=1 and (state<>1 or state is null)
你在后面 加条件 限制一下即可select distinct id from @t where id not in (select distinct id from @t where state<>'1' and state is not null )
不过用以下数据, sql结果是错的:id state
1 1
1 1
2 1
2 0
2 1
3 1
1 NULL
declare @t table( [ID] int ,state int)
insert into @t values(1 , 1 )
insert into @t values(1 , 1 )
insert into @t values(2 , 1 )
insert into @t values(2 , 0 )
insert into @t values(2 , 1 )
insert into @t values(3 , 1 )
insert into @t values(3 , 0 )
insert into @t values(4 , 1 )
insert into @t values(5 , 0 )
insert into @t values(6 , 1 )
insert into @t values(6 , 1 ) select [ID] from @t group by [ID] having Count( distinct state) = 1 and avg(state) =1
select distinct id from #temp where state=1 and id not in(select id from #temp where state=0)
insert into @t values(1 , 1 )
insert into @t values(1 , 1 ) insert into @t values(2 , 1 )
insert into @t values(2 , 0 )
insert into @t values(2 , 1 ) insert into @t values(3 , 1 )
insert into @t values(3 , 0 ) insert into @t values(4 , 1 ) insert into @t values(5 , 0 ) insert into @t values(6 , 1 )
insert into @t values(6 , 1 )
select distinct ID from @t as a
where not exists(select ID from @t where ID=a.ID and isnull(state,'0')=0 )
insert into @t
select '1','1' union all
select '1','1' union all
select '2','1' union all
select '2','0' union all
select '2','1' union all
select '3','1' union all
select '3','0' union all
select '4','1' union all
select '5','0' union all
select '6','1' union all
select '6','1'select distinct * from @t t where not exists(select 1 from @t where id=t.id and [state]=0)
WHERE (stat = 1)
AND (id NOT IN (SELECT id FROM idTable AS idTable_1 WHERE (stat = 0)))
{
select id from idAndState where state=1
} t1 where t1.id not in(
select id from
{
select id from idAndState where state=0
}
)
select id from AA where state !=1 AND state is not null)
应该没错了