如果你真的不能修改表结构，你能做的***的可能是旧的列表黑客之一:

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

• 使用 JOIN 和 FIND_IN_SET(value, commaSeparatedString)

SELECT n.Host, c.Name AS ControlName, s.Name AS ServiceName从节点 nLEFT JOIN control c ON c.controlID = n.controlIDLEFT 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从节点 nLEFT JOIN control c ON c.controlID = n.controlIDLEFT JOIN 服务开启CONCAT(',', n.serviceID,',') LIKECONCAT('%,', 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
   ..

否则，如果您需要保留逗号分隔值，一种可能性是在查询结果上使用 .由于结果首先按主机"排序，因此类似于下面的代码.注意:要使组"正常工作，结果必须按 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>