select isnull(a.CarId,'暂无') as CarId, b.Name, b.UseContent, isnull(caeName,'暂无') as caeName from CarApplyInfo b left join CarApplyInfo a on a.CarId=b.CarId
create table CarApplyInfo(Id int identity(1,1) , CarId int , Name varchar(10) , UseContent varchar(100) ) insert into CarApplyInfo values(NULL,'小王','外出接待') insert into CarApplyInfo values('1','小明','出外开会') insert into CarApplyInfo values('2','小张','出外喝酒') create table CarInfo( CarId int identity(1,1) , CarName varchar(50) )insert into CarInfo values('本田C01') insert into CarInfo values('现代K007') insert into CarInfo values('通用K003')select isnull(cast(a.CarId as varchar(20)),'暂无') as CarId ,Name ,UseContent ,isnull(CarName,'暂无') as CarName from CarApplyInfo a left join CarInfo b on a.CarId=b.CarIdCarId Name UseContent CarName -------------------- ---------- --------------- ----------------- 暂无 小王 外出接待 暂无 1 小明 出外开会 本田C01 2 小张 出外喝酒 现代K007(3 行受影响)
select id ,isnull(cast(a.CarId as varchar(20)),'暂无') as CarId ,Name ,UseContent ,isnull(CarName,'暂无') as CarName from CarApplyInfo a left join CarInfo b on a.CarId=b.CarIdid CarId Name UseContent CarName ----------- -------------------- ---------- ------------------ ----------------- 1 暂无 小王 外出接待 暂无 2 1 小明 出外开会 本田C01 3 2 小张 出外喝酒 现代K007(3 行受影响)刚才的丢了个ID
我用的是sql 2000的,``但是为什么查出来后还是第一种结果```
需要 left join 否则不满足条件a.CarId=b.CarId的数据就过滤掉了。
直接左连接就可以了呀。select isnull(a.CarId,'暂无') as CarId, b.Name, b.UseContent, isnull(caeName,'暂无') as caeName from CarApplyInfo b left join CarApplyInfo a on a.CarId=b.CarId代码是树人大哥的。
isnull(a.CarId,'暂无') as CarId,
b.Name,
b.UseContent,
isnull(caeName,'暂无') as caeName
from
CarApplyInfo b
left join
CarApplyInfo a on a.CarId=b.CarId
CarId int ,
Name varchar(10) ,
UseContent varchar(100) )
insert into CarApplyInfo values(NULL,'小王','外出接待')
insert into CarApplyInfo values('1','小明','出外开会')
insert into CarApplyInfo values('2','小张','出外喝酒')
create table CarInfo(
CarId int identity(1,1) ,
CarName varchar(50) )insert into CarInfo values('本田C01')
insert into CarInfo values('现代K007')
insert into CarInfo values('通用K003')select isnull(cast(a.CarId as varchar(20)),'暂无') as CarId
,Name
,UseContent
,isnull(CarName,'暂无') as CarName
from CarApplyInfo a
left join CarInfo b
on a.CarId=b.CarIdCarId Name UseContent CarName
-------------------- ---------- --------------- -----------------
暂无 小王 外出接待 暂无
1 小明 出外开会 本田C01
2 小张 出外喝酒 现代K007(3 行受影响)
,isnull(cast(a.CarId as varchar(20)),'暂无') as CarId
,Name
,UseContent
,isnull(CarName,'暂无') as CarName
from CarApplyInfo a
left join CarInfo b
on a.CarId=b.CarIdid CarId Name UseContent CarName
----------- -------------------- ---------- ------------------ -----------------
1 暂无 小王 外出接待 暂无
2 1 小明 出外开会 本田C01
3 2 小张 出外喝酒 现代K007(3 行受影响)刚才的丢了个ID
isnull(a.CarId,'暂无') as CarId,
b.Name,
b.UseContent,
isnull(caeName,'暂无') as caeName
from
CarApplyInfo b
left join
CarApplyInfo a on a.CarId=b.CarId代码是树人大哥的。