最近因為接手維護 & 開發舊的系統, 所以必須熟悉 前人寫的 SQL語法, 前輩習慣使用一串SQL語法,把所有的資料撈出來 ,這樣節省很多時間 ,速度也會快很多.
但是, 對於維護的人來說 ,剛開始真是痛苦啊 ...其中, 使用很多的 LEFT JOIN 合併資料, 我覺得很方便 ,但是不熟悉 會搞錯, 所以慢慢的會把相關的心得整理
作為資料參考備忘 放在這裡 ,歡迎交流討論喔 !!

以下，是國外論壇的一篇發問, 關於如何使用兩個以上的 LEFT JOIN ..

The inefficiency of the IN clause in SQL statement is well known. Could anyone suggest some workarounds for the following case.

I have three tables tb1,tb2 and tb3. I would like to sum up field values from two tables,and show a list of person name whose name appear in tb3. How to do this using a SQL statement.

Code:
tb1
Name Salary
Bob 1000
Tom 2000
John 3000
Winson 4000



Code:
tb2
Name Bonus
Bob 100
Tom 200

 

Code:
tb3
Name
Bob
Tom



I would like to get the following result

Code:
Name Total
Bob 1100
Tom 2200




i would like to use the following statement

Code:
SELECT
     tb1.name, (tb1.salary+tb2.bonus) as remuneration
FROM
     tb1
LEFT JOIN
     tb2 on tb2.Name=tb1.Name
WHERE tb1.name IN tb3



However,when tb1, tb2 and tb3 are very large, the above statement is inefficient. Can anyone suggest more efficient alternative statements.

Thanks The inefficiency of the IN clause in SQL statement is well known. Could anyone suggest some workarounds for the following case.

I have three tables tb1,tb2 and tb3. I would like to sum up field values from two tables,and show a list of person name whose name appear in tb3. How to do this using a SQL statement.

Code:
tb1
Name Salary
Bob 1000
Tom 2000
John 3000
Winson 4000



Code:
tb2
Name Bonus
Bob 100
Tom 200

 

Code:
tb3
Name
Bob
Tom



I would like to get the following result

Code:
Name Total
Bob 1100
Tom 2200




i would like to use the following statement

Code:
SELECT
     tb1.name, (tb1.salary+tb2.bonus) as remuneration
FROM
     tb1
LEFT JOIN
     tb2 on tb2.Name=tb1.Name
WHERE tb1.name IN tb3



However,when tb1, tb2 and tb3 are very large, the above statement is inefficient. Can anyone suggest more efficient alternative statements.

解答:

With appropriate indexes, this should be quite efficient enough:

SELECT tb3.employee_name, (salary+bonus) remuneration
FROM tb3
LEFT JOIN tb1
ON tb1.employee_name = tb3.employee_name
LEFT JOIN tb2
ON tb2.employee_name = tb3.employee_name
;

以上與大家分享.

MySQL討論區, 有很多不錯的資訊, 歡迎參考 http://forums.devshed.com/ 


arrow
arrow
    全站熱搜

    Frank 發表在 痞客邦 留言(1) 人氣()

    E-mail轉寄