我有 2 張桌子

表 1 - 應用程序

-----------
id  | name
-----------
1   | abc
-----------
2   | sdf
-----------
3   | dfg
-----------

表 2 - 聯繫人

------------------------
id  | cnt_type  | cnt_id
------------------------
1   | manager   |    20
------------------------
1   | supervisor |  32
------------------------
1   | teamlead   | 45
------------------------
1   | teamlead  | 54
------------------------
2   | manager    |   20
------------------------
2   | supervisor |  32
------------------------

我需要一個如下視圖，其中 cnt_type 行表示為列，並且相應的值由每個 id 的 cnt_type 連接和分組…

---------------------------------------
id  | manager | supervisor  | teamlead
--------------------------------------
1   | 20      |    32       | 45, 54
--------------------------------------
2   | 20      |    32       |
--------------------------------------

任何幫助是極大的讚賞。

提議的查詢

SELECT
   A.id,
   IFNULL(GROUP_CONCAT(DISTINCT B.cnt_id),'') 'manager',
   IFNULL(GROUP_CONCAT(DISTINCT C.cnt_id),'') 'supervisor',
   IFNULL(GROUP_CONCAT(DISTINCT D.cnt_id),'') 'teamlead'
FROM apps A
LEFT JOIN contacts B ON A.id = B.id AND B.cnt_type='manager'
LEFT JOIN contacts C ON A.id = C.id AND C.cnt_type='supervisor'
LEFT JOIN contacts D ON A.id = D.id AND D.cnt_type='teamlead'
WHERE ISNULL(B.cnt_id)+ISNULL(C.cnt_id)+ISNULL(D.cnt_id) < 3
GROUP BY A.id;

樣本數據

DROP DATABASE IF EXISTS kumar;
CREATE DATABASE kumar;
USE kumar
CREATE TABLE apps
(
   id INT NOT NULL AUTO_INCREMENT,
   name VARCHAR(20) NOT NULL,
   PRIMARY KEY (id)
);
CREATE TABLE contacts
(
   id INT NOT NULL,
   cnt_type VARCHAR(20) NOT NULL,
   cnt_id INT NOT NULL
);
INSERT INTO apps (name) VALUES ('abc'),('sdf'),('dfg');
INSERT INTO contacts (id,cnt_type,cnt_id) VALUES
(1,'manager',20), (1,'supervisor',32), (1,'teamlead',45),
(1,'teamlead',54), (2,'manager',20), (2,'supervisor',32);

載入的樣本數據

mysql> DROP DATABASE IF EXISTS kumar;
Query OK, 2 rows affected (0.05 sec)

mysql> CREATE DATABASE kumar;
Query OK, 1 row affected (0.00 sec)

mysql> USE kumar
Database changed
mysql> CREATE TABLE apps
   -> (
   ->     id INT NOT NULL AUTO_INCREMENT,
   ->     name VARCHAR(20) NOT NULL,
   ->     PRIMARY KEY (id)
   -> );
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE contacts
   -> (
   ->     id INT NOT NULL,
   ->     cnt_type VARCHAR(20) NOT NULL,
   ->     cnt_id INT NOT NULL
   -> );
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO apps (name) VALUES ('abc'),('sdf'),('dfg');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> INSERT INTO contacts (id,cnt_type,cnt_id) VALUES
   -> (1,'manager',20), (1,'supervisor',32), (1,'teamlead',45),
   -> (1,'teamlead',54), (2,'manager',20), (2,'supervisor',32);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql>

表格中的樣本數據

mysql> select * from apps;
+----+------+
| id | name |
+----+------+
|  1 | abc  |
|  2 | sdf  |
|  3 | dfg  |
+----+------+
3 rows in set (0.00 sec)

mysql> select * from contacts;
+----+------------+--------+
| id | cnt_type   | cnt_id |
+----+------------+--------+
|  1 | manager    |     20 |
|  1 | supervisor |     32 |
|  1 | teamlead   |     45 |
|  1 | teamlead   |     54 |
|  2 | manager    |     20 |
|  2 | supervisor |     32 |
+----+------------+--------+
6 rows in set (0.00 sec)

mysql>

提議的查詢已執行

mysql> SELECT
   ->     A.id,
   ->     IFNULL(GROUP_CONCAT(DISTINCT B.cnt_id),'') 'manager',
   ->     IFNULL(GROUP_CONCAT(DISTINCT C.cnt_id),'') 'supervisor',
   ->     IFNULL(GROUP_CONCAT(DISTINCT D.cnt_id),'') 'teamlead'
   -> FROM apps A
   -> LEFT JOIN contacts B ON A.id = B.id AND B.cnt_type='manager'
   -> LEFT JOIN contacts C ON A.id = C.id AND C.cnt_type='supervisor'
   -> LEFT JOIN contacts D ON A.id = D.id AND D.cnt_type='teamlead'
   -> WHERE ISNULL(B.cnt_id)+ISNULL(C.cnt_id)+ISNULL(D.cnt_id) < 3
   -> GROUP BY A.id;
+----+---------+------------+----------+
| id | manager | supervisor | teamlead |
+----+---------+------------+----------+
|  1 | 20      | 32         | 45,54    |
|  2 | 20      | 32         |          |
+----+---------+------------+----------+
2 rows in set (0.00 sec)

mysql>

試一試 ！！！

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