我這個標題我還想了一下要怎麼下
下太複雜我怕大家又看不太懂，還有搜尋引擎關鍵字的問題
(題外話……)
這個用看的不會懂，做過才知道其中的奧妙
————————————————————————–
問題
問題是這樣的
假設說，我有個資料庫，分別存了A表格 B表格 C表格 ……
都是主鍵(Primary Key)+外鍵(Foreign Key)的格式
這幾張表都有外來鍵只向同一張表格的主鍵
也就是
A表格
A_id(主鍵) A_name data ……
B表格
B_id(主鍵) A_id(外來鍵) data ……
C表格
C_id(主鍵) A_id(外來鍵) data ……
如今
我想要個別統計一下資料的狀況
最後呈現的欄位是這樣
A_id A_name B_count C_count
這對我們來說不是難事
所以我們在資料庫下了這些SQL (以下使用MySQL的格式表示)
對表格B做統計
SELECT `A_id`, COUNT(`B_id`) as `B_count` FROM `tableB` GROUP BY `A_id`;
得到
A_id B_count 的欄位
對表格B做統計
SELECT `A_id`, COUNT(`C_id`) as `C_count` FROM `tableC` GROUP BY `A_id`;
得到
A_id C_count 的欄位
問題來了
我們有學過JOIN，他們都有A_id
我們如何把他們另外統計的二張表格合起來
還要顯示A表格的其他內容
你要如何做?
————————————————————————–
第一，我可能會想到在JOIN的過程中
不能直接JOIN起來的話
好歹列數也要一致相同吧
所以我改這樣下
SELECT `A_id`, `A_name`, COUNT(`tableB`.`A_id`) as `B_count` FROM `tableA` LEFT JOIN `tableB` ON `tableA`.`A_id`=`tableB`.`A_id` GROUP BY `tableA`.`A_id`;
SELECT `A_id`, `A_name`, COUNT(`tableC`.`A_id`) as `C_count` FROM `tableA` LEFT JOIN `tableC` ON `tableA`.`A_id`=`tableC`.`A_id` GROUP BY `tableA`.`A_id`;
使用LEFT JOIN是因為INNER JOIN是有對到資料才會出現
而LEFT JOIN才會顯示Count是零的狀況
這樣也不盡人意……
————————————————————————–
解答
找好久才找到這個解答
我個人認為其重要性接近死背這個架構
SELECT `A_id`, `A_name`,
(SELECT COUNT(*) FROM `tableB` WHERE `tableB`.`A_id`=`tableA`.`A_id`) AS `B_count`,
(SELECT COUNT(*) FROM `tableC` WHERE `tableC`.`A_id`=`tableA`.`A_id`) AS `C_count`,
FROM `tableA`;
這個架構叫做
關聯子查詢
SQL語句裡面跟外面有產生關聯的
但是光看問題不會馬上想到這個架構
解釋
簡單來說一句SQL語法做了三個動作
從刮號裡面先看好了
裡面有二句SQL語法，看起來像是獨立的但不能跑
會有這個錯誤
#1054 – Unknown column ‘tableA.A_id’ in ‘where clause’
因為他找不到tableA在哪裡
SELECT COUNT(*) FROM `tableB` WHERE `tableB`.`A_id`=`tableA`.`A_id`
這句的意思是計算tableB總共有幾筆
我們有學到在WHERE裡面打上
`tableB`.`A_id`=`tableA`.`A_id`
意思就是要讓表格合併起來 (像是INNER JOIN)
—————————————————-
*TIPS:
WHERE id=id能獨立跑的是這個
SELECT `tableA`.*, `tableB`.* FROM `tableA`, `tableB` WHERE `tableB`.`A_id`=`tableA`.`A_id`
用INNER JOIN 改寫變成
SELECT `tableA`.*, `tableB`.* FROM `tableA`INNER JOIN `tableB` ON `tableB`.`A_id`=`tableA`.`A_id`
—————————————————-
在來就是外面那層
SELECT `A_id`, `A_name`, (……) AS `B_count`, (……) AS `C_count`, FROM `tableA`;
就只有簡單的撈整張表格出來而已
AS就是做欄位別名
整個SQL語句要做為一個欄位賦予別名
那該SQL語句必須資料輸出一個欄位而已
這裡輸出COUNT(*)剛好只有一欄，符合規定
—————————————————-
其它範例
有個問卷的系統，表格長這樣
這個學生詢問了向各種對象，問各種不同的問題，放在不同的表格
而如今他想要把他的問題做統計
欄位名稱如下
Student(id,name,mykad,…)
Customer Service(id, Q1,Q2,Q3,date)
Instructor(id, Q1,Q2,Q3,date)
Runner(id, Q1,Q2,Q3,date)
SQL語法如下：
SELECT A.name, A.mykad,
(select count(*) from customerservice B where B.id = A.id and B.Q1=’Excellent’) AS E,
(select count(*) from customerservice C where C.id = A.id and C.Q1=’Satisfaction’) AS S,
(select count(*) from customerservice D where D.id = A.id and D.Q1=’Poor’) AS P
FROM student A
就會輸出欄位
name mykad E S P
這裡的參考資料一定要看，他寫的比我還要詳細
http://stackoverflow.com/questions/1279569/sql-combine-select-count-from-multiple-tables
http://www.daniweb.com/software-development/legacy-and-other-languages/threads/352342