SQL查询多个表，有多个连接和列字段用逗号分隔的列表 [英] SQL query multiple tables, with multiple joins and column field with comma seperated list
问题描述
我有一个查询在那里我参加三个独立的表（节点，控制，服务）。
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
正如你可以看到，数据库中的表有一点正常化与 node.serviceID 列的除外。我衷心地全同意的 node.serviceID 应规范化和一到多创造了一个数据透视表。没有参数那里。不过，我不控制信息插入到数据库的脚本。我只能从表中读取和格式化数据的方式，我可以。
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.
所以，下面是SQL查询我写的工作，但在如预期的 node.serviceID 不 service.serviceID 加入很好。请注意，我不是我最终的查询使用SELECT *，我选择的节点表约20场和不想让查询更混乱。下面仅仅是一个例子。
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.
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
我要寻找的是：
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列表但不能同时在一起。
I have scoured stackoverflow.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.
这一次差点：使用的逗号分隔SQL 但并不完全。
This one came close: Using id that are comma seperated sql but not quite.
我已经试过CFML的各种方法与ListToArray（），并试图循环遍历他们一个指标，但没有将工作适合我。
I have tried various methods of CFML with ListToArray() and tried looping over them with an index but nothing would work for me.
我从抽丝数据的服务器是MySQL 5.1，我使用jQuery和ColdFusion（Railo 4.2）的组合对数据进行格式化。
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.
这是我第一次张贴在计算器，所以我道歉，如果真的有一个答案，我没有搜索足够长的时间，并会作出这个问题重复。
This is my first time posting on stackoverflow, 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 --------------------
我试着查询，并通过CFML利建议。
I tried the query and CFML suggested by Leigh.
所以，我得到如下：
server1的服务名称一，服务名称一，服务名称一，服务名称一，服务名称一，服务名称一，服务名称一，服务名称二，服务名称二，服务名称二，服务名称二，服务名称二，服务名称二，服务名称三，服务名称四，服务名称四，服务名称四，服务名称四，服务名称四，服务名称四，服务名称四
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
我不知道，在这一点上，如果仅仅是在SQL查询的CFML或改变的东西一点点。但是，它看起来有前途....思考????
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 .... Thoughts????
推荐答案
如果你真的不能修改表结构，可能是最好的你能做的就是旧列表黑客之一：
If you really cannot modify the table structure, probably the best you can do is one of the old list hacks:
SELECT n.Host，c.Name AS控件名称，s.Name AS服务名称
从节点n
LEFT JOIN控制C对c.controlID = n.controlID
LEFT JOIN服务S于FIND_IN_SET（s.serviceID，n.serviceId）
ORDER BY n.host，s.Name
;
使用 LIKE 来检测节点列表中的特定服务ID值presence
Use LIKE to detect the presence of a specific serviceID value within the node list
SELECT n.Host，c.Name AS控件名称，s.Name AS服务名称
从节点n
LEFT JOIN控制C对c.controlID = n.controlID
LEFT JOIN服务S于
CONCAT（'，'，n.serviceID，，）LIKE
CONCAT（'％'，s.serviceID，'％'）
ORDER BY n.host，s.Name
;
不过，因为你已经注意到，列真的应该归。虽然上述方法应小数据集工作，他们从名单的工作中的常见问题受到影响。既不方法非常指数友好的，并且作为一个结果，将不很好地扩展。此外，无论是执行字符串比较。所以丝毫差别可能会导致匹配失败。例如， 1,4 将匹配两个服务ID的，而 1，（空格）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.
基于注释更新：
在二读，我不知道上面的答案，你问了precise的问题，但它应该提供一个良好的基础与...
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
..
另外，如果你需要preserve逗号分隔值，一种可能性是使用＆LT; CFOUTPUT组=...＆GT; 的查询结果。由于结果是通过有序的主机首先，像下面的code。 注：对于组才能正常工作，其结果必须由主机下令，你必须使用多个 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>
结果应该是这样的：
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,
结果
更新2：
我忘了还有在MySQL中一个简单的替代CFOUTPUT组： 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>
这篇关于SQL查询多个表，有多个连接和列字段用逗号分隔的列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
