条件描述

       两个物理表：市直单位基本信息表(T_UnitBaseInfo)，记录所有的市直单位信息，如：市直单位ID，市直单位名称，市直单位状态，是否参评；市直单位定量指标表(T_UnitQuantifyTarget)，记录每个定量指标，针对每个参评市直单位的描述，如：指标ID，指标名称，指标权重，参评市直单位ID，考核该指标的单位1ID，考核该指标的单位1ID，考核该指标的单位1ID。考核指标单位的个数小于等于3，考核指标的单位都是市直单位。

问题描述

       从数据库中查询出一张表：将T_UnitQuantifyTarget表中的考核该指标的单位ID换成相应的市直单位名称。

解决方法

       1、从T_UnitQuantifyTarget表中获得“考核该指标的单位1ID”不为NULL的“指标ID”和“考核该指标的单位1ID”的Unit1表；

       2、从T_UnitQuantifyTarget表中获得“考核该指标的单位2ID”不为NULL的“指标ID”和“考核该指标的单位2ID”的Unit2表；

       3、从T_UnitQuantifyTarget表中获得“考核该指标的单位3ID”不为NULL的“指标ID”和“考核该指标的单位3ID”的Unit3表；    

       4、将Unit1、Unit2和Unit3合并为一张表UnitT1，字段为：指标ID，考核该指标的单位1名称，考核该指标的单位2名称，考核该指标的单位3名称。

       5、通过查询UnitT1和T_UnitQuantifyTarget，得到最终结果。

具体Sql代码

create view V_UnitQulityTarget
as
	with Unit1
	as
	(
		select id,DepartmentName from T_UnitBaseInfo as u1,T_UnitQuantifyTarget as t1 where u1.CityID=t1.ResponsibilityUnit1
	),
	Unit2
	as
	(
		select id,DepartmentName from T_UnitBaseInfo as u1,T_UnitQuantifyTarget t1 where u1.CityID=t1.ResponsibilityUnit2
	),
	Unit3
	as
	(
		select id,DepartmentName from T_UnitBaseInfo as u1,T_UnitQuantifyTarget t1 where u1.CityID=t1.ResponsibilityUnit3
	),
	UnitT1
	as
	(
		select 
			Unit1.id as ID1,Unit2.id as ID2,Unit3.id as ID3,Unit1.DepartmentName as ResponsibilityUnit1Name,Unit2.DepartmentName as ResponsibilityUnit2Name,Unit3.DepartmentName as ResponsibilityUnit3Name
		from 
			Unit1 full outer join  
				(
					Unit2 full outer join Unit3
					on Unit2.id = Unit3.id
				)
			on
				Unit1.id = Unit2.id
	),
	UnitT2
	as
	(
		select
			case when ID1 IS null then
				case 
					when ID2 IS null then ID3 
				else
					ID2
				end
			else
				ID1
			end
			as targetID, ResponsibilityUnit1Name,ResponsibilityUnit2Name,ResponsibilityUnit3Name
		from 
			UnitT1
	)
	select 
		T2.Id as targetID,
		Name,Type,Weight,
		case 
			when T1.ResponsibilityUnit1Name IS NULL then T2.ResponsibilityUnit1
			else T1.ResponsibilityUnit1Name
		end as ResponsibilityUnit1Name
		,
		case	
			when T1.ResponsibilityUnit2Name is null then T2.ResponsibilityUnit2
			else t1.ResponsibilityUnit2Name
		end as ResponsibilityUnit2Name
		,
		case	
			when T1.ResponsibilityUnit3Name is null then T2.ResponsibilityUnit3
			else T1.ResponsibilityUnit3Name
		end as ResponsibilityUnit3Name
		,
		YearTime,CityUnitId,Timestamp, IfRecord
	from 
		T_UnitQuantifyTarget as T2 left outer join UnitT2 as T1 on T1.targetID = T2.Id

附录图片

T_UnitBaseInfo

T_UnitQuantifyTarget

最终结果图

总结

一个表内的多个ID用另一个表内的名称替换

原文：http://blog.csdn.net/jiben2qingshan/article/details/19766067