select a.id,a.name,b.type,b.company from tablea a,tableb where a.id=b.id
select a.id,a.name,b.type_a as type,a.company from tablea a join tableb on a.company=b.company union all select a.id,a.name,b.type_b as type,a.company from tablea a join tableb on a.company=b.company
select a.id,a.name,b.type_a as type,a.company from table1 a join table2 on a.company=b.company union all select a.id,a.name,b.type_b as type,a.company from table1 a join table2 on a.company=b.company
select id, name, case a.when 'type_a' then b.type_a when 'type_b' then b.type_b else type_c end, company from tablea a,tableb b where a.id=b.id
修正 if object_id('[tablea]') is not null drop table [tablea] go create table [tablea]([id] int,[name] varchar(4),[type] varchar(6),[company] varchar(5)) insert [tablea] select 1,'小明','type_a','公司1' union all select 2,'小王','type_b','公司1' if object_id('[tableb]') is not null drop table [tableb] go create table [tableb]([id] int,[type_a] int,[type_b] int,[type_c] int,[company] varchar(5)) insert [tableb] select 1,11,43,88,'公司1' union all select 2,33,55,78,'公司2'select a.id,a.name,b.type_a as type,a.company from tablea a join tableb b on a.company=b.company and a.id=b.id union all select a.id,a.name,b.type_b as type,a.company from tablea a join tableb b on a.company=b.company and a.id=b.id/** id name type company ----------- ---- ----------- ------- 1 小明 11 公司1 1 小明 43 公司1(2 行受影响) **/
select a.id,a.name, (case when a.type='type_a' then b.type_a else b.type_b end) as type, b.company from tablea a,tableb b where a.company=b.company
create table #tableb(id int, type_a int,type_b int,type_c int,company varchar(10)) insert #tableb select 1 ,11 ,43 ,88 ,'公司1' insert #tableb select 2 ,33 ,55 ,78 ,'公司2'create table #tablea(id int,name varchar(10),type varchar(10),company varchar(10))insert #tablea select 1 ,'小明','type_a','公司1' insert #tablea select 2 ,'小王','type_b','公司1'select a.id,a.name,b.value as type,a.company from #tablea a,( select id,type,value,company from #tableb unpivot(value for type in([type_a],[type_b],[type_c])) unpvt)b where a.type=b.type and a.company=b.company id name type company ----------- ---------- ----------- ---------- 1 小明 11 公司1 2 小王 43 公司1(2 行受影响)
--写错了,修正 select id, name, case a.type when 'type_a' then b.type_a when 'type_b' then b.type_b else b.type_c end [type], company from tablea a,tableb b where a.id=b.id
select a.id, a.name, case when(a.type = 'type_a') then c.type_a when(a.type = 'type_b') then c.type_b when(a.type = 'type_c') then c.type_c end as type, a.company from company a inner join ( select sys.columns.name as columnName from sys.objects inner join sys.columns on sys.objects.object_id = sys.columns.object_id where sys.objects.name = 'TableB' ) as b on a.type = b.columnName inner join TableB c on c.id = a.id 未测试, 不过感觉你得这个设计,真的很不爽
select a.id,a.name a.type=(case a.type when 'type_a' then b.type_a when 'type_b' then b.type_b else b,type_c end), a.company from tba a join tbb b on a.id=b.id
from tablea a,tableb
where a.id=b.id
union all
select a.id,a.name,b.type_b as type,a.company from tablea a join tableb on a.company=b.company
union all
select a.id,a.name,b.type_b as type,a.company from table1 a join table2 on a.company=b.company
select id, name,
case a.when 'type_a' then b.type_a when 'type_b' then b.type_b else type_c end, company
from tablea a,tableb b
where a.id=b.id
if object_id('[tablea]') is not null drop table [tablea]
go
create table [tablea]([id] int,[name] varchar(4),[type] varchar(6),[company] varchar(5))
insert [tablea]
select 1,'小明','type_a','公司1' union all
select 2,'小王','type_b','公司1'
if object_id('[tableb]') is not null drop table [tableb]
go
create table [tableb]([id] int,[type_a] int,[type_b] int,[type_c] int,[company] varchar(5))
insert [tableb]
select 1,11,43,88,'公司1' union all
select 2,33,55,78,'公司2'select a.id,a.name,b.type_a as type,a.company from tablea a join tableb b on a.company=b.company and a.id=b.id
union all
select a.id,a.name,b.type_b as type,a.company from tablea a join tableb b on a.company=b.company and a.id=b.id/**
id name type company
----------- ---- ----------- -------
1 小明 11 公司1
1 小明 43 公司1(2 行受影响)
**/
(case when a.type='type_a' then b.type_a
else b.type_b end) as type,
b.company
from tablea a,tableb b
where a.company=b.company
create table #tableb(id int, type_a int,type_b int,type_c int,company varchar(10))
insert #tableb select 1 ,11 ,43 ,88 ,'公司1'
insert #tableb select 2 ,33 ,55 ,78 ,'公司2'create table #tablea(id int,name varchar(10),type varchar(10),company varchar(10))insert #tablea select 1 ,'小明','type_a','公司1'
insert #tablea select 2 ,'小王','type_b','公司1'select a.id,a.name,b.value as type,a.company from #tablea a,(
select id,type,value,company from #tableb
unpivot(value for type in([type_a],[type_b],[type_c])) unpvt)b
where a.type=b.type and a.company=b.company id name type company
----------- ---------- ----------- ----------
1 小明 11 公司1
2 小王 43 公司1(2 行受影响)
--写错了,修正
select id, name,
case a.type when 'type_a' then b.type_a when 'type_b' then b.type_b else b.type_c end [type], company
from tablea a,tableb b
where a.id=b.id
a.id,
a.name,
case
when(a.type = 'type_a') then c.type_a
when(a.type = 'type_b') then c.type_b
when(a.type = 'type_c') then c.type_c
end as type,
a.company
from company a
inner join
(
select sys.columns.name as columnName from sys.objects
inner join sys.columns on sys.objects.object_id = sys.columns.object_id
where sys.objects.name = 'TableB'
) as b
on a.type = b.columnName
inner join TableB c on
c.id = a.id
未测试,
不过感觉你得这个设计,真的很不爽
when 'type_a' then b.type_a
when 'type_b' then b.type_b
else b,type_c end),
a.company
from tba a join tbb b on a.id=b.id