如果您确实无法修改表结构，则可能***的方法就是使用旧列​​表黑客之一:

If you really cannot modify the table structure, probably the best you can do is one of the old list hacks:

• 将JOIN与 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 ;

  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检测节点列表中是否存在特定的serviceID值

  Use LIKE to detect the presence of a specific serviceID value within the node list

  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 ;

  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将仅匹配一个.

  However, as you already noted that column really should be normalized. While the methods above should work for small data sets, they suffer from the usual problems of working with "lists". Neither method is very index friendly, and as a result, will not scale well. Also, both perform string comparisons. So the slightest difference may cause the matching to fail. For example, 1,4 would match two serviceID's, whereas 1,(space)4 or 1,4.0 would match only one.

  根据评论进行更新:

  二读时，我不确定以上内容是否能回答您所要提出的确切问题，但它应该为使用...提供良好的基础

  On second read, I am not sure the above answers the precise question you are asking, but it should provide a good basis to work with ...

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

  If you no longer want a CSV list, just use one of the queries above and output the individual query columns as usual. The result will be one service name per row, ie:

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

  否则，如果需要保留逗号分隔的值，则一种可能性是在查询结果上使用<cfoutput group="..">.由于结果首先由主机"排序，因此类似于下面的代码. 注意:为了使组"正常工作，结果必须按Host排序，并且必须使用多个cfoutput标记，如下所示.

  Otherwise, if you need to preserve the comma separated values, one possibility is to use a <cfoutput group=".."> on the query results. Since the results are ordered by "Host" first, something like the code below. NB: For "group" to work properly, the results must be ordered by Host and you must use multiple cfoutput tags as shown below.

   <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:

  我忘记了MySQL中cfoutput group的替代方法更简单: GROUP_CONCAT

  I forgot there is a simpler alternative to cfoutput group in 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>