我正在努力使现有的数据库和系统符合我们现在从制造商那里收到的数据。它的格式不能/不会更改。这是一个包含单个制造商的产品编号（item_id）的表，该表交叉引用了多个制造商的零件（oem_code）。
表：xref
item_id | oem_code
A | 123
A | 234
B | 234
B | 345
C | 456
表：零件（仅显示相关零件编号列）
partNum
S.A
S.B
S.C
123
234
345
456
两个外部参照列都包含零件表中的零件号。我需要输入零件号($fielddata)，然后从parts.partNum = $fielddata所在的零件中检索所有数据，以及从外部参照中检索与parts.partNum匹配的所有交叉引用编号。不幸的是，制造商的零件号为S。在我们的系统中在它们之前，但不在它们发送的表中。如果没有S，我们的系统将无法识别它们。
MySQL查询是
SELECT *
FROM parts
WHERE partNum
IN (
SELECT oem_code
FROM xref
WHERE item_id = (
SELECT item_id
FROM xref
WHERE oem_code = '$fielddata'
)
)
123返回parts.partNum=123234（还需要S.A）的部件的所有数据234出错，#1242-子查询返回1行以上（需要234，S.A，S.B）A不返回任何内容（需要123234，S.A）S.A也不返回任何内容（需要123234，S.A）
我理解我的sql语句和子查询的局限性，以及为什么我没有得到所需的完整结果集，但我不知道如何扩展它以获得我想要的结果。我很高兴能做到这一点。当我试图扩展它时，我会得到不希望的结果或错误，所以我几乎已经力不从心了。
我还意识到，在大型数据库上运行子查询效率不高。因此，我准备对sql进行全面重写，或者对迄今为止的内容进行修改。外部参照有>27k行，零件有>550k行和10列（如果有人关心的话）。
如有任何帮助，我们将不胜感激。
使用exists 的查询
SELECT *
FROM parts
WHERE EXISTS (
SELECT 1 FROM xref x1
JOIN xref x2 ON x1.item_id = x2.item_id
WHERE (x2.oem_code = '$fielddata' OR x2.item_id = RIGHT('$fielddata',1))
AND (x1.oem_code = partNum OR CONCAT('S.',x1.item_id) = partNum)
)
更新
针对给定$fielddata的派生表进行联接可能更快。这本质上是@JohnRuddell的查询，但被修改为使用派生表。
SELECT p.* FROM parts p
JOIN (
SELECT x.oem_code partNum
FROM xref x
JOIN xref x1
ON x1.item_id = x.item_id
AND x1.oem_code = '$fielddata'
OR x.item_id = RIGHT('$fielddata', 1)
UNION
SELECT CONCAT('S.', x.item_id) partNum
FROM xref x
WHERE x.oem_code = '$fieldata'
OR x.item_id = RIGHT('$fielddata', 1)
) t1 ON t1.partNum = p.partNum
我认为这应该符合的要求
SELECT *
FROM parts
WHERE partNum IN
( SELECT x.oem_code
FROM xref x
JOIN xref x1
ON x1.item_id = x.item_id
AND x1.oem_code = '$fielddata'
OR x.item_id = RIGHT('$fielddata', 1)
)
OR partNum IN
( SELECT CONCAT('S.', x.item_id)
FROM xref x
WHERE x.oem_code = '$fieldata'
OR x.item_id = RIGHT('$fielddata', 1)
)
演示
只需使用联接，就不会进行第三个子查询