展平/导出的平铺关系数据

Say we have two tables in an MS Access db:

假设我们在MS Access数据库中有两个表：

Service Users:

服务用户：

| ID | Name | Other details... |
| 1  | Joe  | Blah...          |
| 2  | Fred | Qwerty...        |
| 3  | Bob  | Something else...|

Teams providing services:

提供服务的团队：

| ID | TeamID | UserID |
| 1  | T1     | 1      |
| 2  | T2     | 1      |
| 3  | T2     | 2      |
| 4  | T3     | 2      |
| 5  | T3     | 3      |

I need to produce a summary query that produces a single row for each user, with the first several teams (by TeamID) assigned sitting in separate columns. Like:

我需要生成一个摘要查询，为每个用户生成一行，前几个团队（由TeamID）分配坐在不同的列中。喜欢：

Query:

查询：

| UserID | Name | Team1 | Team2 |
| 1      | Joe  | T1    | T2    |
| 2      | Fred | T2    | T3    |
| 3      | Bob  | T3    | Null  |

I can get the Team1 column using max() from a sub select query, but I'm having a complete mental block on how to achieve Team2, Team3, etc. (Yes, I know that if there are more teams assigned to a user than I create columns the query will lose that information: that isn't a concern).

我可以使用子选择查询中的max（）来获取Team1列，但是我对如何实现Team2，Team3等有一个完整的心理障碍（是的，我知道如果有更多的团队分配给用户比我创建列，查询将丢失该信息：这不是一个问题）。

Edit: To clarify, the number of columns in the query will be fixed (in the actual query, there will always be 7). If there are less teams than columns the additional columns should be Null (as in example). If there are more teams than columns, only the first 7 teams will be shown in this summary.

编辑：为了澄清，查询中的列数将被修复（在实际查询中，将始终为7）。如果团队数少于列，则其他列应为Null（如示例所示）。如果团队数量多于列数，则此摘要中仅显示前7个团队。

Edit 2 - Possible solution which doesn't work...:

编辑2 - 可能的解决方案不起作用......：

I tried...

我试过了...

SELECT UserTable.ID As UID, UserTable.Name, 
(SELECT TOP 1 TeamID FROM TeamTable WHERE UserTable.ID = TeamTable.UserID
ORDER BY TeamID) As Team1
FROM UserTable

... which works fine. Unfortunately...

......工作正常。不幸...

SELECT UserTable.ID As UID, UserTable.Name, 
(SELECT TOP 1 TeamID FROM TeamTable WHERE UserTable.ID = TeamTable.UserID
ORDER BY TeamID) As Team1,
(SELECT TOP 1 TeamID FROM TeamTable WHERE UserTable.ID = TeamTable.UserID
AND TeamID <> Team1 ORDER BY TeamID) As Team2
FROM UserTable

... throws up a parameter box for Team1. An ideas on how to skip the first/second/etc... values from an jet query?

...为Team1抛出一个参数框。关于如何跳过jet查询的第一个/第二个/ etc ...值的想法？

4 个解决方案

#1

First, you need a query to assign a number from 1 to N to the teams associated to a user:

首先，您需要一个查询来为与用户关联的团队分配从1到N的数字：

SELECT UserID, TeamID, 
       (SELECT count(*) FROM TeamTable t2 WHERE t2.TeamID <= TeamTable.TeamID and t2.UserID=TeamTable.UserID) AS position 
FROM TeamTable 
ORDER BY TeamID

Let's call this query TeamList. Now, you can use this query in the main query, by calling it 7 times, each time filtering a different position:

我们将此查询称为TeamList。现在，您可以在主查询中使用此查询，通过调用它7次，每次过滤不同的位置：

SELECT UserTable.ID As UID, UserTable.Name, 
   (SELECT TeamID FROM TeamList WHERE UserTable.ID = TeamList.UserID AND position=1) As Team1,
   (SELECT TeamID FROM TeamList WHERE UserTable.ID = TeamList.UserID AND position=2) As Team2,
   [...]
FROM UserTable

You could assemble all this in a single query, but it's more practical to define the TeamList query and calling it multiple times. Also note that this way the numbering is based on the order of TeamID. You can choose another order by changing the TeamList query, but the field you choose must have different unique values for each Team (or the <= comparison will generate wrong numbers).

您可以在单个查询中组合所有这些，但定义TeamList查询并多次调用它更为实际。另请注意，这种编号基于TeamID的顺序。您可以通过更改TeamList查询来选择其他订单，但您选择的字段必须为每个团队具有不同的唯一值（或<=比较将生成错误的数字）。

Obviously, with a big number of rows performance would be terrible, but for a few hundreds it could be acceptable. You have to try.

显然，有大量的行表现会很糟糕，但是几百行可能是可以接受的。你得试试。

#2

A crosstab query should suit.

交叉表查询应该适合。

Query 1: Called TeamUser

查询1：称为TeamUser

SELECT Teams.UserID, ServiceUsers.SName, Teams.TeamID
FROM ServiceUsers 
INNER JOIN Teams ON ServiceUsers.ID = Teams.UserID;

Crosstab

交叉表

TRANSFORM First(TeamUser.TeamID) AS FirstOfTeamID
SELECT TeamUser.UserID, TeamUser.SName
FROM TeamUser
GROUP BY TeamUser.UserID, TeamUser.SName
PIVOT TeamUser.TeamID;

EDIT in response to comments

编辑回应评论

It should be possible to combine the two queries and to use a union query to reduce the number of entries.

应该可以组合这两个查询并使用联合查询来减少条目数。

TRANSFORM First(t.ATeamID) AS FirstOfATeamID
SELECT t.UserID, s.SName
FROM (SELECT Teams.UserID, First(Teams.ID) AS FirstOfID, 
             First(Teams.TeamID) AS ATeamID, "1st" As TeamCount
      FROM Teams
      GROUP BY Teams.UserID
      UNION 
      SELECT Teams.UserID, First(Teams.ID) AS FirstOfID, 
             First(Teams.TeamID) AS ATeamID, "2nd" As TeamCount
      FROM Teams
      WHERE ID Not In (SELECT First(Teams.ID) 
                       FROM Teams GROUP BY Teams.UserID)
      GROUP BY Teams.UserID) t
INNER JOIN ServiceUsers s ON t.UserID = s.ID
GROUP BY t.UserID, s.SName
PIVOT t.TeamCount

#3

I'm not an MS-Access expert, but isn't there some way to skip rows in your sub queries? So for Team1 you skip 0 rows, for Team2 you skip 1 row, etc. Each time you use MAX() to select the maximum value from the rows in your sub query. Since there is one less row each time, you get another maximum value each time (if you sort your sub query result of course).

我不是MS-Access专家，但是没有办法在子查询中跳过行？因此对于Team1，您跳过0行，对于Team2，您跳过1行，等等。每次使用MAX（）从子查询中的行中选择最大值。由于每次只有一行，每次都会得到另一个最大值（如果你对子查询结果进行排序）。

The general idea is that each of your sub queries (for Team1, Team2, ...) returns one less value such that your MAX() function returns another value each time.

一般的想法是，每个子查询（对于Team1，Team2，...）返回的值较少，这样MAX（）函数每次都返回另一个值。

Taking a look at what is possible with MS-Access SQL, you can only use TOP. But then you have to know how many rows your sub query has. That's impossible I think. Suppose you do know this and a user is a member of 3 teams, your sub query per team includes SELECT TOP(3) ..., SELECT TOP(2) ..., SELECT TOP(1)....

看一下MS-Access SQL的可能性，你只能使用TOP。但是你必须知道你的子查询有多少行。我认为这是不可能的。假设您确实知道这一点并且用户是3个团队的成员，则每个团队的子查询包括SELECT TOP（3）...，SELECT TOP（2）...，SELECT TOP（1）....

#4

Just put Team1 as a sub query that way Team1 is populated before you reference it when getting Team2

只需将Team1作为子查询，以便在获取Team2时引用Team1之前填充它

SELECT UserTable.ID As UID,UserTable.Name,T1.Team1,
(SELECT TOP 1 TeamID FROM TeamTable WHERE UserTable.ID = TeamTable.UserID AND TeamID <> T1.Team1 ORDER BY TeamID) As Team2 
FROM UserTable 
LEFT OUTER JOIN
    (
        SELECT UserTable.ID As UID,  
           (SELECT TOP 1 TeamID FROM TeamTable WHERE UserTable.ID = TeamTable.UserID 
        ORDER BY TeamID) As Team1, 
        FROM UserTable 
    )T1 ON UserTable.ID=T1.UID

This will also work with a CTE:

这也适用于CTE：

;With CTE_Team1 As (
        SELECT UserTable.ID As UID,  
           (SELECT TOP 1 TeamID FROM TeamTable WHERE UserTable.ID = TeamTable.UserID ORDER BY TeamID) As Team1, 
        FROM UserTable )
SELECT UserTable.ID As UID,UserTable.Name,T1.Team1,
   (SELECT TOP 1 TeamID FROM TeamTable WHERE UserTable.ID = TeamTable.UserID AND TeamID <> T1.Team1 ORDER BY TeamID) As Team2 
FROM UserTable 
LEFT OUTER JOIN
CTE_Team1 T1 ON UserTable.ID=T1.UID

#1