SQL查询多个表，有多个连接和用逗号分隔列表的列字段 [英] SQL query multiple tables, with multiple joins and column field with comma separated list
问题描述
我有一个查询，其中我加入了三个单独的表(节点、控制、服务).
下面是它们的列标题和示例数据.
NODE TABLE(包含超过 7000 行)节点ID |主机|服务ID |控制ID1 |服务器1 |1,2,3,4,9,50,200 |12 |服务器2 |2,3,4,9,200 |23 |服务器3 |1,2,3,4,9,50,200 |24 |服务器4 |1,2,50,200 |35 |服务器5 |1,4 |3控制表(包含大约 50 行)控制ID |姓名1 |控件名称一2 |控件名称二3 |控件名称三4 |控件名称四5 |控件名称五SERVICE TABLE(包含大约 3000 行)服务ID |姓名1 |服务名称一2 |服务名称二3 |服务名称三4 |服务名称四5 |服务名称五6 |服务名称六50 |服务名称 50200 |服务名称 200如您所见，除了 node.serviceID 列之外，数据库表都进行了一些规范化处理.我完全同意 node.serviceID 应该标准化并创建一个一对多的数据透视表.那里没有争论.但是，我不控制将信息插入数据库的脚本.我只能从表格中读取数据并尽可能地格式化数据.
所以，下面是我编写的 SQL 查询，它确实有效，但正如预期的那样，node.serviceID 不能很好地与 service.serviceID 结合.请注意，我没有在最终查询中使用 SELECT *，我从节点表中选择了大约 20 个字段，并且不想让查询变得更加混乱.下面只是一个例子.
SELECT *FROM 节点 ASLEFT JOIN control AS b ON a.controlID = b.controlidLEFT JOIN service AS c ON a.serviceID = c.serviceId由 a.host 订购上面的查询输出了类似的东西:
主机控制服务server1 控制名称一 1,2,3,4,9,50server2 控件名称三 1,2,9,50server3 控件名称 2 4server4 控件名称 4 1,2,3,4,9server5 控件名称 2 1,2,3,50server6 控件名称五 1,3,4,9,50我要找的是这个:
主机控制服务server1 控制名称一服务名称一，服务名称二，服务名称三，服务名称四，服务名称九，服务名称五十server2 控制名称三服务名称一，服务名称二，服务名称九，服务名称五十server3 控制名称二 服务名称四server4 控制名称四服务名称一，服务名称二，服务名称三，服务名称四，服务名称九我已经在 stackoverflow.com 上搜索过有类似问题的人，但我只能找到要么在 ID 和名称上加入多个表，要么有人扩展了 ID 列表，但不能同时扩展.
这个很接近:使用以逗号分隔的 sql 的 id 但不完全是.
我用 ListToArray() 尝试了各种 CFML 方法，并尝试用索引循环它们，但没有任何方法对我有用.
我从中获取数据的服务器是 MySQL 5.1，我使用 jQuery 和 ColdFusion (Railo 4.2) 的组合来格式化数据.
这是我第一次在stackoverflow上发帖，所以如果真的有答案，我很抱歉，我没有搜索足够长的时间，并且会重复这个问题.
----------------- 更新 --------------------
我尝试了 Leigh 建议的查询和 CFML.
所以，我得到以下信息:
server1 服务名称一，服务名称一，服务名称一，服务名称一，服务名称一，服务名称一，服务名称一，服务名称二，服务名称二，服务名称二，服务名称二，服务名称二、服务名称二、服务名称三、服务名称四、服务名称四、服务名称四、服务名称四、服务名称四、服务名称四、服务名称四
目前我不确定这是否只是 CFML 或 SQL 查询中的一些更改.但是，它看起来确实很有希望.
如果你真的不能修改表结构，那么你能做的最好的事情可能就是旧的列表技巧之一:
使用
JOIN和 FIND_IN_SET(value,commaSeparatedString)SELECT n.Host, c.Name AS ControlName, s.Name AS ServiceNameFROM 节点 nLEFT JOIN control c ON c.controlID = n.controlIDLEFT JOIN service s ON FIND_IN_SET(s.serviceID, n.serviceId)按 n.host, s.Name 排序;使用
LIKE来检测节点列表中是否存在特定的serviceID值SELECT n.Host, c.Name AS ControlName, s.Name AS ServiceNameFROM 节点 nLEFT JOIN control c ON c.controlID = n.controlIDLEFT JOIN 服务开启CONCAT(',', n.serviceID,',') LIKECONCAT('%,', s.serviceID,',%')按 n.host, s.Name 排序;
但是，正如您已经注意到的那样，列确实应该标准化.虽然上述方法应该适用于小数据集，但它们会遇到使用列表"时的常见问题.这两种方法都对索引不友好，因此无法很好地扩展.此外，两者都执行字符串比较.所以最细微的差异都可能导致匹配失败.例如，1,4 会匹配两个 serviceID，而 1,(space)4 或 1,4.0 只会匹配一个.>
根据评论更新:
在第二次阅读时，我不确定以上内容是否准确回答了您提出的问题，但它应该为与...一起工作提供良好的基础
如果您不再需要 CSV 列表，只需使用上述查询之一并像往常一样输出各个查询列.结果将是每行一个服务名称，即:
server1 |控件名称一 |服务名称 200服务器1 |控件名称一 |服务名称 50..否则，如果您需要保留逗号分隔值，一种可能性是在查询结果上使用 <cfoutput group="..">.由于结果首先按主机"排序，类似于下面的代码.注意:要使组"正常工作，结果必须按 Host 排序，并且必须使用多个 cfoutput 标签，如下所示.
#主持人# |#控制名称# |<cfoutput>#服务名称#，</cfoutput><br></cfoutput> 结果应该是这样的:
server1 |控件名称一 |服务名称200，服务名称50，服务名称四，服务名称一，服务名称三，服务名称二，服务器2 |控件名称二 |服务名称200，服务名称四，服务名称三，服务名称二，服务器3 |控件名称二 |服务名称200，服务名称50，服务名称四，服务名称一，服务名称三，服务名称二，服务器4 |控件名称三 |服务名称 200、服务名称 50、服务名称一、服务名称二、服务器5 |控件名称三 |服务名称四，服务名称一，更新 2:
我忘记了 MySQL 中 cfoutput group 的更简单替代方法:GROUP_CONCAT
SELECT n.Host, c.Name AS ControlName, GROUP_CONCAT(s.Name) AS ServiceNameListFROM 节点 nLEFT JOIN control c ON c.controlID = n.controlIDLEFT JOIN service s ON FIND_IN_SET(s.serviceID, n.serviceId)GROUP BY n.Host, c.Name按 n.host 排序</cfquery> 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.
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
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 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.
This one came close: Using id that are comma separated sql but not quite.
I have tried various methods of CFML with ListToArray() and tried looping over them with an index but nothing would work for me.
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 --------------------
I tried the query and CFML suggested by Leigh.
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
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.
If you really cannot modify the table structure, probably the best you can do is one of the old list hacks:
Use a
JOINwith FIND_IN_SET(value, 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 ;Use
LIKEto detect the presence of a specific serviceID value within the node listSELECT 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 ;
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.
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:
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 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:
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屋！
