关于左连接只连接上了第一条数据
原贴地址:
http://community.csdn.net/Expert/topic/5660/5660584.xml?temp=.575741
他提问如下:
bianh mingxh xmingc
000001 000030 左眼
000001 000031 右眼
000001 000032 色觉
000067 000001 视力检查
000067 000019 彩超
000067 000020 总胆红素
000068 000003 心电图检查
000068 000004 转氨酶
以下是我要问的
我想执行一条SQL语句后,如下显示
bianh mingxh mingc
000067 000030 左眼
000067 000031 右眼
000067 000032 色觉
000067 000019 彩超
000067 000020 总胆红素
请问这条SQL语句如何写?
我的回答:
SELECT
CASE WHEN T5.BIANH1 = T5.MINGXH2 THEN T5.BIANH2 ELSE T5.BIANH1 END AS BIANH, T5.MINGXH1 AS MINGXH
FROM
(SELECT
T3.BIANH AS BIANH1, T3.MINGXH AS MINGXH1, T3.XMINGC, T4.MINGXH AS MINGXH2, T4.BIANH AS BIANH2
FROM TEMP2 T3
LEFT JOIN
(SELECT
T2.Col4 AS BIANH, T2.COL5 AS MINGXH
FROM
(SELECT BIANH AS Col1 FROM TEMP2) T1
INNER JOIN
(SELECT BIANH AS Col4, MINGXH AS Col5 FROM TEMP2) T2
ON T1.Col1 = T2.Col5
WHERE NVL(T2.COL4,0) <> 0) T4
ON T3.BIANH = T4.MINGXH
) T5
WHERE (T5.BIANH1 || T5.MINGXH1) <> (NVL(T5.BIANH2,'SSSSS') || NVL(T5.MINGXH2,'SSSSS'))
我的做法显然只连接上了一条数据,为什么?谢谢