根据表1的行值查找表2同名列字段的值.

[bonesoup]

表1:
部门类别 部门代码
类别1 123
类别2 456
类别3 789
类别2 456
类别3 789

表2:
类别1 类别2 类别3
123 456 789

如何将表2的内容查找到表1的部门代码字段?谢谢.

[csdyyr]

select * 
from 表1 as a join 表2 as b on 
 a.部门类别=b.类别1 or
 a.部门类别=b.类别2 or
 a.部门类别=b.类别3

[-狙击手-]

(select '类别1' as col ,类别1 from table2
union all
select '类别2',类别2 from table2
union all
select '类别3', 类别3 from table2)

[Yang_]

select 部门类别,
(select max(case 表1.部门类别 when '类别1' then 类别1 when '类别2' then 类别2 when '类别3' then 类别3 end) from 表2) as 部门代码
from 表1 

[jimoshatan]

CREATE TABLE #A (PART VARCHAR(10),CODE VARCHAR(10))
INSERT INTO #A SELECT 'PART1',''
INSERT INTO #A SELECT 'PART2',''
INSERT INTO #A SELECT 'PART3',''
INSERT INTO #A SELECT 'PART2',''
INSERT INTO #A SELECT 'PART3',''

CREATE TABLE #B (PART1 VARCHAR(10),PART2 VARCHAR(10),PART3 VARCHAR(10))
INSERT INTO #B SELECT '123','456','789'


SELECT PART,(SELECT CASE PART WHEN 'PART1' THEN PART1 WHEN 'PART2' THEN  PART2 WHEN 'PART3' THEN PART3  END FROM #B)CODE FROM #A



DROP TABLE #A,#B

/**
PART1	123
PART2	456
PART3	789
PART2	456
PART3	789

[bonesoup]

谢谢各位的热心帮助.