今天遇到的一个大坑，话不多少，看sql和下边的查询结果：
1 --问题：恰好把buildingid is null的记录给过滤掉 2 create table tommyduan_gridcell_group_all_test(gridid nvarchar(32),buildingid nvarchar(32),floor nvarchar(32)); 3 insert into tommyduan_gridcell_group_all_test(gridid,buildingid,floor)values('g1',null,1); 4 insert into tommyduan_gridcell_group_all_test(gridid,buildingid,floor)values('g2','b1',1); 5 insert into tommyduan_gridcell_group_all_test(gridid,buildingid,floor)values('g2','b2',1); 6 insert into tommyduan_gridcell_group_all_test(gridid,buildingid,floor)values('g3','b2',1); 7 insert into tommyduan_gridcell_group_all_test(gridid,buildingid,floor)values('g3','b2',2); 8 insert into tommyduan_gridcell_group_all_test(gridid,buildingid,floor)values('g3','b2',4); 9 10 11 create table tommyduan_fingerlib(gridid nvarchar(32),buildingid nvarchar(32),floor nvarchar(32)); 12 insert into tommyduan_fingerlib(gridid,buildingid,floor)values('g1',null,1); 13 insert into tommyduan_fingerlib(gridid,buildingid,floor)values('g2','b1',1); 14 insert into tommyduan_fingerlib(gridid,buildingid,floor)values('g2','b2',1); 15 insert into tommyduan_fingerlib(gridid,buildingid,floor)values('g3','b2',1); 16 insert into tommyduan_fingerlib(gridid,buildingid,floor)values('g3','b2',2); 17 insert into tommyduan_fingerlib(gridid,buildingid,floor)values('g3','b2',4); 18 19 20 select * from tommyduan_gridcell_group_all_test t10 21 inner join tommyduan_fingerlib t11 22 on t10.gridid=t11.gridid and t10.buildingid=t11.buildingid and t10.floor=t11.floor
查询结果：
解决方案：
1 select * from 2 ( 3 select gridid,buildingid,floor,gridid+(case when buildingid is null then 'd_null' else buildingid end)+floor as tkey 4 from tommyduan_gridcell_group_all_test 5 ) t10 6 inner join 7 ( 8 select gridid,buildingid,floor,gridid+(case when buildingid is null then 'd_null' else buildingid end)+floor as tkey 9 from tommyduan_fingerlib 10 ) t11 11 on t10.tkey=t11.tkey
查询结果：
left outer join时的查询结果是：
1 select * from tommyduan_gridcell_group_all_test t10 2 left outer join tommyduan_fingerlib t11 3 on t10.gridid=t11.gridid and t10.buildingid=t11.buildingid and t10.floor=t11.floor
