总共有两个表,A与B表,A表记录每个人拥有的类型,B表中类型基础表,求一SQL迅速查出A表中当前这个人没有的类型。A表,每个人拥有的类型字段:
a,
b.纪录
-------------------------------------------
a b
-------------------------------------------
张三 A01
张三 A02
张三 A05
李四 A01
李四 A03
李四 A05
-------------------------------------------B表,类型基础表
字段:
b_type纪录
-------------------------------------------
b_type
-------------------------------------------
A01
A02
A03
A04
A05
-------------------------------------------
求SQL能实现如下结果。 纪录
-------------------------------------------
a b
-------------------------------------------
张三 A03
张三 A04
李四 A02
李四 A04
-------------------------------------------
a,
b.纪录
-------------------------------------------
a b
-------------------------------------------
张三 A01
张三 A02
张三 A05
李四 A01
李四 A03
李四 A05
-------------------------------------------B表,类型基础表
字段:
b_type纪录
-------------------------------------------
b_type
-------------------------------------------
A01
A02
A03
A04
A05
-------------------------------------------
求SQL能实现如下结果。 纪录
-------------------------------------------
a b
-------------------------------------------
张三 A03
张三 A04
李四 A02
李四 A04
-------------------------------------------
select a1.a, b1.b_type
from a1 full join b b1 on 1=1
minus
select a2.a, a2.b as b_type
from a a2;
除了一楼大哥的select a1.a, b1.b_type from a1 full join b b1 on 1=1 minus select a2.a, a2.b as b_type from a a2还有别的吗?
MINUS SELECT ta.a,tb.b_type FROM ta,tb
WHERE ta.b=tb.b_type;
SELECT DISTINCT a.a,b.b_type FROM a,b
MINUS
SELECT a.a,a.b FROM a
我来个复杂的 ,嘿嘿 with t as (
select '张三' as a,'A01' as b from dual
union all
select '张三' as a,'A02' as b from dual
union all
select '张三' as a,'A05' as b from dual
union all
select '李四' as a,'A01' as b from dual
union all
select '李四' as a,'A03' as b from dual
union all
select '李四' as a,'A05' as b from dual
),
s as (
select 'A01' as b_type from dual
union all
select 'A02' as b_type from dual
union all
select 'A03' as b_type from dual
union all
select 'A04' as b_type from dual
union all
select 'A05' as b_type from dual
)
select * from (
select distinct t.a,case when s.b_type in (select t1.b from t t1 where t1.a=t.a) then null els
s.b_type end q from t,s
) re
where re.q is not null
order by re.a,re.q
minus
select a.a_column,b.c_column from t_tabla a,t_tablb b
where a.b_column = b.c_column
代码献上,祝您好运!