MySQL无效查询:嵌套级别太高，无法进行选择 [英] MySQL Invalid query: Too high level of nesting for select

问题描述

我将代码切换为安德鲁斯解决方案:

I switched the code to Andrews solution:

SELECT s1.biz_name, s1.biz_info, s1.e_address, s1.e_city, s1.e_state,
    s1.e_postal, s1.e_zip_full, s1.loc_LAT_centroid, s1.loc_LONG_centroid,
    s1.biz_phone, s1.biz_phone_ext, s1.biz_fax, s1.biz_email, s1.web_url,
    s2.upc as upc2, s2.retailprice as retailprice2, s2.dollar_sales as
    dollar_sales2, s2.dollar_sales_ly as dollar_sales_ly2, s2.todaydate as
    todaydate2, s2.datetimesql as datetimesql2, s2.shelfposition as
    shelfposition2, s2.reg_sale as reg_sale2, s2.representative as
    representative2, s2.notes as notes2, s3.upc as upc3, s3.retailprice as
    retailprice3, s3.dollar_sales as dollar_sales3, s3.dollar_sales_ly as
    dollar_sales_ly3, s3.todaydate as todaydate3, s3.datetimesql as
    datetimesql3, s3.shelfposition as shelfposition3, s3.reg_sale as reg_sale3,
    s3.representative as representative3, s3.notes as notes3, s4.upc as upc4,
    s4.retailprice as retailprice4, s4.dollar_sales as dollar_sales4,
    s4.dollar_sales_ly as dollar_sales_ly4, s4.todaydate as todaydate4,
    s4.datetimesql as datetimesql4, s4.shelfposition as shelfposition4,
    s4.reg_sale as reg_sale4, s4.representative as representative4, s4.notes as
    notes4, s5.upc as upc5, s5.retailprice as retailprice5, s5.dollar_sales as
    dollar_sales5, s5.dollar_sales_ly as dollar_sales_ly5, s5.todaydate as
    todaydate5, s5.datetimesql as datetimesql5, s5.shelfposition as
    shelfposition5, s5.reg_sale as reg_sale5, s5.representative as
    representative5, s5.notes as notes5 
FROM allStores AS s1 
LEFT OUTER JOIN storeCheckRecords AS s2
    ON s1.e_address = s2.e_address AND s2.upc = '650637119004' 
LEFT OUTER JOIN storeCheckRecords AS s3 
    ON s1.e_address = s3.e_address AND s3.upc = '650637119011' 
LEFT OUTER JOIN storeCheckRecords AS s4 
    ON s1.e_address = s4.e_address AND s4.upc = '650637374007' 
LEFT OUTER JOIN storeCheckRecords AS s5 
    ON s1.e_address = s5.e_address AND s5.upc = '650637374014' 
WHERE  s2.e_address IS NOT NULL
    OR s3.e_address IS NOT NULL
    OR s4.e_address IS NOT NULL
    OR s5.e_address IS NOT NULL

这是新错误:查询无效:表太多； MySQL在一个联接中只能使用61个表

Here is the new error: Invalid query: Too many tables; MySQL can only use 61 tables in a join

还有其他想法吗?感谢您的帮助.

Any other ideas? Thanks for the help.

推荐答案

可能与 MySQL错误#41156，派生表的列表就像是相互嵌套的子查询链.

该错误日志表明已针对MySQL 5.0.72、5.1.30和6.0.7进行了验证.
在MySQL 5.1.37，MySQL 5.4.2(已变为5.5.something)和NDB 7.1.0中进行了修复.

The bug log indicates it was verified against MySQL 5.0.72, 5.1.30, and 6.0.7.
Fixed in MySQL 5.1.37, MySQL 5.4.2 (which became 5.5.something), and NDB 7.1.0.

关于上述问题中重新设计的查询:

Regarding your redesigned query in the question above:

数据透视查询可能很棘手.您可以在他的答案中使用安德鲁建议的方法.如果要搜索许多UPC值，则需要编写应用程序代码来构建SQL查询，并添加与要搜索的UPC值数量一样多的JOIN子句.

Pivot queries can be tricky. You can use the method suggested by Andrew in his answer. If you search for many UPC values, you need to write application code to build the SQL query, appending as many JOIN clauses as the number of UPC values you're searching for.

MySQL确实对单个查询中可以进行的连接数有限制，但是您所达到的示例不应达到此限制.也就是说，您显示的查询确实有效.

MySQL does have a limit on the number of joins that can be done in a single query, but the example you should doesn't reach the limit. That is, the query you show does work.

我假设您正在显示一个示例查询，该查询搜索四个UPC代码，而您的应用程序可能会动态构造查询以获取更多的UPC代码，有时可能会超过61个.

I assume that you're showing an example query searching for four UPC codes, whereas your app may construct the query dynamically for a greater number of UPC codes, and that may be more than 61 sometimes.

查询的目标似乎是返回至少具有列出的UPC代码之一的商店.您可以在以下查询中更简单地做到这一点:

It looks like the goal of your query is to return stores that has at least one of the listed UPC codes. You can do that more simply in the following query:

SELECT DISTINCT s.*
FROM allStores AS s
JOIN storeCheckRecords AS cr
  ON s.e_address = cr.e_address
     AND cr.upc IN ('650637119004','650637119011','650637374007','650637374014');

您可以通过其他方式使用此方法，例如查找具有所有四个UPC的商店:

You can use this method in other ways, for example to find stores that have all four of the UPC's:

SELECT s.*
FROM allStores AS s
JOIN storeCheckRecords AS cr
  ON s.e_address = cr.e_address
     AND cr.upc IN ('650637119004','650637119011','650637374007','650637374014');
GROUP BY s.e_address
HAVING COUNT(DISTINCT upc) = 4;

或者要查找UPC中的一些但不是全部四个的商店:

Or to find stores that some but not all four of the UPC's:

SELECT s.*
FROM allStores AS s
JOIN storeCheckRecords AS cr
  ON s.e_address = cr.e_address
     AND cr.upc IN ('650637119004','650637119011','650637374007','650637374014');
GROUP BY s.e_address
HAVING COUNT(DISTINCT upc) < 4;

或者查找缺少所有四个UPC的商店:

Or to find stores that lack all four of the UPC's:

SELECT s.*
FROM allStores AS s
JOIN storeCheckRecords AS cr
  ON s.e_address = cr.e_address
     AND cr.upc IN ('650637119004','650637119011','650637374007','650637374014');
WHERE cr.e_address IS NULL;

您仍然必须编写一些代码来构建此查询，但这要容易一些，并且不会超出您可以运行的联接或子查询的数量限制.

You still have to write some code to build this query, but it's a bit easier to do, and it doesn't exceed any limits on the number of joins or subqueries you can run.

这篇关于MySQL无效查询:嵌套级别太高，无法进行选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！