MySQL查询/子查询

Question

MySQL查询/子查询

3

我正在试图将现有的数据库和系统调整为我们从制造商那里接收到的数据。但它的格式不能/不会更改。这是一个表格，其中包含来自单个制造商的产品编号（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

两个xref列都包含零件表中的零件号。我需要输入一个零件号($fielddata)，然后检索出所有从parts表中匹配parts.partNum = $fielddata的数据，以及与parts.partNum相匹配的所有xref的交叉参考编号。不幸的是，制造商的零件号在我们的系统中有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 = 123 的部件数据，234（还需要 S.A）
234 出错：#1242 - 子查询返回多行 (需要 234、S.A 和 S.B)
A 没有返回结果 (需要 123、234 和 S.A)
S.A 也没有返回结果 (需要 123、234 和 S.A)

我理解我的 SQL 语句和子查询的限制以及为什么无法得到完整的结果集，但我不知道如何扩展它以获得所需的结果。我很高兴能够用它做到这一步。当我尝试扩展它时，我会得到不想要的结果或错误，所以我基本上已经达到了我的能力极限。

我也知道，在大型数据库上运行子查询并不高效。因此，我可以对 SQL 进行全面重写或修改我目前的代码。如果有人关心，xref 表有 >27k 行记录，而 parts 表有 >550k 行记录，共 10 列。

任何帮助都将不胜感激。

- Greg Buck

你能编辑你的问题并提供样本结果吗？我不确定这样的查询应该返回什么。 - Gordon Linoff

2个回答

1

我认为这应该符合您的要求。

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

演示

只需使用一个连接，这样就不需要进行第三个子查询了。

- John Ruddell

1

第二个子查询不需要连接，即 select concat('S.',x.item_id) from xref x where x.oem_code = '$fieldata' or x.item_id = '$fielddata'。 - FuzzyTree

这段代码实际上比其他代码快，但对于生产仍然远远不够快。我期望的太多了吗？就像我在原帖中说的那样，xref有超过27k行需要与550k个零件进行交叉引用。 - Greg Buck

1

@GregBuck，你在表上有哪些索引？我相信你应该能够通过一些索引来优化它，使其运行更快。但你可能需要做更多的研究。 - John Ruddell

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- FuzzyTree · Accepted Answer

使用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