mysql - SQL:无法理解如何从我的表中进行选择

Question

我需要数据提取方面的帮助。我是一名 sql 菜鸟，我认为我的数据设计技能存在严重问题。数据库系统是运行在Linux上的MYSQL。

表 A 的结构如下:

TYPE    SUBTYPE ID
-------------------
xyz     aaa     0001
xyz     aab     0001
xyz     aac     0001
xyz     aad     0001
xyz     aaa     0002
xyz     aaj     0002
xyz     aac     0002
xyz     aav     0002

表B是:

TYPE1   SUBTYPE1    TYPE2   SUBTYPE2    
-------------------------------------
xyz     aaa         xyz     aab
xyz     aac         xyz     aad

查看整个表 A，我需要提取类型和子类型都作为单个表 B 行中的列出现的所有行。当然，这个条件永远不会满足，因为 A.subtype 不能同时等于 B.subtype1 AND B.subtype2 ...

在示例中，id 的结果集应为:

xyz     aaa     0001
xyz     aab     0001
xyz     aac     0001
xyz     aad     0001

我正在尝试使用具有 2 AND 条件的联接，但当然我得到了一个空集。

编辑:

@Barmar 感谢您的支持。看来我真的很接近最终的解决方案了。为了让事情变得清晰，我用一个缩短和简化的数据结构打开了这个线程，只是为了突出我被困住的地方。我考虑了您的解决方案，并且将两个结果放在一行上是可以接受的。现在，我需要减少执行时间。

首次连接大约需要 2 分钟才能完成，并生成大约 2300 万行。第二次连接(表 B)可能需要更长的时间。事实上，我需要 3 个小时才能获得最终的 1000 万行数据集。我们怎样才能稍微改善一下呢？我注意到mysql引擎不是线程的，并且查询仅使用单个CPU。我索引了 join 使用的所有字段，但我不确定这样做是否正确......因为我不是 DBA我认为对于如此大的连接还必须依赖 VARCHAR 比较并不是最好的解决方案。也许我应该使用数字 ID 重写一些东西，这样会更快..

可能将事物分成不同的查询将有助于并行性。感谢您的反馈

Answer 1

您可以将表 A 与其本身连接起来，以查找具有相同 ID 的类型和子类型的所有组合，然后将它们与表 B 中的值进行比较。

SELECT t1.type AS type1, t1.subtype AS subtype1, t2.type AS type2, t2.subtype AS subtype2, t1.id
FROM TableA AS t1
JOIN TableA AS t2 ON t1.id = t2.id AND NOT (t1.type = t2.type AND t1.subtype = t2.subtype)
JOIN TableB AS b ON t1.type = b.type1 AND t1.subtype = b.subtype1 AND t2.type = b.type2 AND t2.subtype = b.subtype2

这会将表 A 中的两行作为结果中的一行返回，而不是作为单独的行，我希望这没问题。如果需要拆分它们，可以将其移动到子查询中，然后将其与原始表 A 连接回来以返回每一行。

SELECT a.*
FROM TableA AS a
JOIN (the above query) AS x
ON a.id = x.id AND
    ((a.type = x.type1 AND a.subtype = x.subtype1)
    OR
     (a.type = x.type2 AND a.subtype = x.subtype2))

DEMO