[SQL] 多個各自count之後還要join成一張表 (在多個表格裡count資料)

我這個標題我還想了一下要怎麼下

下太複雜我怕大家又看不太懂，還有搜尋引擎關鍵字的問題

(題外話……)

 

這個用看的不會懂，做過才知道其中的奧妙

————————————————————————–

問題

問題是這樣的

 

假設說，我有個資料庫，分別存了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