情况如下:
select id from table1 where find_in_set(type_id,getCatalogList(10004));
select id from table1 where type_id in (type_id,getCatalogList(10004));需要找到table1中类别在getCatalogList(10004)类别中的数据 做统计及导出Excel 但是执行速度是在太慢了,有没有更好的办法 或方式 提高速度 注释:
getCatalogList(10004)是获取一个大类的子类的function
select id from table1 where find_in_set(type_id,getCatalogList(10004));
select id from table1 where type_id in (type_id,getCatalogList(10004));需要找到table1中类别在getCatalogList(10004)类别中的数据 做统计及导出Excel 但是执行速度是在太慢了,有没有更好的办法 或方式 提高速度 注释:
getCatalogList(10004)是获取一个大类的子类的function
select p.catalog_id ,c.title as catalog_title ,p.plan_storage_id, s.title as storage_title, left(p.delivery_date,10) as delivery_date ,
sum(p.quantity) as quantity from plan_storage_sales p,product_catalog c,product_storage_catalog s
where find_in_set (p.catalog_id,product_line_child_list(catalogID) ) and p.catalog_id = c.id and p.plan_storage_id = s.id
and p.plan_storage_id=storageID and p.delivery_date between startDatre and endDatre
group by p.catalog_id, left(p.delivery_date,10);set @sqlselect = dateStr_;
prepare stmtselect from @sqlselect;
execute stmtselect;
deallocate prepare stmtselect;
END
DECLARE sTemp VARCHAR(1000);DECLARE sTempChd VARCHAR(1000);DECLARE THE_CNT VARCHAR(1000);set sTemp = SUBSTRING_INDEX(NodeId, ',', 1);set sTempChd=NodeId;WHILE sTempChd is not null DO SET sTemp = concat(sTemp,',',sTempChd); SELECT group_concat(id) INTO sTempChd FROM product_catalog where FIND_IN_SET(parentid,sTempChd)>0; END WHILE;RETURN sTemp;END
where find_in_set (p.catalog_id,getCatalogList(catalogID) ) 调用这个才对
另外建议还是用,如果ID上有索引
select id from table1 where type_id in (100004,100004,100019,100020,100021,100022,101081,101136,100031,100032,100033,100034,100035,100036,100037,100038,100039,100040,100041,100042,100043,100044,100045,100046,100047,100048,100049,100062,100063,100064,100065,100068,100069,100070,100071,100072,100073,100074,100075,100076,100077,100078,100079,100080,100081,100082,100083,100084,100087,100092,100093,100095,100096,100097,100098,100099,100100,100101,100102,100103,100104,100105,100106,100107,100108,100109,100111,100112,100113,100114,100115,100116,100117,100118,100119,100120,100121,100122,100123,100124,100125,100127,100128,100129,100186,101084,101085,101091,101092,101093,101101,101103,101109,101110,101111,101112,101113,101114,101115,101116,101117,101119,101120,101121,101122,101123,101124,101125,101126,101127,101128,101129,101130,101131,101174,101175,101176,101177,101178,101179,101180,101181,101182,101183,101184,101185,101186,101187,101188,101189);
set @sqlselect = dateStr_;
prepare stmtselect from @sqlselect;
execute stmtselect;
deallocate prepare stmtselect; 预处理是为了转化临时表里(即第一次查询的结果)的格式 。问题:find_in_set()速度很慢
where p.catalog_id in (p.catalog_id,getCatalogList(catalogID) )用in总是把集合中所有数据都查出来
测过了 是find_in_set 慢
where id in (id) 类似于 where id=id 当然所有记录都会被选中。
或
where p.catalog_id in (100004,product_line_child_list(100004) ) ;都是没结果的 这个in()后面的第一个参数怎么设置
select id from table1 where type_id in (100004,100004,100019,100020,100021,100022,101081,101136,100031,100032,100033,100034,100035,100036,100037,100038,100039,100040,100041,100042,100043,100044,100045,100046,100047,100048,100049,100062,100063,100064,100065,100068,100069,100070,100071,100072,100073,100074,100075,100076,100077,100078,100079,100080,100081,100082,100083,100084,100087,100092,100093,100095,100096,100097,100098,100099,100100,100101,100102,100103,100104,100105,100106,100107,100108,100109,100111,100112,100113,100114,100115,100116,100117,100118,100119,100120,100121,100122,100123,100124,100125,100127,100128,100129,100186,101084,101085,101091,101092,101093,101101,101103,101109,101110,101111,101112,101113,101114,101115,101116,101117,101119,101120,101121,101122,101123,101124,101125,101126,101127,101128,101129,101130,101131,101174,101175,101176,101177,101178,101179,101180,101181,101182,101183,101184,101185,101186,101187,101188,101189);
这个可以 很快很正确 但是一把in后面 换成function(product_line_child_list(100004) ,100004)就废了
你返回的是多个数字和逗号组成的一个字符串,无法转为数字。 既然你已经把数字写入到了一个临时表,为什么不直接与这个临时表做JOIN查询?然后返回需要的结果集?
'截至2011-10-27 17:36:46 用户结帖率0.00% 当您的问题得到解答后请及时结贴.
http://topic.csdn.net/u/20090501/15/7548d251-aec2-4975-a9bf-ca09a5551ba5.html
http://topic.csdn.net/u/20100428/09/BC9E0908-F250-42A6-8765-B50A82FE186A.html
http://topic.csdn.net/u/20100626/09/f35a4763-4b59-49c3-8061-d48fdbc29561.html8、如何给分和结贴?
http://community.csdn.net/Help/HelpCenter.htm#结帖