主表：tb_main
---------------------------------------------------------------------
userid name sex pol nation edu
10001 欧阳锋 1 02 01 02
10002 黄药师 1 01 02 01
10003 黄容 0 02 01 01
代码表：tb_code
---------------------------------------------------------------------
code explain codenote
1 男 sex
2 女 sex
01 党员 pol
02 群众 pol
01 大学 edu
02 专科 edu
03 高中 edu
01 汉族 nation
02 藏族 nation
01 满族 nation
我想把tb_main与tb_code关联起来，这样写的：
SELECT userid
,name
,(SELECT explain FROM tb_code WHERE code = sex) AS sex
,(SELECT explain FROM tb_code WHERE code = pol) AS pol
,(SELECT explain FROM tb_code WHERE code = nation) AS nation
,(SELECT explain FROM tb_code WHERE code = edu) AS edu
FROM tb_main
ORDER BY userid;
感觉效率太低，哪位老师提供一个好些的语句，如怎样使用join？多谢！
------解决思路----------------------
with tb_main(userid,name,sex,pol,nation,edu) as
(
select 10001,'欧阳锋','01','02','01','02' union all
select 10002,'黄药师','01','01','02','01' union all
select 10003,'黄容','01','02','01','01'
),tb_code(code,explain,codenote) as
(
select '01','男','sex' union all
select '22','女','sex' union all
select '01','党员','pol' union all
select '02','群众','pol' union all
select '01','大学','edu' union all
select '02','专科','edu' union all
select '03','高中','edu' union all
select '01','汉族','nation' union all
select '02','藏族','nation' union all
select '01','满族','nation'
)
select * from
(select userid,name,总,explain from
(select * from tb_main unpivot (汇 for 总 in ([sex],[pol],[nation],[edu])) as b) as c
left join tb_code as d on c.汇=d.code and c.总=d.codenote) as e pivot (max(e.explain) for
总 in ([sex],[pol],[nation],[edu])) as f
/**
userid name set pol nation edu
-----------------------------------------
10003 黄容 男 群众 满族 大学
10002 黄药师 男 党员 藏族 大学
10001 欧阳锋 男 群众 满族 专科
-----------------------------------------
**/
------解决思路----------------------
他只有这段是要给你的
select * from
(select userid,name,总,explain from
(select * from tb_main unpivot (汇 for 总 in ([sex],[pol],[nation],[edu])) as b) as c
left join tb_code as d on c.汇=d.code and c.总=d.codenote) as e pivot (max(e.explain) for
总 in ([sex],[pol],[nation],[edu])) as f
------解决思路----------------------
这一段是创建临时表tb_main，以便下面的查询使用
with tb_main(userid,name,sex,pol,nation,edu) as
(
select 10001,'欧阳锋','01','02','01','02' union all
select 10002,'黄药师','01','01','02','01' union all
select 10003,'黄容','01','02','01','01'
),tb_code(code,explain,codenote) as
(
select '01','男','sex' union all
select '22','女','sex' union all
select '01','党员','pol' union all
select '02','群众','pol' union all
select '01','大学','edu' union all
select '02','专科','edu' union all
select '03','高中','edu' union all
select '01','汉族','nation' union all
select '02','藏族','nation' union all
select '01','满族','nation'
)