查询获取基于另一个表的值的列值,即一个表值具有作为另一个表中的字段名称
问题描述:
我有两个表hrm_m_allowance,pay_m_allowance。查询获取基于另一个表的值的列值,即一个表值具有作为另一个表中的字段名称
像hrm_m_allowance有场......像
INT_APPID INT_BASIC A0001 A0002 A0003 A0004 A0005
---------------------------------------------------------
14 7900 1200 700 2000 1000 500
pay_m_allowance其领域...............
CHR_ACODE CHR_ANAME CHR_BONUS .......etc
----------------------------------------------
A0001 HRA 0
A0002 DA 0
A0003 PF 0
A0004 ESI 0
现在我得值像
BASIC 7900
HRA 1200
DA 700
PF 2000
ESI 1000
请帮我如何获得价值像上面使用MySQL查询
答
时髦的数据库设计。我建议首先unpivoting的数据,然后做join
:
select coalesce(pb.chr_aname, col) as chr_aname, h.val
from (select 'INT_BASIC' as col, INT_BASIC as val from hrm_m_allowance union all
select 'A0001' as col, A0001 as val from hrm_m_allowance union all
select 'A0002' as col, A0002 as val from hrm_m_allowance union all
select 'A0003' as col, A0003 as val from hrm_m_allowance union all
select 'A0004' as col, A0004 as val from hrm_m_allowance union all
select 'A0005' as col, A0005 as val from hrm_m_allowance
) h left join
pay_m_allowance pb
on pb.chr_acode = h.col;
警告:将最好的工作时,所有列具有相同的数据类型。
是否有连接两个表的字段? – fen1ksss 2014-10-31 09:58:57
是的,实际上我必须加入另一张表hrm_m_offerletter。 hrm_m_offerletter,hrm_m_allowance具有公共字段INT_APPID。 – user3898783 2014-10-31 10:28:56