declare @ta table(XH varchar(10),XM varchar(10)) insert @ta select '2001' , '王一' union all select '2002' , '李二' union all select '2003' , '蒋王'declare @tb table( XH varchar(10),JCDM int) insert @tb select '2001', 1 union all select '2002' , 2 union all select '2003' , NULL select a.XH , a.XM, case when b.JCDM=1 then '奖励' else '无' end as JL, case when b.JCDM=2 then '惩罚' else '无' end as CF from @ta a,@tb b where a.XH=b.XH--测试结果XH XM JL CF ---------- ---------- ---- ---- 2001 王一 奖励 无 2002 李二 无 惩罚 2003 蒋王 无 无(3 row(s) affected)
insert @ta select
'2001' , '王一' union all select
'2002' , '李二' union all select
'2003' , '蒋王'declare @tb table( XH varchar(10),JCDM int)
insert @tb select
'2001', 1 union all select
'2002' , 2 union all select
'2003' , NULL select a.XH , a.XM, case when b.JCDM=1 then '奖励' else '无' end as JL,
case when b.JCDM=2 then '惩罚' else '无' end as CF
from @ta a,@tb b
where a.XH=b.XH--测试结果XH XM JL CF
---------- ---------- ---- ----
2001 王一 奖励 无
2002 李二 无 惩罚
2003 蒋王 无 无(3 row(s) affected)