SQL查询多个表，具有多个连接和列字段，以逗号分隔列表

I have a query where I join three separate tables (node, control, service).

我有一个查询，我加入三个单独的表（节点，控制，服务）。

Below is their column headings and sample data.

下面是他们的列标题和样本数据。

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

As you can see, the database tables have a bit of normalization with the exception of the node.serviceID column. I whole heartily agree that node.serviceID should be normalized and a pivot table of one-to-many created. No argument there. However, I do not control the scripts that insert the information into the database. I can only read from the tables and format the data how I can.

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

So, below is the SQL query I wrote that does work but the, as expected, node.serviceID does not join well with service.serviceID. Please note that I am not using a SELECT * in my final query, I select about 20 fields from the node table and do not want to make the query more confusing. Below is just an example.

因此，下面是我编写的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

The query above spits out something similar:

上面的查询吐出类似的东西：

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

What I am looking for is this:

我要找的是这个：

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

I have scoured *.com for someone with an issue like this but I can only find either joining multiple tables on ID and name OR someone expanding a list of IDs but not both together.

我已经为有这样一个问题的人搜索了*.com，但我只能找到在ID和名称上加入多个表，或者有人扩展ID列表但不能同时扩展。

This one came close: Using id that are comma separated sql but not quite.

这个接近：使用逗号分隔的SQL但不完全的id。

I have tried various methods of CFML with ListToArray() and tried looping over them with an index but nothing would work for me.

我已经尝试了各种带有ListToArray（）的CFML方法，并尝试使用索引对它们进行循环，但没有什么能对我有用。

The server I snag the data from is MySQL 5.1 and I am using a combination of jQuery and ColdFusion (Railo 4.2) to format the data.

我抓住数据的服务器是MySQL 5.1，我使用jQuery和ColdFusion（Railo 4.2）的组合来格式化数据。

This is my first time posting on *, so my apologies if there really is an answer to this, I did not search long enough, and would make this question a duplicate.

这是我第一次在*上发帖，所以我很抱歉，如果确实有这个答案，我搜索的时间不长，并且会使这个问题重复。

----------------- UPDATE --------------------

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

I tried the query and CFML suggested by Leigh.

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

So, I get the following:

所以，我得到以下内容：

server1 Service Name One , Service Name One , Service Name One , Service Name One , Service Name One , Service Name One , Service Name One , Service Name Two , Service Name Two , Service Name Two , Service Name Two , Service Name Two , Service Name Two , Service Name Three , Service Name Four , Service Name Four , Service Name Four , Service Name Four , Service Name Four , Service Name Four , Service Name Four

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

I am not sure, at this point, if that is just a little bit of change with the CFML or something in the SQL query. But, it does look promising.

我不确定，在这一点上，如果只是对CFML或SQL查询中的某些内容进行了一些改动。但是，它确实看起来很有希望。

1 个解决方案

#1

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

如果你真的无法修改表结构，那么你可以做的最好的就是旧的列表黑客之一：

Use a JOIN with FIND_IN_SET(value, commaSeparatedString)

使用与FIND_IN_SET的JOIN（值，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 ;

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。主持人，s。名称;
Use LIKE to detect the presence of a specific serviceID value within the node list

使用LIKE检测节点列表中是否存在特定的serviceID值

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

SQLFiddle

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.

但是，正如您已经注意到的那样，列确实应该正常化。虽然上述方法适用于小型数据集，但它们仍然存在使用“列表”的常见问题。这两种方法都不是非常友好的索引，因此不能很好地扩展。此外，两者都执行字符串比较。因此，最微小的差异可能导致匹配失败。例如，1,4将匹配两个serviceID，而1，（space）4或1,4.0只匹配一个。

Update based on comments:

根据评论更新：

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

在第二次阅读时，我不确定上述答案是否是您要问的确切问题，但它应该提供一个良好的基础来...

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:

如果您不再需要CSV列表，只需使用上述查询之一并像往常一样输出各个查询列。结果将是每行一个服务名称，即：

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

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标记，如下所示。

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

The result should look like this:

结果应如下所示：

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,

Update 2:

更新2：

I forgot there is a simpler alternative to cfoutput group in MySQL: GROUP_CONCAT

我忘了在MySQL中有一个比cfoutput组更简单的替代方法：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>

#1