22,209
社区成员
发帖
与我相关
我的任务
分享
--> 测试数据:[Building]
if object_id('[Building]') is not null drop table [Building]
create table [Building]([ID] int,[Name] varchar(10),[BuildingState] int,[UseType] int)
insert [Building]
select 1,'xx花园xx栋',0,1 union all
select 2,'xx花园xx栋',1,1
--> 测试数据:[CODEVALUE]
if object_id('[CODEVALUE]') is not null drop table [CODEVALUE]
create table [CODEVALUE]([CodeType] varchar(15),[CodeID] int,[CodeValue] varchar(12))
insert [CODEVALUE]
select 'BuildingState',0,'在建建筑物' union all
select 'BuildingState',1,'已竣工建筑物' union all
select 'BuildingUseType',0,'商业用途' union all
select 'BuildingUseType',1,'住宅用途'
select t.ID,t.Name,t.CodeValue as BuildingState,
d.CodeValue as BuildingUseType from(
select a.ID,a.Name,b.CodeValue,a.UseType from [Building] a
inner join [CODEVALUE] b on a.BuildingState=b.CodeID
where b.CodeType='BuildingState')t
inner join [CODEVALUE] d
on t.UseType=d.CodeID
where d.CodeType='BuildingUseType'
/*
ID Name BuildingState BuildingUseType
1 xx花园xx栋 在建建筑物 住宅用途
2 xx花园xx栋 已竣工建筑物 住宅用途
*/
select
case when a.BuildingState=0 and b.CodeType='BuildingState' and b.CodeID=0 then b.CodeValue else 0 end ,
case when a.UseType=1 and b.CodeType='BuildingState' and b.CodeID=0 then b.CodeValue else 0 end
from
Building a inner join CODEVALUE b
on
a.xx=b.xx