sq多表查询

  昨天做项目时遇到了sql多表查询的问题，需要把A表中的type字段对应在B（jeesite字典表）表中的label字段查出，如果是只有一个type还好，但是还有一个grade字段的label也需要取出，而在B表中type和grade是两条记录而不是一条记录的两列。纠结了一个小时后，终于想出解决办法。

select 
"title","type", "typename","grade","gradename","lon","lat","department",r."NAME" AS "departmentname","remarks"
from 
(select 
  a.pt_name  AS "title",
  a.pt_type  AS  "type",
  b.label    AS  "typename",
  a.pt_grade AS  "grade",
  a.pt_lon  AS "lon",
  a.pt_lat  AS  "lat",
  a.pt_department AS  "department",
  a.pt_common  AS "remarks"
FROM rps_pt_baseinfo a
LEFT JOIN sys_dict b ON b.type = 'pt_type' and b.VALUE=a.pt_type
where 	a.del_flag = 0) q
,
(
select 
  a.pt_name  AS "title1",
  a.pt_type  AS  "type1",
  a.PT_GRADE  AS  "grade1",
  b.label  AS  "gradename",
  a.pt_lon  AS "lon1",
  a.pt_lat  AS  "lat1",
  a.pt_department AS  "department1",
  a.pt_common  AS "remarks1"
FROM rps_pt_baseinfo a
LEFT JOIN sys_dict b ON b.type = 'pt_grade' and b.VALUE=a.pt_grade
where 	a.del_flag = 0) w,
SYS_OFFICE r
where q."remarks" =w."remarks1" and q."department"=r."ID"

我先把A表与B表Join查出type的labe了，把它当作一个表C，然后再把A表与B表join查出grade的label，把它当作表D，然后再在表C、D之间选出想要的typelabel和gradelabel

虽然感觉很多而且应该能优化，但是先这样吧，毕竟已经能够达到我要的目的了@_@