的MySQL查询无效:嵌套选择
的过高的水平,我切换代码安德鲁斯解决方案:的MySQL查询无效:嵌套选择
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个表格
任何其他想法?谢谢您的帮助。
这应该给你相同的结果,而无需使用子查询:
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)
实际上,如果所有4个连接都成功,那么这只会返回一条记录,因为您的“WHERE”子句具有明确的“AND”。如果你将你的“AND s?.upc ='...'”元素移动到左外部连接部分,它就会工作。必须找到where子句IMPLIES a。 – DRapp 2012-02-03 19:44:58
@DRapp你是对的!为了清楚起见,我尝试将没有加入表格的内容放在ON子句之外。修复我的答案... – Andrew 2012-02-03 19:51:40
我切换到您的解决方案并发布新错误。感谢您的帮助。 – user1184169 2012-02-03 20:24:58
可能与 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.something)和NDB 7.1.0中修复。
关于上述问题的重新设计的查询:
枢轴查询可能会非常棘手。您可以使用Andrew在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;
你还必须写一些代码来构建这个查询,但它是一个有点容易做的,它不超过上加入或可以运行子查询的数量的任何限制。
我将简化到只得到所有的元素先用一个简单的WHERE IN子句......你似乎是做了透视表显示T1到T2到T3到T4至T5。如果您获取了单个行中的所有数据,那么您可以在顶部显示STATIC列来显示每个行下面的详细信息。
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”进行GROUP,并将该组作为报告给用户的部分之间的中断。
谢谢,但我将结果返回到谷歌地图,并需要每个商店的所有细节。因此,我需要upc1,upc2,upc3等。可以用这个查询完成吗? – user1184169 2012-02-03 23:38:42
@ user1184169,虽然我没有使用谷歌地图,但我会非常惊讶,他们为什么会采用非数据标准化方法来生成数据。有没有一些你正在与谷歌合作的规范,我可以看看。 – DRapp 2012-02-04 01:36:26
我觉得你不应该每个UPC加入'storeCheckRecords'一次。 – 2012-02-03 19:35:22
也许我错过了一些东西,但你确定你需要子查询吗?你可以做JOIN来创建数据透视表吗? – 2012-02-03 19:38:43
您原来的问题现在已经流失了。另外,storeCheckRecords是一个视图吗? DRapp的解决方案看起来越来越好。 – Andrew 2012-02-03 20:35:45