MySql:从同一表的三个不同列中提取不同的条目

Question

我有一个名为 deals 的表，其中存储了 client_id1、client_id2、client_id3。

这些 id 对应于 clients 表，其中包含 clientName 和 client_id 字段。

如何创建名称字符串数组，其 ID 位于交易表的三列中的任何一列中。

Answer 1

SELECT GROUP_CONCAT(Client) FROM (
 SELECT clientName as Client 
 FROM deals JOIN clients
 ON deals.client_id1 = clients.client_id
UNION ALL
 SELECT clientName as Client 
 FROM deals JOIN clients
 ON deals.client_id2 = clients.client_id
UNION ALL
 SELECT clientName as Client 
 FROM deals JOIN clients
 ON deals.client_id3 = clients.client_id)
GROUP BY Client

说明:

从一张表中获取客户端:

 SELECT clientName as Client 
 FROM deals JOIN clients
 ON deals.client_id1 = clients.client_id

工会也将从其他表中获取它们

 SELECT clientName as Client 
 FROM deals JOIN clients
 ON deals.client_id1 = clients.client_id
UNION ALL
 SELECT clientName as Client 
 FROM deals JOIN clients
 ON deals.client_id2 = clients.client_id
UNION ALL
 SELECT clientName as Client 
 FROM deals JOIN clients
 ON deals.client_id3 = clients.client_id)

现在要获得一个逗号分隔的字符串，要么在客户端(php)执行，要么让 MySQL 服务器执行，使用 GROUP_CONCAT :

SELECT GROUP_CONCAT(Client) FROM (
 SELECT clientName as Client 
 FROM deals JOIN clients
 ON deals.client_id1 = clients.client_id
UNION ALL
 SELECT clientName as Client 
 FROM deals JOIN clients
 ON deals.client_id2 = clients.client_id
UNION ALL
 SELECT clientName as Client 
 FROM deals JOIN clients
 ON deals.client_id3 = clients.client_id)
GROUP BY Client