我对sql 存储过程了解比较少,那大家看看我下面的统计,该怎样才能提高执行效率?载analyzer里面大概需要11秒,delphi里用adoquery 和 dbgrideh需要半分钟。12000条记录。其中有些关键的排序字段是汉字。没有关联表。select a_agency_short, count(distinct(a_id)) as tdsl, sum(a_cus_num) as tdrs, sum(a_type_sum) as azh, cast(sum(a_type_sum) as decimal(38,2)) /cast(sum(a_cus_num) as decimal(38,2)) as apj, sum(b_type_sum) as bzh, cast(sum(b_type_sum) as decimal(38,2)) /cast(sum(a_cus_num) as decimal(38,2)) as bpj, sum(c_type_sum) as czh, cast(sum(c_type_sum) as decimal(38,2)) /cast(sum(a_cus_num) as decimal(38,2)) as cpj, sum(a_type_sum)+sum(b_type_sum)+sum(c_type_sum) as zzh, cast(sum(a_type_sum)+sum(b_type_sum)+sum(c_type_sum) as decimal(38,2)) /cast(sum(a_cus_num) as decimal(38,2)) as zpj, sum(a_agency_cos) as zrt, cast(sum(a_agency_cos) as decimal(38,2)) /cast(sum(a_cus_num) as decimal(38,2)) as rtpj, qita, anhui, aomen, beijing, chongqing, fujian, guangdong, gansu, guangxi, guizhou, hebei, hubei, xianggang, heilongjiang, hainan, henan, hunan, jilin, jiangsu, jiangxi, liaoning, qinghai, sichuan, shandong, shanghai, shanxi, shanxi1, tianjin, taiwan, xinjiang, xizang, yunnan, zhejiang from a_all A LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS qita FROM A_ALL WHERE A_CITY_NAME='其他' GROUP BY A_AGENCY_SHORT) C1 ON A.a_agency_short=c1.ag LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS anhui FROM A_ALL WHERE A_CITY_NAME='安徽' GROUP BY A_AGENCY_SHORT) C2 ON A.a_agency_short=c2.ag LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS aomen FROM A_ALL WHERE A_CITY_NAME='澳门' GROUP BY A_AGENCY_SHORT) C3 ON A.a_agency_short=c3.ag LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS beijing FROM A_ALL WHERE A_CITY_NAME='北京' GROUP BY A_AGENCY_SHORT) C4 ON A.a_agency_short=c4.ag LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS chongqing FROM A_ALL WHERE A_CITY_NAME='重庆' GROUP BY A_AGENCY_SHORT) C5 ON A.a_agency_short=c5.ag LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS fujian FROM A_ALL WHERE A_CITY_NAME='福建' GROUP BY A_AGENCY_SHORT) C6 ON A.a_agency_short=c6.ag LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS guangdong FROM A_ALL WHERE A_CITY_NAME='广东' GROUP BY A_AGENCY_SHORT) C7 ON A.a_agency_short=c7.ag LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS gansu FROM A_ALL WHERE A_CITY_NAME='甘肃' GROUP BY A_AGENCY_SHORT) C8 ON A.a_agency_short=c8.ag LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS guangxi FROM A_ALL WHERE A_CITY_NAME='广西' GROUP BY A_AGENCY_SHORT) C9 ON A.a_agency_short=c9.ag LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS guizhou FROM A_ALL WHERE A_CITY_NAME='贵州' GROUP BY A_AGENCY_SHORT) C10 ON A.a_agency_short=c10.ag LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS hebei FROM A_ALL WHERE A_CITY_NAME='河北' GROUP BY A_AGENCY_SHORT) C11 ON A.a_agency_short=c11.ag LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS hubei FROM A_ALL WHERE A_CITY_NAME='湖北' GROUP BY A_AGENCY_SHORT) C12 ON A.a_agency_short=c12.ag LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS xianggang FROM A_ALL WHERE A_CITY_NAME='香港' GROUP BY A_AGENCY_SHORT) C13 ON A.a_agency_short=c13.ag LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS heilongjiang FROM A_ALL WHERE A_CITY_NAME='黑龙江' GROUP BY A_AGENCY_SHORT) C14 ON A.a_agency_short=c14.ag LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS hainan FROM A_ALL WHERE A_CITY_NAME='海南' GROUP BY A_AGENCY_SHORT) C15 ON A.a_agency_short=c15.ag LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS henan FROM A_ALL WHERE A_CITY_NAME='河南' GROUP BY A_AGENCY_SHORT) C16 ON A.a_agency_short=c16.ag LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS hunan FROM A_ALL WHERE A_CITY_NAME='湖南' GROUP BY A_AGENCY_SHORT) C17 ON A.a_agency_short=c17.ag LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS jilin FROM A_ALL WHERE A_CITY_NAME='吉林' GROUP BY A_AGENCY_SHORT) C18 ON A.a_agency_short=c18.ag LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS jiangsu FROM A_ALL WHERE A_CITY_NAME='江苏' GROUP BY A_AGENCY_SHORT) C19 ON A.a_agency_short=c19.ag LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS jiangxi FROM A_ALL WHERE A_CITY_NAME='江西' GROUP BY A_AGENCY_SHORT) C20 ON A.a_agency_short=c20.ag LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS liaoning FROM A_ALL WHERE A_CITY_NAME='辽宁' GROUP BY A_AGENCY_SHORT) C21 ON A.a_agency_short=c21.ag LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS qinghai FROM A_ALL WHERE A_CITY_NAME='青海' GROUP BY A_AGENCY_SHORT) C22 ON A.a_agency_short=c22.ag LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS sichuan FROM A_ALL WHERE A_CITY_NAME='四川' GROUP BY A_AGENCY_SHORT) C23 ON A.a_agency_short=c23.ag LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS shandong FROM A_ALL WHERE A_CITY_NAME='山东' GROUP BY A_AGENCY_SHORT) C24 ON A.a_agency_short=c24.ag LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS shanghai FROM A_ALL WHERE A_CITY_NAME='上海' GROUP BY A_AGENCY_SHORT) C25 ON A.a_agency_short=c25.ag LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS shanxi FROM A_ALL WHERE A_CITY_NAME='山西' GROUP BY A_AGENCY_SHORT) C26 ON A.a_agency_short=c26.ag LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS shanxi1 FROM A_ALL WHERE A_CITY_NAME='陕西' GROUP BY A_AGENCY_SHORT) C27 ON A.a_agency_short=c27.ag LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS tianjin FROM A_ALL WHERE A_CITY_NAME='天津' GROUP BY A_AGENCY_SHORT) C28 ON A.a_agency_short=c28.ag LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS taiwan FROM A_ALL WHERE A_CITY_NAME='台湾' GROUP BY A_AGENCY_SHORT) C29 ON A.a_agency_short=c29.ag LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS xinjiang FROM A_ALL WHERE A_CITY_NAME='新疆' GROUP BY A_AGENCY_SHORT) C30 ON A.a_agency_short=c30.ag LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS xizang FROM A_ALL WHERE A_CITY_NAME='西藏' GROUP BY A_AGENCY_SHORT) C31 ON A.a_agency_short=c31.ag LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS yunnan FROM A_ALL WHERE A_CITY_NAME='云南' GROUP BY A_AGENCY_SHORT) C32 ON A.a_agency_short=c32.ag LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS zhejiang FROM A_ALL WHERE A_CITY_NAME='浙江' GROUP BY A_AGENCY_SHORT) C33 ON A.a_agency_short=c33.ag GROUP BY A.A_AGENCY_SHORT, c1.qita, C2.anhui, c3.aomen, c4.beijing, c5.chongqing, c6.fujian, c7.guangdong, c8.gansu, c9.guangxi, c10.guizhou, c11.hebei, c12.hubei, c13.xianggang, c14.heilongjiang, c15.hainan, c16.henan, c17.hunan, c18.jilin, c19.jiangsu, c20.jiangxi, c21.liaoning, c22.qinghai, c23.sichuan, c24.shandong, c25.shanghai, c26.shanxi, c27.shanxi1, c28.tianjin, c29.taiwan, c30.xinjiang, c31.xizang, c32.yunnan, c33.zhejiang
count(distinct(a_id)) as tdsl,
sum(a_cus_num) as tdrs,
sum(a_type_sum) as azh,
cast(sum(a_type_sum) as decimal(38,2))
/cast(sum(a_cus_num) as decimal(38,2)) as apj,
sum(b_type_sum) as bzh,
cast(sum(b_type_sum) as decimal(38,2))
/cast(sum(a_cus_num) as decimal(38,2)) as bpj,
sum(c_type_sum) as czh,
cast(sum(c_type_sum) as decimal(38,2))
/cast(sum(a_cus_num) as decimal(38,2)) as cpj,
sum(a_type_sum)+sum(b_type_sum)+sum(c_type_sum) as zzh,
cast(sum(a_type_sum)+sum(b_type_sum)+sum(c_type_sum) as decimal(38,2))
/cast(sum(a_cus_num) as decimal(38,2)) as zpj,
sum(a_agency_cos) as zrt,
cast(sum(a_agency_cos) as decimal(38,2))
/cast(sum(a_cus_num) as decimal(38,2)) as rtpj, qita, anhui, aomen, beijing, chongqing, fujian, guangdong, gansu, guangxi, guizhou, hebei, hubei, xianggang, heilongjiang, hainan, henan, hunan, jilin, jiangsu, jiangxi, liaoning, qinghai, sichuan, shandong, shanghai, shanxi, shanxi1, tianjin, taiwan, xinjiang, xizang, yunnan, zhejiang from a_all A LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS qita FROM A_ALL WHERE A_CITY_NAME='其他' GROUP BY A_AGENCY_SHORT) C1 ON A.a_agency_short=c1.ag
LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS anhui FROM A_ALL WHERE A_CITY_NAME='安徽' GROUP BY A_AGENCY_SHORT) C2 ON A.a_agency_short=c2.ag
LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS aomen FROM A_ALL WHERE A_CITY_NAME='澳门' GROUP BY A_AGENCY_SHORT) C3 ON A.a_agency_short=c3.ag
LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS beijing FROM A_ALL WHERE A_CITY_NAME='北京' GROUP BY A_AGENCY_SHORT) C4 ON A.a_agency_short=c4.ag
LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS chongqing FROM A_ALL WHERE A_CITY_NAME='重庆' GROUP BY A_AGENCY_SHORT) C5 ON A.a_agency_short=c5.ag
LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS fujian FROM A_ALL WHERE A_CITY_NAME='福建' GROUP BY A_AGENCY_SHORT) C6 ON A.a_agency_short=c6.ag
LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS guangdong FROM A_ALL WHERE A_CITY_NAME='广东' GROUP BY A_AGENCY_SHORT) C7 ON A.a_agency_short=c7.ag
LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS gansu FROM A_ALL WHERE A_CITY_NAME='甘肃' GROUP BY A_AGENCY_SHORT) C8 ON A.a_agency_short=c8.ag
LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS guangxi FROM A_ALL WHERE A_CITY_NAME='广西' GROUP BY A_AGENCY_SHORT) C9 ON A.a_agency_short=c9.ag
LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS guizhou FROM A_ALL WHERE A_CITY_NAME='贵州' GROUP BY A_AGENCY_SHORT) C10 ON A.a_agency_short=c10.ag
LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS hebei FROM A_ALL WHERE A_CITY_NAME='河北' GROUP BY A_AGENCY_SHORT) C11 ON A.a_agency_short=c11.ag
LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS hubei FROM A_ALL WHERE A_CITY_NAME='湖北' GROUP BY A_AGENCY_SHORT) C12 ON A.a_agency_short=c12.ag
LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS xianggang FROM A_ALL WHERE A_CITY_NAME='香港' GROUP BY A_AGENCY_SHORT) C13 ON A.a_agency_short=c13.ag
LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS heilongjiang FROM A_ALL WHERE A_CITY_NAME='黑龙江' GROUP BY A_AGENCY_SHORT) C14 ON A.a_agency_short=c14.ag
LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS hainan FROM A_ALL WHERE A_CITY_NAME='海南' GROUP BY A_AGENCY_SHORT) C15 ON A.a_agency_short=c15.ag
LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS henan FROM A_ALL WHERE A_CITY_NAME='河南' GROUP BY A_AGENCY_SHORT) C16 ON A.a_agency_short=c16.ag
LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS hunan FROM A_ALL WHERE A_CITY_NAME='湖南' GROUP BY A_AGENCY_SHORT) C17 ON A.a_agency_short=c17.ag
LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS jilin FROM A_ALL WHERE A_CITY_NAME='吉林' GROUP BY A_AGENCY_SHORT) C18 ON A.a_agency_short=c18.ag
LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS jiangsu FROM A_ALL WHERE A_CITY_NAME='江苏' GROUP BY A_AGENCY_SHORT) C19 ON A.a_agency_short=c19.ag
LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS jiangxi FROM A_ALL WHERE A_CITY_NAME='江西' GROUP BY A_AGENCY_SHORT) C20 ON A.a_agency_short=c20.ag
LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS liaoning FROM A_ALL WHERE A_CITY_NAME='辽宁' GROUP BY A_AGENCY_SHORT) C21 ON A.a_agency_short=c21.ag
LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS qinghai FROM A_ALL WHERE A_CITY_NAME='青海' GROUP BY A_AGENCY_SHORT) C22 ON A.a_agency_short=c22.ag
LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS sichuan FROM A_ALL WHERE A_CITY_NAME='四川' GROUP BY A_AGENCY_SHORT) C23 ON A.a_agency_short=c23.ag
LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS shandong FROM A_ALL WHERE A_CITY_NAME='山东' GROUP BY A_AGENCY_SHORT) C24 ON A.a_agency_short=c24.ag
LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS shanghai FROM A_ALL WHERE A_CITY_NAME='上海' GROUP BY A_AGENCY_SHORT) C25 ON A.a_agency_short=c25.ag
LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS shanxi FROM A_ALL WHERE A_CITY_NAME='山西' GROUP BY A_AGENCY_SHORT) C26 ON A.a_agency_short=c26.ag
LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS shanxi1 FROM A_ALL WHERE A_CITY_NAME='陕西' GROUP BY A_AGENCY_SHORT) C27 ON A.a_agency_short=c27.ag
LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS tianjin FROM A_ALL WHERE A_CITY_NAME='天津' GROUP BY A_AGENCY_SHORT) C28 ON A.a_agency_short=c28.ag
LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS taiwan FROM A_ALL WHERE A_CITY_NAME='台湾' GROUP BY A_AGENCY_SHORT) C29 ON A.a_agency_short=c29.ag
LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS xinjiang FROM A_ALL WHERE A_CITY_NAME='新疆' GROUP BY A_AGENCY_SHORT) C30 ON A.a_agency_short=c30.ag
LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS xizang FROM A_ALL WHERE A_CITY_NAME='西藏' GROUP BY A_AGENCY_SHORT) C31 ON A.a_agency_short=c31.ag
LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS yunnan FROM A_ALL WHERE A_CITY_NAME='云南' GROUP BY A_AGENCY_SHORT) C32 ON A.a_agency_short=c32.ag
LEFT JOIN (SELECT A_AGENCY_SHORT as ag,SUM(A_CUS_NUM) AS zhejiang FROM A_ALL WHERE A_CITY_NAME='浙江' GROUP BY A_AGENCY_SHORT) C33 ON A.a_agency_short=c33.ag GROUP BY A.A_AGENCY_SHORT, c1.qita, C2.anhui, c3.aomen, c4.beijing, c5.chongqing, c6.fujian, c7.guangdong, c8.gansu, c9.guangxi, c10.guizhou, c11.hebei, c12.hubei, c13.xianggang, c14.heilongjiang, c15.hainan,
c16.henan, c17.hunan, c18.jilin, c19.jiangsu, c20.jiangxi, c21.liaoning, c22.qinghai, c23.sichuan, c24.shandong, c25.shanghai, c26.shanxi, c27.shanxi1, c28.tianjin, c29.taiwan, c30.xinjiang, c31.xizang, c32.yunnan, c33.zhejiang