--生成测试数据
create table #a(cardno int,isdown int,available int,id int)
insert into #a select 1000,0,0,1
insert into #a select 1000,1,0,2
insert into #a select 1000,1,1,3
insert into #a select 1000,0,1,4
insert into #a select 1111,1,1,5
insert into #a select 2222,0,0,6
insert into #a select 2222,0,0,7
insert into #a select 3333,0,0,8
insert into #a select 3333,0,1,9
--执行查询
select
t.cardno,t.id
from
#a t
where
(t.isdown = 1
and
t.available = 1
and
exists(select 1 from #a where cardno=a.cardno and isdown=1 and available=1))
or
(not exists(select 1 from #a where cardno=a.cardno and isdown=1 and available=1))
and
not exists(select 1 from #a where cardno=a.cardno and id < a.id))
--输出结果
/*
cardno id
------ -----
1000 3
1111 5
2222 6
3333 8
*/
create table #a(cardno int,isdown int,available int,id int)
insert into #a select 1000,0,0,1
insert into #a select 1000,1,0,2
insert into #a select 1000,1,1,3
insert into #a select 1000,0,1,4
insert into #a select 1111,1,1,5
insert into #a select 2222,0,0,6
insert into #a select 2222,0,0,7
insert into #a select 3333,0,0,8
insert into #a select 3333,0,1,9
--执行查询
select
t.cardno,t.id
from
#a t
where
(t.isdown = 1
and
t.available = 1
and
exists(select 1 from #a where cardno=a.cardno and isdown=1 and available=1))
or
(not exists(select 1 from #a where cardno=a.cardno and isdown=1 and available=1))
and
not exists(select 1 from #a where cardno=a.cardno and id < a.id))
--输出结果
/*
cardno id
------ -----
1000 3
1111 5
2222 6
3333 8
*/
解决方案 »
- navicat8 建表
- Report Service 部署后的验证框
- 数据库远程注册的问题
- 在a数据库的存储过程插入一条数据到b数据库...
- 请教SQL查询效率优化问题?
- 救命啊!游标有没有象编程里continue那样跳过一句,让循环继续执行啊
- where identitycol=@@identity 这条语句什么意思?
- 在ORACLE里面的语法和SQLSERVER不同,如何在SQLSERVER里面实现??
- 程序员考试求助,鸡毛信!!!!
- 一个难对应的考勤明细表的存储过程->这个以前我一直没有想到一个排班的问题(我出的的大错误!)罚自已再出500分
- 请帮忙优化这个多条件查询
- 100分求一个复杂的sql查询(100%结贴)
insert into #a select 1000,0,0,1
insert into #a select 1000,1,0,2
insert into #a select 1000,1,1,3
insert into #a select 1000,0,1,4
insert into #a select 1111,1,1,5
insert into #a select 2222,0,0,6
insert into #a select 2222,0,0,7
insert into #a select 3333,0,0,8
insert into #a select 3333,0,1,9--语句
select cardno,id
from #a
where isdown=1 and available=1
union
select cardno,min(id)
from #a
where (isdown<>1 or available<>1)
and cardno not in(select cardno from #a where isdown=1 and available=1)
group by cardno
order by cardnodrop #a
/*结果
1000 3
1111 5
2222 6
3333 8*/
只显示Cardno在B表中有的记录呢
insert into #a select 1000,0,0,1
insert into #a select 1000,1,0,2
insert into #a select 1000,1,1,3
insert into #a select 1000,0,1,4
insert into #a select 1111,1,1,5
insert into #a select 2222,0,0,6
insert into #a select 2222,0,0,7
insert into #a select 3333,0,0,8
insert into #a select 3333,0,1,9create table b(cardno int,id int)
insert into b select 1000,1
insert into b select 1111,5
insert into b select 2222,6
insert into b select 3333,8
--语句
select a.cardno,a.id
from a ,b
where isdown=1 and available=1 and a.cardno=b.cardno
union
select a.cardno ,min(a.id)
from a,b
where (isdown<>1 or available<>1)
and a.cardno not in(select a.cardno from a,b where isdown=1 and available=1 and a.cardno=b.cardno)
group by a.cardno
order by a.cardno
结果:1000 3
1111 5
2222 6
3333 8