如何將許多資訊分組而不是一個選擇

有我的數據庫

articles
id | art_code | art_name
1  | 5678     | computer
2  | 0987     | display
3  | 8547     | keyboard

action
id | code        | action      | qte
1  | 5678        | IN          | 4
2  | 0987        | OUT         | 3
3  | 5678        | IN          | 6
4  | 5678        | OUT         | 2

我想用 IN / OUT 組的 SUM() 逐篇選擇文章的所有值

前任：

RESULT:

   id | art_code | art_name  | IN  |  OUT 
   1  | 5678     | computer  | 10  |   2  
   2  | 0987     | display   | 3   |   NULL  
   3  | 8547     | keyboard  | NULL|   NULL  

我嘗試但是，我的結果是：

結果：

   id | art_code | art_name  | IN  |  OUT 
   1  | 5678     | computer  | 10  |   NULL  
   1  | 5678     | computer  | NULL|   2  
   2  | 0987     | display   | 3   |   NULL  

我不能在 article.art_code 的 1 行中包含 SUM(IN) 和 SUM(OUT)。

一個想法？

SELECT id, art_code, art_name, SUM(`IN`) `IN`, SUM(`OUT`) `OUT`
FROM 
 ( SELECT a0.id, a0.art_code, a0.art_name, SUM(a1.qte) `IN`, NULL `OUT`
   FROM article a0
   LEFT JOIN action a1 ON a0.art_code = a1.code AND a1.action = 'IN'
   GROUP BY a0.id, a0.art_code, a0.art_name
 UNION ALL
   SELECT a0.id, a0.art_code, a0.art_name, NULL, SUM(a2.qte)
   FROM article a0
   LEFT JOIN action a2 ON a0.art_code = a2.code AND a2.action = 'OUT'
   GROUP BY a0.id, a0.art_code, a0.art_name
 ) sq
GROUP BY id, art_code, art_name
ORDER BY id;

小提琴

引用自：https://dba.stackexchange.com/questions/232191