select name, a.dept as Dept_A ,b.dept as Dept_B, age ,sex ,
       case when a.dept=b.dept then Y' else 'N' end as Remark 
from a join b on a.name=b.name

------解决方案--------------------

SQL code
select A.name,A.Dept as Dept_A,B.Dept as Dept_B,A.age,B.Sex,(case when A.Dept=B.Dept then 'Y' else 'N' end) as Remark from A,B where A.name=B.name

------解决方案--------------------
SQL code
select a.name,a.dept as dept_A,b.dept as dept_B,a.age,b.sex,(case when a.dept=b.dept then 'Y' else 'N' end) as Remark
from tableA a inner join tableB b on a.name=b.name

------解决方案--------------------
SQL code
select
    name,
    Dept_A=a.dept,
    Dept_B=b.dept,
    age,
    sex,
    Remark =case when a.dept=b.dept then 'Y' else 'N' end
from a 
    join b 
      on a.name=b.name

------解决方案--------------------

SQL code
declare @A table(name varchar(10),Dept varchar(10),age int)
insert into @A select 'mary ','IT',23 
insert into @A select 'henry','fs',25 
insert into @A select 'joyce','pr',28 

declare @B table(name varchar(10),Dept varchar(10),sex char(1))
insert into @B select 'mary ','HR','F' 
insert into @B select 'henry','fs','m' 
insert into @B select 'joyce','ps','F' 


select 
    A.name,A.Dept as Dept_A,B.Dept as Dept_B,A.age,B.Sex,
    (case when A.Dept=B.Dept then 'Y' else 'N' end) as Remark 
from 
    @A A,@B B 
where 
    A.name=B.name

/*
name       Dept_A     Dept_B     age         Sex  Remark 
---------- ---------- ---------- ----------- ---- ------ 
mary       IT         HR         23          F    N
henry      fs         fs         25          m    Y
joyce      pr         ps         28          F    N
*/

------解决方案--------------------
SQL code
select name, =a.dept   , Dept_B=b.dept , age ,sex ,
       case when a.dept=b.dept then Y' else 'N' end as Remark 
from tb1 a join tb2 b on a.name=b.name

------解决方案--------------------
SQL code
if object_id('a') is not null
  drop table a
go
create table a(name varchar(10), Dept varchar(10), age int)
---------------- 
insert a select 'mary','IT',23
insert a select 'henry','fs',25
insert a select 'joyce','pr',28
go

if object_id('b') is not null
  drop table b
go
create table b(name varchar(10), Dept varchar(10), Sex varchar(10))
insert b select 'mary','HR','F'
insert b select 'henry','fs','m'
insert b select 'joyce','ps','F'
go

select
    a.name,
    Dept_A=a.dept,
    Dept_B=b.dept,
    age,
    sex,
    Remark =case when a.dept=b.dept then 'Y' else 'N' end
from a 
    join b 
      on a.name=b.name

/*
name       Dept_A     Dept_B     age         sex        Remark
---------- ---------- ---------- ----------- ---------- ------
mary       IT         HR         23          F          N
henry      fs         fs         25          m          Y
joyce      pr         ps         28          F          N

(3 行受影响)
*/