表A
create table a(
aid int identity(1,1) primary key,
a1 varchar(20) not null,
a2 varchar(20) not null,
a_status int default(0)-----状态值只能是1,2,3
)
insert into a(a1,a2,a_status) values('001','风',1);
insert into a(a1,a2,a_status) values('002','风',1);--
insert into a(a1,a2,a_status) values('003','风',1);--
insert into a(a1,a2,a_status) values('004','风',1);--
insert into a(a1,a2,a_status) values('001','雨',1);--
insert into a(a1,a2,a_status) values('002','雨',1);
insert into a(a1,a2,a_status) values('003','雨',1);--
insert into a(a1,a2,a_status) values('004','雨',1);--
insert into a(a1,a2,a_status) values('001','雷',1);--
insert into a(a1,a2,a_status) values('002','雷',1);--
insert into a(a1,a2,a_status) values('003','雷',1);
insert into a(a1,a2,a_status) values('004','雷',1);--
insert into a(a1,a2,a_status) values('001','风',2);
insert into a(a1,a2,a_status) values('002','雨',2);
insert into a(a1,a2,a_status) values('003','雷',2);
---------------------------查询出状态为1但不为2的所有记录(即有'--'标记的记录) ---------------------------------
create table a(
aid int identity(1,1) primary key,
a1 varchar(20) not null,
a2 varchar(20) not null,
a_status int default(0)-----状态值只能是1,2,3
)
insert into a(a1,a2,a_status) values('001','风',1);
insert into a(a1,a2,a_status) values('002','风',1);--
insert into a(a1,a2,a_status) values('003','风',1);--
insert into a(a1,a2,a_status) values('004','风',1);--
insert into a(a1,a2,a_status) values('001','雨',1);--
insert into a(a1,a2,a_status) values('002','雨',1);
insert into a(a1,a2,a_status) values('003','雨',1);--
insert into a(a1,a2,a_status) values('004','雨',1);--
insert into a(a1,a2,a_status) values('001','雷',1);--
insert into a(a1,a2,a_status) values('002','雷',1);--
insert into a(a1,a2,a_status) values('003','雷',1);
insert into a(a1,a2,a_status) values('004','雷',1);--
insert into a(a1,a2,a_status) values('001','风',2);
insert into a(a1,a2,a_status) values('002','雨',2);
insert into a(a1,a2,a_status) values('003','雷',2);
---------------------------查询出状态为1但不为2的所有记录(即有'--'标记的记录) ---------------------------------
where not exists(select 1 from a b where a.a1=b.a1 and a.a2=b.a2 and b.a_status=2)
select *
from a t
where a_status=1
and not exists(select 1 from a where a1=t.a1 and a2=t.a2 and a_status=2)/**
aid a1 a2 a_status
----------- -------------------- -------------------- -----------
2 002 风 1
3 003 风 1
4 004 风 1
5 001 雨 1
7 003 雨 1
8 004 雨 1
9 001 雷 1
10 002 雷 1
12 004 雷 1(所影响的行数为 9 行)
**/
where a_status = 1
and aid not exists(select distinct aid from a where a_status = 2)
select * From a where a_status = 1 and not exists (select 1 from
a b where a.a1=b.a1 and a.a2=b.a2 and b.a_status=2)