用于汇总/导出的扁平化关系数据 [英] Flatten relational data for summary/export

问题描述

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

Say we have two tables in an MS Access db:

服务用户:

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

提供服务的团队:

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

我需要生成一个汇总查询，为每个用户生成一行，将前几个团队(由 TeamID)分配到不同的列中.喜欢:

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:

查询:

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

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

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

澄清一下，查询中的列数将是固定的(在实际查询中，总是有 7 个).如果团队少于列，则附加列应为 Null(如示例所示).如果团队数量多于列数，则此摘要中将仅显示前 7 个团队.

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.

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

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

我试过了...

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

... 为 Team1 抛出一个参数框.关于如何从喷气式查询中跳过第一个/第二个/等...值的想法?

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

推荐答案

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

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

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

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

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:

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 

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

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

显然，大量行的性能会很糟糕，但对于几百行来说，它是可以接受的.你必须尝试.

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

这篇关于用于汇总/导出的扁平化关系数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！