条件描述
两个物理表:市直单位基本信息表(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
最终结果图