mysql嵌套查询排序无效果,MySQL无效查询：嵌套级别太高，无法进行选择

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

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.

解决方案

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:

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

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.

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

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;

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;

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.