select a.*,
(if(exists(select * from b where b.bid=a.bid)) select 'Y ' else select 'N ') isInB
from a
---------------------
用case when 来代替 if
(if(exists(select * from b where b.bid=a.bid)) select 'Y ' else select 'N ') isInB
from a
---------------------
用case when 来代替 if
from A t1 left join B t2
on t1. Bid=t2. Bid
create table a
(Bid int, name varchar(10), age int, sex varchar(10) )
insert into a
select 1 , 'sss', 18 , 'Y' union all
select 2 , 'FFF', 19 , 'N' union all
select 3 , 'EEE', 32 , 'Y' union all
select 4 , 'EW' , 23 , 'Y' union all
select 5 , 'FEFE', 32 , 'N' create table b
(cid int, Bid int )
insert into b
select 1, 4 union all
select 2, 5 select t1.*,IsInB =case when t1. Bid=t2. Bid then 'Y ' else 'N ' end
from A t1 left join B t2
on t1. Bid=t2. Bid Bid name age sex IsInB
----------- ---------- ----------- ---------- -----
1 sss 18 Y N
2 FFF 19 N N
3 EEE 32 Y N
4 EW 23 Y Y
5 FEFE 32 N Y (所影响的行数为 5 行)
insert into A values(1, 'sss' , 18, 'Y')
insert into A values(2, 'FFF' , 19, 'N')
insert into A values(3, 'EEE' , 32, 'Y')
insert into A values(4, 'EW' , 23, 'Y')
insert into A values(5, 'FEFE', 32, 'N')
create table B(cid int,Bid int)
insert into B values(1, 4)
insert into B values(2, 5)
goselect A.* , IsInB =
case when a.bid = b.bid then 'Y' else 'N' end
from A left join B
on A.bid = B.biddrop table A,B/*
Bid name age sex IsInB
----------- ---------- ----------- ---- -----
1 sss 18 Y N
2 FFF 19 N N
3 EEE 32 Y N
4 EW 23 Y Y
5 FEFE 32 N Y(所影响的行数为 5 行)
*/
create table A(Bid int,name varchar(10),age int,sex varchar(2))
insert into A values(1, 'sss' , 18, 'Y')
insert into A values(2, 'FFF' , 19, 'N')
insert into A values(3, 'EEE' , 32, 'Y')
insert into A values(4, 'EW' , 23, 'Y')
insert into A values(5, 'FEFE', 32, 'N')
create table B(cid int,Bid int)
insert into B values(1, 4)
insert into B values(2, 5)
goselect A.* , IsInB =
case when a.bid in (select bid from B) then 'Y' else 'N' end
from A drop table A,B/*
Bid name age sex IsInB
----------- ---------- ----------- ---- -----
1 sss 18 Y N
2 FFF 19 N N
3 EEE 32 Y N
4 EW 23 Y Y
5 FEFE 32 N Y(所影响的行数为 5 行)
*/