有数据表
a1 a2 a3 a4
A a 1
A a 2
A a 3
B b 4
B b 5 x
C c 6 x
C c 7
D d 8
D d 9
a1,a2,a3是主键,如果 a4为x的,那么a1,a2相同的项也要取消,操作后为
a1 a2 a3 a4
A a 1
A a 2
A a 3
D d 8
D d 9
问sql语句如何实现,急,谢谢高手指点!!!
a1 a2 a3 a4
A a 1
A a 2
A a 3
B b 4
B b 5 x
C c 6 x
C c 7
D d 8
D d 9
a1,a2,a3是主键,如果 a4为x的,那么a1,a2相同的项也要取消,操作后为
a1 a2 a3 a4
A a 1
A a 2
A a 3
D d 8
D d 9
问sql语句如何实现,急,谢谢高手指点!!!
declare @t table
(
a1 char(1),
a2 char(1),
a3 char(1),
a4 char(1)
)insert @t select 'A','a','1',''
union all select 'A','a','2',''
union all select 'A','a','3',''
union all select 'B','b','4',''
union all select 'B','b','5','x'
union all select 'C','c','6','x'
union all select 'C','c','7',''
union all select 'D','d','8',''
union all select 'D','d','9',''
delete a
from @t a
where a.a1 = a.a2
and exists(
select
*
from @t
where a1 = a.a1
and a2 = a.a2
and a4 = 'x')select * from @t/**
A a 1
A a 2
A a 3
D d 8
D d 9
**/
union all select 'A','a1','2',''
union all select 'A','a1','3',''
union all select 'B','b2','4',''
union all select 'B','b2','5','x'
union all select 'C','c2','6','x'
union all select 'C','c3','7',''
union all select 'D','d4','8',''
union all select 'D','d4','9',''
就不成立了
(
a1 char(1),
a2 char(1),
a3 char(1),
a4 char(1)
)insert @t select 'A','a','1',''
union all select 'A','a','2',''
union all select 'A','a','3',''
union all select 'B','b','4',''
union all select 'B','b','5','x'
union all select 'C','c','6','x'
union all select 'C','c','7',''
union all select 'D','d','8',''
union all select 'D','d','9',''
delete a
from @t a
where exists(
select
*
from @t
where a1 = a.a1
and a2 = a.a2
and a4 = 'x')select * from @t--结果
a1 a2 a3 a4
---- ---- ---- ----
A a 1
A a 2
A a 3
D d 8
D d 9 (5 行受影响)
where exists (select 1 from 数据表 where a1=a.a1 and a1=a.a2 and a4='x')
delete a from t1 as a where exists(select * from t1 where a4='X' and a1=a.a1 and a2=a.a2)