查找分组值，然后串联行 [英] Find grouped values and then concatenate rows

问题描述

我无法写一个查询或查询，将得到我想要的结果。

I am having trouble writing a query or queries that will get the results that I want.

基本上，我试图找到独特的公司。问题是，一些企业自己在同一地址经营多种业务。我想来连接这些值，而不是重复1家公司的业绩在2个地址工作。

Basically, I am trying to find unique "companies." The problem is that some companies own multiple businesses operating at the same address. I'd like to concatenate those values, while not duplicating the results of 1 company operating at 2 addresses.

样本数据：

Company  Address
A        1
A        2
B        1
C        3

因此​​，我们有独特的公司：

A
B
C

和地址

1
2
3

我想获得两个记录的结果：

I'd like to get two records for the results:

A, B
C

（因此理想情况下，在第一个表，其结果为A-1，A-2和B-1可能都具有指向同一自动编号以不同的字段。）

(So ideally, in the first table, the results for A-1, A-2, and B-1 would all have point to the same autonumber in a different field.)

推荐答案

要澄清：

• 在一个企业可以有多个地址
• 在任何一个企业中的商业集团（即公司）的股份与任何其他的业务地址组
• 可以拥有多个商业企业集团
• 在每一个业务仅与一个业务组（推论第二个点）
相关

• A business can have multiple addresses
• Any business in a business group (AKA company) shares an address with any other business in the group
• A business group can own multiple businesses
• Each business is only associated with one business group (corollary to second point)

SqlFiddle样本数据

我们可以通过第一（排名不分先后最小的名称）业务组中参考各个业务集团。让我们把这个在关键业务。之后，我们已经确定了关键业务的每个业务，我们可以通过关键业务组，得到的结果。

We can refer to each business group by the first (smallest name in alphabetical order) business in the group. Let's call this the key business. After we've identified the key business for each business, we can group by the key business and get the results.

为了获得关键业务：

• 生成对商家其中两个企业都在同一组中，基于任何共享地址的列表。该清单应排除以下（见下一点为什么）：

• Generate a list of pairs of businesses where both businesses are in the same group, based on any shared address. This list should exclude the following (see next point for why):

1. A - ＆GT;乙，当我们有 B - ＆GT; A
2. A - ＆GT; A

1. A -> B, when we have B -> A
2. A -> A

的对的左侧应是唯一的：每个业务应出现在一对不超过一次的左侧，如果在所有

The left side of the pairs should be unique: each business should appear on the left side of the pair no more than once, if at all.

对于每一个企业，按照从一个对到下一个，直到合适的企业从来都不是左经营任何其他对。这是关键的业务。

For each business, follow the pairs from one to the next until the right business is never the left business in any other pair. That is the key business.

这是第一点的排除的原因。如果我们有两个 A - ＆GT;乙和 B - ＆GT; A ，我们会得到一个永无止境的循环。同样适用于 A - ＆GT; A 。

That is the reason for the exclusions in the first point. If we have both A -> B and B -> A, we'll get to a never-ending loop. Same goes for A -> A.

第一部分可以在纯SQL直截了当地做：

The first part can be done straightforwardly in pure SQL:

SELECT Businesses.Business AS Business2, MIN(Businesses_1.Business) AS Business1
FROM Businesses
INNER JOIN Businesses AS Businesses_1 ON Businesses.Address = Businesses_1.Address
WHERE Businesses.Business > Businesses_1.Business
GROUP BY Businesses.Business

SqlFiddle

（其他RDBMS支持递归加盟，所以第二部分我们可以先源数据和直接数据库保持连接无限期，直到最后的业务1 是 NULL 的See这里。AFAIK MS Access不支持这种在SQL，所以我们必须要做到在VBA）

(Other RDBMS support recursive joining, so for the second part we could start with the source data and direct the database to keep joining indefinitely until the final Business1 is NULL. See here. AFAIK MS Access doesn't support this in SQL, so we'll have to do it in VBA.)

我建议使用的Scripting.Dictionary 用于这一目的。你需要添加一个引用为 Microsoft脚本运行时（工具 - >引用... ）

I suggest using Scripting.Dictionary for this purpose. You'll need to add a reference to Microsoft Scripting Runtime (Tools -> References...).

'Create a Dictionary to hold the pairs
Dim pairs As New Scripting.Dictionary

'Load the pairs using the above SQL
Dim sql = _
    "SELECT Businesses.Business AS Business2, MIN(Businesses_1.Business) AS Business1 " & _
    "FROM Businesses " & _
    "INNER JOIN Businesses AS Businesses_1 ON Businesses.Address = Businesses_1.Address " & _
    "WHERE Businesses.Business > Businesses_1.Business " & _
    "GROUP BY Businesses.Business"
Dim rsPairs = CurrentDb.OpenRecordset(sql, dbOpenForwardOnly)
Do Until rsPairs.EOF
    pairs(rsPairs!Business2) = rsPairs!Business1
Loop
rsPairs.Close
Set rsPairs = Nothing

'Create a Dictionary to hold the groupings
Dim groupings As New Scripting.Dictionary

sql = _
    "SELECT DISTINCT Business " & _
    "FROM Businesses " & _
    "ORDER BY Business"        
Dim rsBusinesses As DAO.Recordset
Set rsBusinesses = CurrentDb.OpenRecordSet(sql, dbOpenForwardOnly)
Do Until rsBusinesses.EOF
    Dim business As String
    business = rsBusinesses!Business
    If Not pairs.Exists(business) Then
        Dim col As New Collection
        col.Add(business)
        groupings(business) = col
    Else
        'Find the group's key business
        Dim prevBusiness As String
        prevBusiness = business
        Do While pairs.Exists(prevBusiness)
            prevBusiness = pairs(prevBusiness)
        Loop
        groupings(prevBusiness).Add(business)
    End If
Loop

最后字典将是这样的：

The final Dictionary will look like this:

Key    Collection
A      (A, B, W, X)
C      (C)
D      (D, E, F, G)

和可以如下迭代：

Dim key As Variant, item As Variant
For Each key In groupings
    Debug.Print "Grouping " & key
    For Each item In groupings(key)
        Debug.Print "Business - " & item
    Next
Next

这篇关于查找分组值，然后串联行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！