mysql - 子查询在 SELECT 部分返回多于 1 行(Dlookup 到 MySql)

Question

我正在将桌面应用程序从 MS Access VBA 迁移到 Java Springboot 应用程序，并且需要在 MySQL 中进行 VBA 查询。 VBA 查询非常大，因此我向您展示一个较小的示例来演示我的情况。VBA查询:

SELECT SELECT tbl_trade.id, 
   DLookUp("[price]","tbl_so_manifest","[so_id] = " 
   & tbl_trade.so_id & " AND [product_id] = " & tbl_po_manifest.product_id),
   // .... many more attributes....
FROM tbl_so_manifest as sm (((
   /// 15 nested INNER JOINS ....
   INNER JOIN tbl_trade AS t ON sm.so_id = t.so_id 
   INNER JOIN tbl_po_manifest AS pm ON sm.product_id = pm.product_id
   )));

MySQL 转换:

SELECT SELECT tbl_trade.id, 
   (SELECT sm.product_id 
   FROM tbl_so_manifest as sm 
      INNER JOIN tbl_trade AS t ON sm.so_id = t.so_id 
      INNER JOIN tbl_po_manifest AS pm ON sm.product_id = pm.product_id), 
   // ......
FROM tbl_so_manifest as sm (((
   /// 15 nested INNER JOINS ....
   INNER JOIN tbl_trade AS t ON sm.so_id = t.so_id 
   INNER JOIN tbl_po_manifest AS pm ON sm.product_id = pm.product_id
   )));

但它给出了错误

Error Code: 1242. Subquery returns more than 1 row 0.844 sec

需要有关如何处理此问题的建议。

Answer 1

您可以强制子查询仅返回一个前1的记录

所以这个:

DLookUp("[price]","tbl_so_manifest","[so_id] = " 
& tbl_trade.so_id & " AND [product_id] = " & tbl_po_manifest.product_id)

变成:

(SELECT TOP 1 Price FROM tbl_so_manifest 
         WHERE so_ID = tbl_trade.so_id AND product_id = sm.product_id
         ORDER BY PRICE, ID DESC) AS MyPrice

因此，您实际上不需要联接，而只需“提取”该值即可。为了将子查询限制为一条记录，TOP 1 和按“PK”排序将确保只返回一行。如果省略“id”，那么如果两个最高价格相同，那么您将得到两行。通过将“ID”添加到订单依据中，即使 5 个价格相同，您也只能获得一行。

如果上下文中的其他表具有相同的列名，那么当然将表限定符添加到上面(但是，它是为了速记而写的)。

因此，作为一般规则，您可以将 dlookup() 替换为上述类型的查询。我对 MySQL 不太熟悉，所以你可能无法使用上面的表别名，但总而言之，使用 top 1 并按“ID(或 PK)”在价格之后或其他情况下进行排序其中可能存在多于一行。在大多数情况下，dlookup() 不应该有多个值(但 dlookup() 始终只返回一个值)。因此，在大多数从 dlookup() 到子查询的“翻译”中， top 1 不应该是必需的。

我不认为 dlookup() 需要“连接”。 Dlookup() 总是命中一张表，从 dlookup() 到子查询的转换也应该如此。您希望选取列、表格，然后是条件)。

如果 dlookup() 是 FK 值，则左连接到查找表会更容易，不会导致查询中出现额外的行，但任何一种方法都可以。我发现子查询与主查询返回的行数“困惑”的可能性较小。

因此，dlookup() 到子查询是一对一的转换。不需要加入，并且正如在大多数情况下所指出的，在大多数情况下不需要 TOP + 附加的“id/pk”。