您的位置: 首页  >  技术问答  >  SQL查询多个表，具有多个连接和带有逗号分隔列表的列字段

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

分类: 技术问答 • 2022-04-14 15:07:52
问题描述：

我有一个查询，其中我连接了三个单独的表(节点，控件，服务).

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列表但又不能同时扩展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的ID 但不太完全.

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

我用ListToArray()尝试了CFML的各种方法，并尝试用索引循环遍历它们，但是对我来说什么都行不通.

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.

这是我第一次在stackoverflow上发帖，所以我很抱歉，如果真的有答案，我没有搜索足够长的时间，因此这个问题会重复出现.

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

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

I tried the query and CFML suggested by Leigh.

因此，我得到以下信息:

So, I get the following:

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

目前，我不确定CFML或SQL查询中的内容是否只是一点点更改.但是，它看起来确实很有前途.

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:

  • 将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>

相关推荐