提出查询

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>

试试看 ！！！