求一个简化sql语句
select * from m_test
/*
运行结果：
cd1 cd2 cd3 cd4
30002 30003 30006 30001
30005 30001 30002 30009
*/
------------------------------------------------
select * from m_test2
/*
运行结果
cd mei
30001 a
30002 b
30003 c
30004 d
30005 e
30006 f
30007 g
30008 h
30009 i
*/
-------------------------------------------------
select m_test.cd1, (select mei from m_test2 where cd = m_test.cd1) as mei1,
m_test.cd2,
(select mei from m_test2 where cd = m_test.cd2) as mei2,
m_test.cd3,
(select mei from m_test2 where cd = m_test.cd3) as mei3,
m_test.cd4,
(select mei from m_test2 where cd = m_test.cd4) as mei4
from m_test
/*
运行结果：
cd1 mei cd2 mei ...
30002 b 30003 c ...
30005 e 30001 a ... 等等。
*/
问题，我该如何简化第3条语句呢？
[解决办法]
select cd1 , m1.mei as mei1, cd2 ,m2.mei as mei2, cd3,m3.mei as mei3, cd4,m4.mei as mei4
from m_test left outer join m_test2 m1 on m_test.cd1=m1.cd
left outer join m_test2 m2 on m_test.cd2=m2.cd
left outer join m_test2 m3 on m_test.cd3=m3.cd
left outer join m_test2 m4 on m_test.cd4=m4.cd
[解决办法]
Select
A.cd1,
B.mei As mei1,
A.cd2,
C.mei As mei2,
A.cd3,
D.mei As mei3,
A.cd4,
E.mei As mei4
From
m_test A
Left Join
m_test2 B
On A.cd1 = B.cd
Left Join
m_test2 C
On A.cd2 = C.cd
Left Join
m_test2 D
On A.cd3 = D.cd
Left Join
m_test2 E
On A.cd4 = D.cd
