＋设为首页 ＋添加到收藏夹
a表
cardno isdown available id
1000 0 0 1
1000 1 0 2
1000 1 1 3
1000 0 1 4
1111 1 1 5
2222 0 0 6
2222 0 0 7
3333 0 0 8
3333 0 1 9
b表
cardno xx id
1000 xx 1
1111 xx 2
2222 xx 3
结果
cardno id
1000 3
1111 5
2222 6
3333 8
A表中 cardno 可能重复
结果为A表中所有的记录
条件:
cardno相同的只取一条,取哪一条根据下面的规则
如果A表中 isdown =1 available =1 那么得到a表中的ID(如cardno =1000 则得到ID=3 1111-->5,)
否则得到a表中ID的最小值 (如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
*/
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
create 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
