1.select distinct id from tableA where name='li' and state=0
2.select distinct id from tableA where name='li' and state=100
3.select distinct id from tableA where name='li' and (state=200 or state=0)??
4.select distinct id from tableA where name='li' and (state=300 or state=350 or state!=100)??
2.select distinct id from tableA where name='li' and state=100
3.select distinct id from tableA where name='li' and (state=200 or state=0)??
4.select distinct id from tableA where name='li' and (state=300 or state=350 or state!=100)??
2.select distinct id from tableA where name = 'li' and State = 100
3.select distinct id from tableA A where name = 'li' and State = 200 and not exists (select * from tableA where id = A.id and State <> 0)
4.select distinct id from tableA A where name = 'li' and (State = 300 or State = 350) and not exists (select * from tableA where id = A.id and State = 100)
2.seletc * from tableA where ID in (select distinct id from tableA where name='li' and state=100)
3.seletc * from tableA where ID in (select distinct id from tableA where name='li' and (state=200 or state=0))
4.seletc * from tableA where ID in (select distinct id from tableA where name='li' and (state=300 or state=350 or state!=100))
2.select distinct id from tableA where name = 'li' and State = 100
3.select distinct id from tableA A where name = 'li' and State = 200 and not exists (select * from tableA where id = A.id and State <> 0)
4.select distinct id from tableA A where name = 'li' and (State = 300 or State = 350) and not exists (select * from tableA where id = A.id and State = 100)
2.select distinct id from tableA where name='li' and state=100
3.select distinct id from tableA A where name = 'li' and State = 200 and not exists (select * from tableA where id = A.id and State <> 0)
4.select distinct id from tableA A where name = 'li' and (State = 300 or State = 350) and not exists (select * from tableA where id = A.id and State = 100)
对不起,问题的表达方式存在问题。更改如下
查询需求
1、用一条语句查询出:name为'li',且【同样的ID】对应的State字段值全为0 的 ID(要求结果中ID不重复)
2、用一条语句查询出:name为'li',且【同样的ID】对应的State字段值存在100的ID(要求结果集合中ID不重复)
3、用一条语句查询出:name为'li',且【同样的ID】对应的State字段值存在200而其余全部为0的ID(要求结果集合ID不重复)
4、用一条语句查询出:name为'li',且【同样的ID】对应的State字段存值在300或350而不存在100的ID(要求结果集合ID不重复)非常抱歉:
请仔细理解一下我的意思,比如我的给出的表中
id
10 符合查询1,而13不符合查询一,因为13对应的State字段不全为0
insert ta1 values(10,0,'zhang')
insert ta1 values(10,0,'li')
insert ta1 values(10,100,'zhang')
insert ta1 values(10,0,'li')
insert ta1 values(13 ,0,'li')
insert ta1 values(13,100,'li')
1.select distinct id from ta1 A where name='li' and State = 0 and not exists (select * from ta1 where name = A.name and id=A.id and State <> 0)
2.select distinct id from ta1 A where name='li' and exists (select * from ta1 where name = A.name and id=A.id and State =100)
3.select distinct id from ta1 A where name='li' and exists (select * from ta1 where name = A.name and id=A.id and State =200) and not exists (select * from ta1 where name=a.name and id=a.id and (state=200 or state=0))
4.select distinct id from ta1 A where name='li' and exists (select * from ta1 where name = A.name and id=A.id and (State =300 or state=350)) and not exists (select * from ta1 where name=a.name and id=a.id and state=100)
where a.name='li'
and not exists
(select * from tableA b where b.id=a.id and b.status<>0)select distinct id
from tableA a
where a.name='li'
and exists(select id from tableA b where b.id=a.id and b.status=100)select distinct id
from tableA a
where a.name='li'
and exists(select * from tableA b where b.id=a.id and b.status=200)
and not exists
(select * from tableA c where c.id=a.id and status not in(0,200))
select distinct id
from tableA a
where a.name='li'
and exists(select * from tableA b where b.id=a.id and b.status in(350,300))
and not exists(select * from tableA c where c.id=a.id and c.status=100)
insert @ta1 values(10,0,'zhang')
insert @ta1 values(10,0,'li')
insert @ta1 values(10,100,'zhang')
insert @ta1 values(10,0,'li')
insert @ta1 values(13 ,0,'li')
insert @ta1 values(13,100,'li')
select * from @ta1select distinct id
from @ta1 as t1
where t1.name = 'li'
and state = 0
and t1.id not in (select t2.id from @ta1 t2 where t2.id = t1.id and t1.name = t2.name and t2.state <> 0)select distinct id
from @ta1 as t1
where t1.name = 'li'
and t1.id in (select t2.id from @ta1 t2 where t2.id = t1.id and t1.name = t2.name and t2.state = 100)select distinct id
from @ta1 as t1
where t1.name = 'li'
and t1.id in (select t2.id from @ta1 t2 where t2.id = t1.id and t1.name = t2.name and t2.state = 200)
and
t1.ID in (select t2.id from @ta1 t2 where t2.id = t1.id and t1.name = t2.name and t2.state = 0)select distinct id
from @ta1 as t1
where t1.name = 'li'
and t1.id in (select t2.id from @ta1 t2 where t2.id = t1.id and t1.name = t2.name and ((t2.state = 300) or (t2.State = 350)))
and
t1.ID not in (select t2.id from @ta1 t2 where t2.id = t1.id and t1.name = t2.name and t2.state = 100)
Select distinct [ID] from tableA a
where [name] = 'li' and not exists(select * from tableA where [id] = a.[id] and state <> 0)
2:
Select distinct [ID] from tableA a
where [name] = 'li' and exists(select * from tableA where [id] = a.[id] and state 100)
3:
Select distinct [ID] from tableA a
where [name] = 'li'
and exists(select * from tableA where [id] = a.[id] and state = 200)
and not exists(select * from tableA where [id] = a.[id] and (state not in (200,0))
4:
Select distinct [ID] from tableA a
where [name] = 'li'
and exists(select * from tableA where [id] = a.[id] and state in (300,350))
and not exists(select * from tableA where [id] = a.[id] and state = 100)
insert #a values(10,0,'zhang')
insert #a values(10,0,'li')
insert #a values(10,100,'zhang')
insert #a values(10,0,'li')
insert #a values(13 ,0,'li')
insert #a values(13,100,'li')--查询需求
-- 1、用一条语句查询出:name为'li',且【同样的ID】对应的State字段值全为0 的 ID(要求结果中ID不重复)select distinct [id] from #a where [name]='li' and [id] not in (select [id] from #a where [name]='li' and state<>0)-- 2、用一条语句查询出:name为'li',且【同样的ID】对应的State字段值存在100的ID(要求结果集合中ID不重复)select distinct [id] from #a where [name]='li' and [id] in (select [id] from #a where [name]='li' and state=100)-- 3、用一条语句查询出:name为'li',且【同样的ID】对应的State字段值存在200而其余全部为0的ID(要求结果集合ID不重复)select distinct [id] from #a where [name]='li' and state=200 and [id] not in (select [id] from #a where [name]='li' and state not in (200,0))-- 4、用一条语句查询出:name为'li',且【同样的ID】对应的State字段存值在300或350而不存在100的ID(要求结果集合ID不重复)select distinct [id] from #a where [name]='li' and state in (300,350) and [id] not in (select [id] from #a where [name]='li' and state=100)
go
drop table #a
select distinct id from tableA where name='li' and state like 100
select distinct id from tableA where name='li' and (state like 200 or state=0)这个怎么解决等待!
select distinct id from tableA where name='li' and (state like 300 or state like 350 and state not like 100)
select distinct id from tableA where name='li' and state=0
select distinct id from tableA where name='li' and state like ‘%100%’
select distinct id from tableA where name='li' and (state like‘% 200 %'or state=0)这个怎么解决等待!
select distinct id from tableA where name='li' and (state like ‘%300%' or state like ‘%350%' and state not like ‘%100%')