用左连接完成对一个表中的多个外键字段替换查询返回名称

---------- 创建4个表结构，其中jobs表中的三个字段cityID、provinceID、countryID对应着其他3个表的主键

CREATE TABLE [dbo].[city] ( [ID] [smallint] IDENTITY (1, 1) NOT NULL , [cityName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY]GO

CREATE TABLE [dbo].[country] ( [ID] [smallint] IDENTITY (1, 1) NOT NULL , [countryName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY]GO

CREATE TABLE [dbo].[jobs] ( [job_id] [smallint] IDENTITY (1, 1) NOT NULL , [userName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [cityID] [int] NULL , [provinceID] [int] NULL , [countryID] [int] NULL , [productName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY]GO

CREATE TABLE [dbo].[province] ( [ID] [smallint] IDENTITY (1, 1) NOT NULL , [provinceName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY]GO

 

----查询jobs表中的三个外键对应的名称

---t1 get countryNameselect job_id, userName, provinceID,countryName,ProductName from jobs as t1 left  join country t2 on t2.id = t1.countryID

-- get countryName and provinceNameselect job_id, userName, provinceName,countryName,ProductName from (  select job_id, userName, provinceID,countryName,ProductName  from jobs as t1 left  join country t2 on t2.id = t1.countryID     ) as t3 left join Province t4 on t3.provinceID = t4.ID

-- get all select job_id, userName, cityName, provinceName,countryName,ProductName from( select job_id, userName,cityID, provinceName,countryName,ProductName  from (   select job_id, userName,cityID, provinceID,countryName,ProductName   from jobs as t1 left  join country t2 on t2.id = t1.countryID      ) as t3 left join Province t4 on t3.provinceID = t4.ID     )  as t5 left join city t6 on t5.cityID = t6.ID