表A:
a b
--------
101 23
101 24
101 25
102 26
102 27
103 28
103 29
103 30表B
a b
-----
23 A
24 B
26 C
27 D如果表A中列b的值出现在表B中,则列出A.a,否则显示空值,并且,表A的a列不重复显示.
我的语句是:
select A.a,B.a from A left join B on A.b=B.a
只做到了如下结果:a b
---------
101 23
101 24
101 NULL
102 26
102 27
103 NULL
103 NULL
103 NULL
我要的结果是:
a b
---------
101 23
102 26
103 NULL
a b
--------
101 23
101 24
101 25
102 26
102 27
103 28
103 29
103 30表B
a b
-----
23 A
24 B
26 C
27 D如果表A中列b的值出现在表B中,则列出A.a,否则显示空值,并且,表A的a列不重复显示.
我的语句是:
select A.a,B.a from A left join B on A.b=B.a
只做到了如下结果:a b
---------
101 23
101 24
101 NULL
102 26
102 27
103 NULL
103 NULL
103 NULL
我要的结果是:
a b
---------
101 23
102 26
103 NULL
select A.a,b=case when exists(select 1 from B where a=A.b) then b end
from
(
select a,b=min(b)
from A
group by a
)A
insert A select 101, 23
union all select 101, 24
union all select 101, 25
union all select 102, 26
union all select 102, 27
union all select 103, 28
union all select 103, 29
union all select 103, 30create table B(a int,b nvarchar(10))
insert B select 23, 'A'
union all select 24, 'B'
union all select 26, 'C'
union all select 27, 'D'
select A.a,b=case when exists(select 1 from B where a=A.b) then b end
from
(
select a,b=min(b)
from A
group by a
)A
--result
a b
----------- -----------
101 23
102 26
103 NULL(3 row(s) affected)