select distinct x.Code,(select count(weight) from persont where weight>=0 and weight<=20 and ( x.code=compose or x.Code=compose2 or x.Code=compose3 or x.Code=compose4) ) as weight1,
(select count(weight) from persont where weight>=21 and weight<=40 and ( x.code=compose or x.Code=compose2 or x.Code=compose3 or x.Code=compose4)) as weight2,
(select count(weight) from persont where weight>=41 and weight<=60 and ( x.code=compose or x.Code=compose2 or x.Code=compose3 or x.Code=compose4)) as weight3,
(select count(weight) from persont where weight>=61 and weight<=80 and ( x.code=compose or x.Code=compose2 or x.Code=compose3 or x.Code=compose4)) as weight4,
(select count(weight) from persont where weight>=81 and weight<=100 and ( x.code=compose or x.Code=compose2 or x.Code=compose3 or x.Code=compose4)) as weight5,
(select count(weight) from persont where weight>=101 and weight<=120 and ( x.code=compose or x.Code=compose2 or x.Code=compose3 or x.Code=compose4)) as weight6,
(select count(weight) from persont where weight>=121 and weight<=140 and ( x.code=compose or x.Code=compose2 or x.Code=compose3 or x.Code=compose4)) as weight7,
(select count(weight) from persont where weight>=141 and weight<=160 and ( x.code=compose or x.Code=compose2 or x.Code=compose3 or x.Code=compose4)) as weight8,
(select count(weight) from persont where weight>=161 and ( x.code=compose or x.Code=compose2 or x.Code=compose3 or x.Code=compose4)) as weight9,
(select count(weight) from persont where ( x.code=compose or x.Code=compose2 or x.Code=compose3 or x.Code=compose4) ) as total
from x_compose x left join persont p on ( x.code=p.compose or x.Code=p.compose2 or x.Code=p.compose3 or x.Code=p.compose4)
where x.class=1
group by x.code ;当数据达到10000条以上的时候执行查询非常慢,请高手指教!
(select count(weight) from persont where weight>=21 and weight<=40 and ( x.code=compose or x.Code=compose2 or x.Code=compose3 or x.Code=compose4)) as weight2,
(select count(weight) from persont where weight>=41 and weight<=60 and ( x.code=compose or x.Code=compose2 or x.Code=compose3 or x.Code=compose4)) as weight3,
(select count(weight) from persont where weight>=61 and weight<=80 and ( x.code=compose or x.Code=compose2 or x.Code=compose3 or x.Code=compose4)) as weight4,
(select count(weight) from persont where weight>=81 and weight<=100 and ( x.code=compose or x.Code=compose2 or x.Code=compose3 or x.Code=compose4)) as weight5,
(select count(weight) from persont where weight>=101 and weight<=120 and ( x.code=compose or x.Code=compose2 or x.Code=compose3 or x.Code=compose4)) as weight6,
(select count(weight) from persont where weight>=121 and weight<=140 and ( x.code=compose or x.Code=compose2 or x.Code=compose3 or x.Code=compose4)) as weight7,
(select count(weight) from persont where weight>=141 and weight<=160 and ( x.code=compose or x.Code=compose2 or x.Code=compose3 or x.Code=compose4)) as weight8,
(select count(weight) from persont where weight>=161 and ( x.code=compose or x.Code=compose2 or x.Code=compose3 or x.Code=compose4)) as weight9,
(select count(weight) from persont where ( x.code=compose or x.Code=compose2 or x.Code=compose3 or x.Code=compose4) ) as total
from x_compose x left join persont p on ( x.code=p.compose or x.Code=p.compose2 or x.Code=p.compose3 or x.Code=p.compose4)
where x.class=1
group by x.code ;当数据达到10000条以上的时候执行查询非常慢,请高手指教!
sum(if(p.weight>=0 and p.weight<=20,1,0))
看看结果是否正确
分别加索引
SUM(IF(IFNULL(P.weight,0)>=0 and IFNULL(P.weight,0)<=20,1,0))
show index from persont;
persont 1 persont_No 1 no A 10004 BTREE
persont 1 persont_No 2 serialno A 10004 YES BTREE
persont 1 persont_compose1 1 compose A 2 YES BTREE
persont 1 persont_compose2 1 compose2 A 2 YES BTREE
persont 1 persont_compose3 1 compose3 A 2 YES BTREE
persont 1 persont_compose4 1 compose4 A 2 YES BTREE
select distinct x.Code,(select count(weight) from persont where weight>=0 and weight<=20 and ( x.code=compose or x.Code=compose2 or x.Code=compose3 or x.Code=compose4) ) as weight1
from x_compose x left join persont p on ( x.code=p.compose or x.Code=p.compose2 or x.Code=p.compose3 or x.Code=p.compose4)
where x.class=1
group by x.code ;贴结果
declare iFName varchar(10);
declare iESickName varchar(10);
declare iOSickName varchar(20);
declare stop int default 0;
declare cur cursor for select Family,EmictionSickName,OtherSickName from t_familysick where EswlID=iEswlID;
declare continue handler for NOT FOUND SET stop=1;
set @sickName="";
open cur;
while stop <> 1 do
fetch cur into iFName,iESickName,iOSickName;
set @sickName=CONCAT(@sickName,'/',iFName,'-尿石病史:',iESickName,';其他病史:',iOSickName);
set iFName='';set iESickName='';set iOSickName='';
end while;
close cur;
set @sickName=SUBSTRING(@sickName FROM 2);
set @sickName1=substring_index(@sickName,'/',-1);
set @sickName=substring_index(@sickName,@sickName1,1);
set @sickName=SUBSTRING(@sickName FROM 1 FOR CHAR_LENGTH(@sickName)-1);
set @sickName=SUBSTRING(@sickName FROM 1 FOR 500);
return @sickName;
END