将联接的数据添加到查询结果中 [英] Adding in joined data to a query result

问题描述

这可能已经在这里了，但是很难理解.基本上，我有一个带有CompanyID的主表，Companies.我必须连接到它的表-用户和承包商.一个公司可能有多个用户，也可能有多个承包商.但是用户和承包商之间没有任何关系.

This may already be here but it's understandably difficult to search for. Basically, I have a main table, Companies, with a CompanyID. I have to tables that join to it - Users, and Contractors. A company may have multiple users, it may have multiple contractors. But users and contractors have no relation to each other.

我想要的是列出公司的所有用户和所有承包商的列表，如下所示:

What I want is to have a list of all the users and all the contractors for a company, like this:

Company1    User1    Contractor1
Company1    User2    Contractor2
Company2    User3    Contractor1
Company2    User4    NULL
Company3    User5    Contractor2
Company3    NULL     Contractor3

等在上面，公司1有两个用户和两个承包商，公司2有两个用户和一个承包商，公司3有一个用户和两个承包商.

etc. In the above, Company1 has two users and two contractors, Company2 has two users and one contractor, Company3 has one user and two contractors.

我不知道是否有可能在纯单个SQL查询中重新创建.如果我使用的是PHP，那将很容易-我只需将其全部拉入数组并创建子数组.但是我正在尝试在Logi Info中执行此操作，尽管到目前为止我仍无法弄清楚如何在Logi的对象中执行此操作，但我希望也许可以直接在SQL中执行此操作.

I'm at a loss of if this is possible to recreate in a pure single SQL query. If I were using PHP, it would be easy - I would just pull it all into an array and create subarrays. But I'm attempting to do this in Logi Info, and while I've so far been unable to figure out how to do it in Logi's objects, I was hoping maybe I could just straight do it in the SQL.

这可能吗?加入通常会包含难以清除的重复项；例如，对于Company1，它将具有四行，即User1-Contractor1，User1-Contractor2，User2-Contractor1和User2-Contractor2.而且我无法想到一种分组方法，因为它将需要一个唯一的组合(已经存在)，或者它将消除错误的东西，例如让User1与两个不同的承包商重复，而完全省略User2.

Is this possible? Joins will typically include duplicates that are difficult to weed out; for example, for Company1, it would have four rows, User1-Contractor1, User1-Contractor2, User2-Contractor1, and User2-Contractor2. And I can't think of a method to group by since it would require a unique combination (which already exists) or it would remove the wrong thing, like having User1 repeated with two different contractors, and omitting User2 entirely.

推荐答案

WITH indexed_users AS (
  SELECT u.*, ROW_NUMBER() OVER ( PARTITION BY Company_ID ORDER BY Name ) AS idx
  FROM Users u
),
indexed_contractors AS (
  SELECT c.*, ROW_NUMBER() OVER ( PARTITION BY Company_ID ORDER BY Name ) AS idx
  FROM Contractors c
),
indexed_users_and_contractors AS (
  SELECT COALESCE( u.Company_ID, c.Company_ID ) AS Company_ID,
         u.Name AS UserName,
         c.Name AS ContractorName,
         COALESCE( u.idx, c.idx ) AS idx
  FROM   indexed_users u
         FULL OUTER JOIN
         indexed_contractors c
         ON ( u.Company_id = c.Company_ID
             AND u.idx = c.idx )
  ORDER BY 4
)
SELECT c.Name,
       i.UserName,
       i.ContractorName
FROM   Companies c
       LEFT OUTER JOIN
       indexed_users_and_contractors i
       ON ( c.Company_ID = i.Company_ID )
ORDER BY c.Name, i.idx

SQL小提琴 :

SQL Fiddle:

|     NAME | USERNAME | CONTRACTORNAME |
|----------|----------|----------------|
| Company1 |    User1 |    Contractor1 |
| Company1 |    User2 |    Contractor2 |
| Company2 |    User3 |    Contractor1 |
| Company2 |    User4 |         (null) |
| Company3 |    User5 |    Contractor2 |
| Company3 |   (null) |    Contractor3 |

这篇关于将联接的数据添加到查询结果中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！