select a.*,b.Department
from
(
select a.Money,
(case when a.POSLogNo is null then a.Operator else (select POSOperator from POSLog where LogNo=a.POSLogNo ) end ) as Operator
from WorkLog a
)a ,
Operator b
where a.Operator=b.Name
from
(
select a.Money,
(case when a.POSLogNo is null then a.Operator else (select POSOperator from POSLog where LogNo=a.POSLogNo ) end ) as Operator
from WorkLog a
)a ,
Operator b
where a.Operator=b.Name
insert @WorkLog select 1 , 322 , '王庆' union all select
null , 412 , '刘新' union all select
null , 143 , '王然' union all select
3 , 23 , '王庆' union all select
2 , 851, '刘新'declare @POSLog table (LogNO int,POSOperator nvarchar(20))
insert into @POSLog select
1,'操作员01' union all select
2,'操作员02' union all
select 3,'操作员01' declare @Operator table (Department int ,name nvarchar(20))
insert into @Operator select
2 , '王庆' union all select
1, '操作员01' union all select
3 , '刘新' union all select
4 , '操作员02' union all select
1 , '王然'
select a.*,b.Department
from
(
select a.Money,
(case when a.POSLogNo is null then a.Operator else (select POSOperator from @POSLog where LogNo=a.POSLogNo ) end ) as Operator
from @WorkLog a
)a ,
@Operator b
where a.Operator=b.Name--测试结果
Money Operator Department
----------- -------------------- -----------
322 操作员01 1
412 刘新 3
143 王然 1
23 操作员01 1
851 操作员02 4(5 row(s) affected)