Thursday, April 19, 2012

[MYSQL] 多個資料表(兩個以上)的連結(INNER JOIN)

第一種寫法:
SELECT * FROM(
SELECT * FROM (
SELECT * FROM (
SELECT * FROM (
SELECT * FROM `table001` AS t1 INNER JOIN `table002` AS t2 ON t1.t1_no=t2.t2_t1_no
) AS t12 INNER JOIN `table003` AS t3 ON t12.t2_no=t3.t3_t2_no
) AS t123 INNER JOIN `table004` AS t4 ON t123.t3_no=t4.t4_t3_no
) AS t1234 INNER JOIN `table005` AS t5 ON t1234.t4_no=t5.t5_t4_no
) AS t12345 INNER JOIN `table006` AS t6 ON t12345.t5_no=t6.t6_t5_no
照此架構,以此類推即可。


第二種種寫法:

SELECT
  *
FROM
  ` table001` AS t1
  INNER JOIN ` table002` AS t1 ON t1.t1_no = t2.t2_no
  INNER JOIN ` table003` AS t3 ON t2.t2_no = t3.t3_no
  INNER JOIN ` table004` AS t4 ON t3.t3_no = t4.t4_no  INNER JOIN ` table005` AS t5 ON t4.t4_no = t5.t5_no 
WHERE
  t1.status = 'y' AND t2.status = 'y' AND t3.status = 'y'

可以一直串接下去。


我個人比較推薦第二種寫法。

No comments:

Post a Comment