MS Access匹配结构表中的行组 [英] MS Access Match groups of rows in structure table
问题描述
我有一个有趣的问题要克服，那就是桌子.我已经从完整的零件数据库中存储了一组简化的数据(我们不能随意修改，也不执行所需的查询).我需要找到工程师在哪里创建了完全克隆的程序集，包括它们在空间中的位置(xyz位置和xyz旋转)和区域.我基本上需要列出它们以便在主系统中将其删除.该表显示了顶层程序集，它是第一级子程序.
I have an interesting problem to overcome with a table. I have stored a simplified set of data from our full parts database (which we are not at liberty to hack around in, and does not perform the required queries). I need to find where engineers have created completely cloned assemblies including their position in space (xyz position and xyz rotation) and area. I basically need to list them out for removal in the main system. The table shows the top level assembly, and it's first level children.
即
组装_1
|-Part_1(0,0,0 0,0,0)
|-Part_2(0,0,0 0,0,0)
|-Part_3(0,0,0 0,0,0)
Assembly_1
|- Part_1 (0,0,0 0,0,0)
|- Part_2 (0,0,0 0,0,0)
|- Part_3 (0,0,0 0,0,0)
装配体_2
|-Part_1(0,0,0 0,0,0)
|-Part_2(0,0,0 0,0,0)
|-Part_3(0,0,0 0,0,0)
Assembly_2
|- Part_1 (0,0,0 0,0,0)
|- Part_2 (0,0,0 0,0,0)
|- Part_3 (0,0,0 0,0,0)
Assembly_3
|-Part_1(500,0,0 0,0,0)
|-Part_2(500,0,0 0,0,0)
|-Part_3(500,0,0 0,0,0)
Assembly_3
|- Part_1 (500,0,0 0,0,0)
|- Part_2 (500,0,0 0,0,0)
|- Part_3 (500,0,0 0,0,0)
Assembly_4
|-Part_4(0,0,0 0,0,0)
|-Part_5(0,0,0 0,0,0)
|-Part_6(0,0,0 0,0,0)
在一个表中存储为:
Assembly_4
|- Part_4 (0,0,0 0,0,0)
|- Part_5 (0,0,0 0,0,0)
|- Part_6 (0,0,0 0,0,0)
Stored in one table as:
Parent ID | Child ID | Assembly Level | Area | Position
Assembly_1 | Assembly_1 | 0 | 0001 | 0,0,0 0,0,0
Assembly_1 | Part_1 | 1 | 0001 | 0,0,0 0,0,0
Assembly_1 | Part_2 | 1 | 0001 | 0,0,0 0,0,0
Assembly_1 | Part_3 | 1 | 0001 | 0,0,0 0,0,0
Assembly_2 | Assembly_2 | 0 | 0001 | 0,0,0 0,0,0
Assembly_2 | Part_1 | 1 | 0001 | 0,0,0 0,0,0
Assembly_2 | Part_2 | 1 | 0001 | 0,0,0 0,0,0
Assembly_2 | Part_3 | 1 | 0001 | 0,0,0 0,0,0
Assembly_3 | Assembly_3 | 0 | 0001 | 0,0,0 0,0,0
Assembly_3 | Part_1 | 1 | 0001 | 500,0,0 0,0,0
Assembly_3 | Part_2 | 1 | 0001 | 500,0,0 0,0,0
Assembly_3 | Part_3 | 1 | 0001 | 500,0,0 0,0,0
Assembly_4 | Assembly_4 | 0 | 0002 | 0,0,0 0,0,0
Assembly_4 | Part_4 | 1 | 0002 | 0,0,0 0,0,0
Assembly_4 | Part_5 | 1 | 0002 | 0,0,0 0,0,0
Assembly_4 | Part_6 | 1 | 0002 | 0,0,0 0,0,0在该示例中，我希望装配1&要显示的是图2，但不是装配3，因为虽然它使用相同的零件，但是x，y，z的位置不同，而不是装配4，因为它使用不同的零件并且位于不同的区域.
In the example I would expect Assemblies 1 & 2 to be displayed, but not Assembly 3, as although it uses the same parts, it's x,y,z position is different, and not Assembly 4 as it uses different parts and is in a different area.
我想提取的东西是这样的:
What I would like is to extract is something like:
Assembly | Cloned Assembly | Area
Assembly_1 | Assembly_2 | 0001
我不介意您是否在Assembly 1& 2个交换侧.甚至比手动搜索整个结构还要好！
And I don't mind if you end up with additional lines where Assembly 1 & 2 swap sides. Even this is far more palatable than searching the whole structure manually!
我怀疑目前这很可能最终成为VBA解决方案(主要是因为我对VBA的了解远远超过我对SQL的了解！)，因为我什至无法在SQL中做到这一点，但是这很耗时(我认为，要在一个产品中将近400个区域的完整数据集中进行搜索和比较，则有超过20000个订单项，而且每个产品中大约有30多个产品都将重复此操作.
I suspect at the moment this might well end up being a VBA solution (chiefly as I understand VBA far more than I understand SQL!) as I can't even get close to this in SQL, however that is time consuming (I believe there is in excess of 20000 line items to search and compare in the full dataset with nearly 400 areas in a product), and this will be repeated per product of which there are about 30+.
我尝试了以下示例来提取重复项(实际上在提取0级装配被意外重复到产品中的过程中发挥了出色的作用)，但这是一个单行与另一行匹配的地方，而不是一组的地方的几行与另一组匹配.
I have tried following examples to extract duplicates (which has in fact worked brilliantly to extract where an assembly at level 0 have been accidentally repeated into the product), but this is where one single line matches another line, not where a group of several lines match another group.
任何帮助/指导，甚至只是确认使用VBA都将不胜感激！
Any help / guidance or even just the affirmation of use of VBA would be greatly appreciated!
更新的SQL:
SELECT DISTINCT
a1.GROUP,
a1.SUB_GROUP,
a1.ParentID AS Assembly,
(SELECT DISTINCT AssemblyTable.NAME
FROM AssemblyTable
WHERE AssemblyTable.ChildID=a1.ParentID) AS NAME,
(SELECT DISTINCT AssemblyTable.OWNER
FROM AssemblyTable
WHERE AssemblyTable.ChildID=a1.ParentID) AS OWNER,
a1.Area,
(SELECT DISTINCT AssemblyTable.ITEM_CREATION
FROM AssemblyTable
WHERE AssemblyTable.ChildID=a1.ParentID) AS ITEM_CREATION,
a2.ParentID AS CLONED_ASSEMBLY,
(SELECT DISTINCT AssemblyTable.NAME
FROM AssemblyTable
WHERE AssemblyTable.ChildID=a2.ParentID) AS CLONE_NAME,
(SELECT DISTINCT AssemblyTable.OWNER
FROM AssemblyTable
WHERE AssemblyTable.ChildID=a2.ParentID) AS CLONE_OWNER,
(SELECT DISTINCT AssemblyTable.ITEM_CREATION
FROM AssemblyTable
WHERE AssemblyTable.ChildID=a2.ParentID) AS CLONE_CREATION
FROM (SELECT * FROM AssemblyTable ORDER BY AssemblyTable.ITEM_CREATION ASC) AS a1
INNER JOIN (SELECT * FROM AssemblyTable ORDER BY AssemblyTable.ITEM_CREATION ASC) AS a2
ON (a1.ParentID < a2.ParentID)
AND (a1.Area = a2.Area)
AND (a1.[Position] = a2.[Position])
AND (a1.ChildID=a2.ChildID)
WHERE a1.ParentID<>a2.ParentID
ORDER BY a1.GROUP, a1.SUB_GROUP, a1.Area, a1.ParentID;
表格现在是:
Parent ID | Child ID | Name | OWNER | Assembly Level | Area | Position | ITEM_CREATION
Assembly_1 | Assembly_1 | Assembly_1_Name | User_1 | 0 | 0001 | 0,0,0 0,0,0 | 01-01-2016
Assembly_1 | Part_1 | Part_1_Name | User_1 | 1 | 0001 | 0,0,0 0,0,0 | 01-01-2016
Assembly_1 | Part_2 | Part_2_Name | User_1 | 1 | 0001 | 0,0,0 0,0,0 | 01-01-2016
Assembly_1 | Part_3 | Part_3_Name | User_1 | 1 | 0001 | 0,0,0 0,0,0 | 01-01-2016
Assembly_2 | Assembly_2 | Assembly_2_Name | User_2 | 0 | 0001 | 0,0,0 0,0,0 | 01-01-2017
Assembly_2 | Part_1 | Part_1_Name | User_2 | 1 | 0001 | 0,0,0 0,0,0 | 01-01-2017
Assembly_2 | Part_2 | Part_2_Name | User_2 | 1 | 0001 | 0,0,0 0,0,0 | 01-01-2017
Assembly_2 | Part_3 | Part_3_Name | User_2 | 1 | 0001 | 0,0,0 0,0,0 | 01-01-2017
Assembly_3 | Assembly_3 | Assembly_3_Name | User_3 | 0 | 0001 | 0,0,0 0,0,0 | 01-01-2016
Assembly_3 | Part_1 | Part_1_Name | User_3 | 1 | 0001 | 500,0,0 0,0,0 | 01-01-2016
Assembly_3 | Part_2 | Part_2_Name | User_3 | 1 | 0001 | 500,0,0 0,0,0 | 01-01-2016
Assembly_3 | Part_3 | Part_3_Name | User_3 | 1 | 0001 | 500,0,0 0,0,0 | 01-01-2016
Assembly_4 | Assembly_4 | Assembly_4_Name | User_4 | 0 | 0002 | 0,0,0 0,0,0 | 01-01-2016
Assembly_4 | Part_4 | Part_4_Name | User_4 | 1 | 0002 | 0,0,0 0,0,0 | 01-01-2016
Assembly_4 | Part_5 | Part_5_Name | User_4 | 1 | 0002 | 0,0,0 0,0,0 | 01-01-2016
Assembly_4 | Part_6 | Part_6_Name | User_4 | 1 | 0002 | 0,0,0 0,0,0 | 01-01-2016我偶尔仍然看到:
Assembly | Cloned Assembly | Area
Assembly_2 | Assembly_1 | 0001
推荐答案
考虑一个 self join ，您可以在其中将同一张表连接到自身.在所需的匹配列上建立连接条件，然后过滤不同的 ParentID .为避免反向重复，请使用特殊的ON子句大于/小于平等:
Consider a self join where you join the same table to itself. Make the join criteria on the needed matched columns and then filter for different ParentID. And to avoid the reverse duplicates use a special ON clause with greater/less than equality:
SELECT DISTINCT a1.ParentID AS Assembly, a2.ParentID AS ClonedAssembly, a1.Area
FROM AssemblyTable AS a1
INNER JOIN AssemblyTable AS a2
ON (a1.ParentID < a2.ParentID) AND (a1.Area = a2.Area)
AND (a1.[Position] = a2.[Position]) AND (a1.ChildID = a2.ChildID)
WHERE a1.ParentID <> a2.ParentID;
-- Assembly Cloned_Assembly Area
-- Assembly_1 Assembly_2 0001
这篇关于MS Access匹配结构表中的行组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
