九月 03
合併多筆資料到單一欄位
MySQL 4.1以後有支援一個方便的函數GROUP_CONCAT()
GROUP_CONCAT([DISTINCT] expr [,expr ...]
[ORDER BY {unsigned_integer | col_name | expr}
[ASC | DESC] [,col_name ...]]
[SEPARATOR str_val])
[ORDER BY {unsigned_integer | col_name | expr}
[ASC | DESC] [,col_name ...]]
[SEPARATOR str_val])
請直接看執行範例。XD
使用前：
SELECT empno AS '人員代號',ap_id '可用系統' FROM `ct` WHERE empno IN ('0770019','0770485') ORDER BY empno,ap_id
+----------+----------+
| 人員代號 | 可用系統 |
+----------+----------+
| 0770019 | erpchk |
| 0770019 | erp_qa |
| 0770019 | msg |
| 0770485 | erpchk |
| 0770485 | erp_qa |
| 0770485 | msg |
+----------+----------+
+----------+----------+
| 人員代號 | 可用系統 |
+----------+----------+
| 0770019 | erpchk |
| 0770019 | erp_qa |
| 0770019 | msg |
| 0770485 | erpchk |
| 0770485 | erp_qa |
| 0770485 | msg |
+----------+----------+
使用後：
SELECT empno AS '人員代號', group_concat(ap_id separator ',') AS '可用系統' FROM `ct` WHERE empno IN ('0770019','0770485') GROUP BY empno ORDER BY empno
+----------+-------------------+
| 人員代號 | 可用系統 |
+----------+-------------------+
| 0770019 | erpchk,erp_qa,msg |
| 0770485 | erpchk,erp_qa,msg |
+----------+-------------------+
+----------+-------------------+
| 人員代號 | 可用系統 |
+----------+-------------------+
| 0770019 | erpchk,erp_qa,msg |
| 0770485 | erpchk,erp_qa,msg |
+----------+-------------------+
標籤: MySQL
09-03 15:54@ 網頁設計 | 回應 | | 隱藏資訊列
回應 ( 發表您的回應 )
尚無回應