create table A
(
ID int identity primary key,
Num varchar(20),
Date datetime,
OperatorID int,
Status bit
)
create table B
(
ID int identity primary key,
OperatorName varchar(20)
)
怎么查找出A.ID,Num,Date,OperatorName,Status(--要显示中文)
--0为未发货 1为已发货
如果我用A.OperatorID=b.ID的话,只能查找出状态为1的数据
因为status=0时,operatorID是为空的
(
ID int identity primary key,
Num varchar(20),
Date datetime,
OperatorID int,
Status bit
)
create table B
(
ID int identity primary key,
OperatorName varchar(20)
)
怎么查找出A.ID,Num,Date,OperatorName,Status(--要显示中文)
--0为未发货 1为已发货
如果我用A.OperatorID=b.ID的话,只能查找出状态为1的数据
因为status=0时,operatorID是为空的
如果用select into 把状态为1的先插入一个临时表,在用insert into 把状态为0的插入到这表,不过有点烦,有没有简单的办法1?
select A.ID,a.Num,a.Date,a.OperatorName from a inner join (select id,OperatorName from b union all select 0,'未发货') as tb on a.id=tb.id
case when status =0 then '未发货' else '已发货' end as Status
from A
left join B on B.ID = A.OperatorID
条件单拿出来,使用case when 处理
from A
left join B on A.OperatorID=b.ID