SQL查询多个表，具有多个联接和带有逗号分隔列表的列字段

Question

我有一个查询，在其中我联接了三个单独的表（节点，控件，服务）。

下面是它们的列标题和示例数据。

NODE TABLE  (contains over 7000 rows)
nodeID | host    | serviceID        | controlID
     1 | server1 | 1,2,3,4,9,50,200 |         1
     2 | server2 | 2,3,4,9,200      |         2
     3 | server3 | 1,2,3,4,9,50,200 |         2
     4 | server4 | 1,2,50,200       |         3
     5 | server5 | 1,4              |         3

CONTROL TABLE  (contains roughly 50 rows)
controlID | name
        1 | Control Name One
        2 | Control Name Two
        3 | Control Name Three
        4 | Control Name Four
        5 | Control Name Five

SERVICE TABLE (contains roughly 3000 rows)
serviceID | name
        1 | Service Name One
        2 | Service Name Two
        3 | Service Name Three
        4 | Service Name Four
        5 | Service Name Five
        6 | Service Name Six
       50 | Service Name 50
      200 | Service Name 200

如您所见，除了 node.serviceID 列之外，数据库表还有一些标准化。我完全衷心同意应规范化 node.serviceID
并创建一对多的数据透视表。那里没有争论。但是，我不控制将信息插入数据库的脚本。我只能从表中读取数据并格式化数据。

因此，下面是我编写的有效的SQL查询，但按预期， node.serviceID 与 service.serviceID
不能很好地结合在一起。请注意，我在最终查询中没有使用SELECT *，我从节点表中选择了大约20个字段，并且不想让查询更加混乱。以下仅是示例。

SELECT *
FROM node AS a
LEFT JOIN control AS b ON a.controlID = b.controlid
LEFT JOIN service AS c ON a.serviceID = c.serviceId
ORDER BY a.host

上面的查询吐出类似的内容：

Host      Control              Services
server1   Control Name One     1,2,3,4,9,50
server2   Control Name Three   1,2,9,50
server3   Control Name Two     4
server4   Control Name Four    1,2,3,4,9
server5   Control Name Two     1,2,3,50
server6   Control Name Five    1,3,4,9,50

我正在寻找的是：

Host      Control              Services
server1   Control Name One     Service Name One,
                               Service Name Two,
                               Service Name Three,
                               Service Name Four,
                               Service Name Nine,
                               Service Name Fifty
server2   Control Name Three   Service Name One,
                               Service Name Two,
                               Service Name Nine,
                               Service Name Fifty
server3   Control Name Two     Service Name Four
server4   Control Name Four    Service Name One,
                               Service Name Two,
                               Service Name Three,
                               Service Name Four,
                               Service Name Nine

我已经搜寻了stackoverflow.com来解决类似这样的问题，但是我只能找到在ID和名称上连接多个表的人，或者正在扩展ID列表但又不能同时扩展两个人的人。

这很接近：使用以逗号分隔sql的id，但使用的不是完全相同。

我已经尝试过使用ListToArray（）使用CFML的各种方法，并尝试使用索引循环遍历它们，但是对我来说没有任何用处。

我从中获取数据的服务器是MySQL 5.1，我正在使用jQuery和ColdFusion（Railo 4.2）的组合来格式化数据。

这是我第一次在stackoverflow上发帖，所以我很抱歉，如果真的有答案，我没有搜索足够长的时间，因此这个问题会重复出现。

-----------------更新--------------------

我尝试了Leigh建议的查询和CFML。

因此，我得到以下信息：

server1服务名称一，服务名称一，服务名称一，服务名称一，服务名称一，服务名称一，服务名称一，服务名称二，服务名称二，服务名称二，服务名称二，服务名称二，服务名称二，服务名三，服务名四，服务名四，服务名四，服务名四，服务名四，服务名四，服务名四

在这一点上，我不确定CFML或SQL查询中的内容是否只是一点点更改。但是，它看起来确实很有希望。

Answer 1

如果您确实无法修改表结构，则可能最好的方法是使用旧列​​表技巧之一：

• 使用JOIN与FIND_IN_SET（值，commaSeparatedString）

SELECT n.Host, c.Name AS ControlName, s.Name AS ServiceName FROM node n LEFT JOIN control c ON c.controlID = n.controlID LEFT JOIN service s ON FIND_IN_SET(s.serviceID, n.serviceId) ORDER BY n.host, s.Name ;

• 使用LIKE检测节点列表中的特定服务ID值的存在

SELECT n.Host, c.Name AS ControlName, s.Name AS ServiceName FROM node n LEFT JOIN control c ON c.controlID = n.controlID LEFT JOIN service s ON CONCAT(',', n.serviceID,',') LIKE CONCAT('%,', s.serviceID,',%') ORDER BY n.host, s.Name ;

SQLFiddle

但是，正如您已经指出的，该列确实应该被规范化。尽管上面的方法应该适用于小型数据集，但是它们却遇到了使用“列表”的常见问题。两种方法都不十分适合索引，因此无法很好地扩展。同样，它们都执行字符串比较。因此，最微小的差异可能会导致匹配失败。例如，1,4将匹配两个serviceID，而1,(space)4或1,4.0仅匹配一个。

根据评论更新：

在二读时，我不确定上面的答案是否能回答您所要提出的确切问题，但是它应该为使用…提供良好的基础。

如果您不再需要CSV列表，则只需使用上面的查询之一，然后照常输出各个查询列即可。结果将是每行一个服务名称，即：

   server1 | Control Name One | Service Name 200
   server1 | Control Name One | Service Name 50
   ..

否则，如果您需要保留逗号分隔的值，则一种可能性是对<cfoutput group="..">查询结果使用a
。由于结果首先由“主机”排序，因此类似于下面的代码。 注意：
要使“组”正常工作，必须按以下顺序对结果进行排序，Host并且必须使用多个cfoutput标签，如下所示。

 <cfoutput query="..." group="Host"> 
    #Host# |
    #ControlName# |
    <cfoutput>
      #ServiceName#,
    </cfoutput>
    <br>
 </cfoutput>

结果应如下所示：

server1 | Control Name One | Service Name 200, Service Name 50, Service Name Four, Service Name One, Service Name Three, Service Name Two, 
server2 | Control Name Two | Service Name 200, Service Name Four, Service Name Three, Service Name Two, 
server3 | Control Name Two | Service Name 200, Service Name 50, Service Name Four, Service Name One, Service Name Three, Service Name Two, 
server4 | Control Name Three | Service Name 200, Service Name 50, Service Name One, Service Name Two, 
server5 | Control Name Three | Service Name Four, Service Name One,

更新2：

我忘记了cfoutput group在MySQL中还有一个更简单的替代方法：GROUP_CONCAT

<cfquery name="qry" datasource="MySQL5">
   SELECT n.Host, c.Name AS ControlName, GROUP_CONCAT(s.Name) AS ServiceNameList 
   FROM node n 
        LEFT JOIN control c ON c.controlID = n.controlID 
        LEFT JOIN service s ON FIND_IN_SET(s.serviceID, n.serviceId) 
   GROUP BY n.Host, c.Name
   ORDER BY n.host
</cfquery>