select a.id,c.[名称] as [人员],'' as [部门],a.[物品] from a inner join c on a.[人员]=c.id where a.[部门]=0 union select a.id,'' as [人员],b.[名称] as [部门],a.[物品] from a inner join b on a.[部门]=b.id where a.[人员]=0 union select a.id,c.[名称] as [人员],b.[名称] as [部门],a.[物品] from a inner join b on a.[部门]=b.id inner join c on a.[人员]=c.id where a.[部门]<>0 and a.[人员]<>0
select a.id,c.[名称] as [人员],'' as [部门],a.[物品] from a inner join c on a.[人员]=c.id where a.[部门]=0 union select a.id,'' as [人员],b.[名称] as [部门],a.[物品] from a inner join b on a.[部门]=b.id where a.[人员]=0 union select a.id,c.[名称] as [人员],b.[名称] as [部门],a.[物品] from a inner join b on a.[部门]=b.id inner join c on a.[人员]=c.id where a.[部门]<>0 and a.[人员]
SQL 没有错,表不对:1 在表 c 中增加一条记录,ID=0, 名称=[空] 2 把表 a 中的第二条记录的部门值改为 2 结果: ID 人员 部门 物品 1 小明 教导处 铅笔 2 初一办公室 桌子
select a.id,c.[名称] as [人员],'' as [部门],a.[物品] from a inner join c on a.[人员]=c.id where a.[部门]=0
union
select a.id,'' as [人员],b.[名称] as [部门],a.[物品] from a inner join b on a.[部门]=b.id where a.[人员]=0
union
select a.id,c.[名称] as [人员],b.[名称] as [部门],a.[物品] from a inner join b on a.[部门]=b.id inner join c on a.[人员]=c.id where a.[部门]<>0 and a.[人员]<>0
select a.id,c.[名称] as [人员],'' as [部门],a.[物品] from a inner join c on a.[人员]=c.id where a.[部门]=0
union
select a.id,'' as [人员],b.[名称] as [部门],a.[物品] from a inner join b on a.[部门]=b.id where a.[人员]=0
union
select a.id,c.[名称] as [人员],b.[名称] as [部门],a.[物品] from a inner join b on a.[部门]=b.id inner join c on a.[人员]=c.id where a.[部门]<>0 and a.[人员]
2 把表 a 中的第二条记录的部门值改为 2
结果:
ID 人员 部门 物品
1 小明 教导处 铅笔
2 初一办公室 桌子