MySQL无效查询: select的嵌套级别太高
我把代码换成了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个表
还有其他想法吗？谢谢你的帮助。
回答 3
Stack Overflow用户
发布于 2012-02-04 03:48:28
可能与MySQL bug #41156, List of derived tables acts like a chain of mutually-nested subqueries有关。
错误日志表明它已针对MySQL 5.0.72、5.1.30和6.0.7进行了验证。
已在MySQL 5.1.37、MySQL 5.4.2 (后来变为5.5.thing)和NDB 7.1.0中修复。
关于上面问题中重新设计的查询：
透视表查询可能很棘手。您可以使用安德鲁在his answer中建议的方法。如果要搜索许多UPC值，则需要编写应用程序代码来构建SQL查询，并附加与要搜索的UPC值数量一样多的JOIN子句。
MySQL确实对单个查询中可以完成的连接数量有限制，但是示例中您应该没有达到这个限制。也就是说，您显示的查询确实有效。
我假设您展示了一个搜索四个UPC代码的示例查询，而您的应用程序可能会动态构造查询以获取更多数量的UPC代码，有时可能会超过61个。
看起来您的查询的目标是返回至少有一个列出的UPC代码的商店。您可以在下面的查询中更简单地做到这一点：
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的商店：
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的商店：
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的商店：
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;您仍然需要编写一些代码来构建这个查询，但是这样做会更容易一些，而且它不会超过您可以运行的连接或子查询的数量限制。
Stack Overflow用户
发布于 2012-02-04 03:42:36
这应该会在不使用子查询的情况下给出相同的结果：
SELECT s1.biz_name,
...
s2.upc AS upc2,
...
s3.upc AS upc3,
...
s4.upc AS upc4,
...
s5.upc AS upc5,
...
FROM allStores AS s1
LEFT OUTER JOIN storeCheckRecords AS s2 ON s1.e_address = s2.e_address
LEFT OUTER JOIN storeCheckRecords AS s3 ON s1.e_address = s3.e_address
LEFT OUTER JOIN storeCheckRecords AS s4 ON s1.e_address = s4.e_address
LEFT OUTER JOIN storeCheckRecords AS s5 ON s1.e_address = s5.e_address
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)
AND (s2.upc = '650637119004' OR s2.upc IS NULL)
AND (s3.upc = '650637119011' OR s3.upc IS NULL)
AND (s4.upc = '650637374007' OR s4.upc IS NULL)
AND (s5.upc = '650637374014' OR s5.upc IS NULL)Stack Overflow用户
发布于 2012-02-04 03:52:48
我将简化为首先使用一个简单的WHERE IN子句获取所有元素……您似乎正在执行一个数据透视表，以显示T1、T2、T3、T4和T5。如果您获取单个行中的所有数据，则可以在顶部的静态列中显示每行的详细信息。
SELECT
t1.brand,
t1.biz_name,
t1.biz_info,
t1.e_address,
t1.e_city,
t1.e_state,
t1.e_postal,
t1.e_zip_full,
t1.loc_LAT_centroid,
t1.loc_LONG_centroid,
t1.biz_phone,
t1.biz_phone_ext,
t1.biz_fax,
t1.biz_email,
t1.web_url,
t1.upc,
t1.retailprice,
t1.dollar_sales,
t1.dollar_sales_ly,
t1.todaydate,
t1.datetimesql,
t1.shelfposition,
t1.reg_sale,
t1.representative,
t1.notes
FROM
storeCheckRecords as t1
WHERE
t1.upc IN ( '650637119004', '650637119011', '650637374007', '650637374014') 例如..
Brand Bus Addr UPC Retail$ Sales Notes
xyz Bus Name UPC ... etc... Cur Yr
Bus Info Shelf Info Last Yr
Address, (Cit/State/Zip)
Lat / Long
Phone / Fax
Email / Web
----
Next Entry完全相同的地址与谁携带物品是相同的，这真的很重要吗？如果一个条目是"123 Main St"，另一个条目是"123B Main St“和"123 Main St- Suite B"，你不会找到匹配的。
此外，您提到的一些具有多达75个UPC代码...将它们放在一个单独的表中，并将其用作连接到"StoreCheckRecords“的第一个表，然后获取所有这些表……而不是手动键入后缀为2到75的所有列...或者无论下一次只有17次，又有4次...我认为你可能过于执着于你试图从数据中得到的东西。
您甚至可以按最初希望匹配的公共"e_address“进行分组，并提供该组作为报告给用户的部分之间的分隔符。
https://stackoverflow.com/questions/9134320
复制相似问题
腾讯云开发者
