请高人把sql 性能 效率 优化下。。谢谢。。分全送。。
下面的city_id in(....) 这里数据是不同的,所以要多个sql union all 在一起,
比如sqlA如下:根据city_id 的范围不同还会有 同样的sql 需要union all 在一起。。求优化。
select aa.fixed_net,aa.school_net,aa.differ_net,aa.wlan_net,aa.other_net,aa.city_name,
aa.oneType_user,aa.twoType_user,aa.threeType_user,aa.other_user,
aa.local_rom,aa.out_rom,aa.in_rom ,aa.nation_out_rom,aa.nation_in_rom from
(select
'city_name' = '写死的',
'fixed_net' = sum(case a.id when 1 then a.num else 0 end ) ,
'school_net' = sum(case a.id when 3 then a.num else 0 end ),
'differ_net' = sum(case a.id when 4 then a.num else 0 end ),
'wlan_net' = sum(case a.id when 5 then a.num else 0 end ),
'other_net' = sum(case a.id when 0 then a.num else 0 end ),
'oneType_user' = sum(case b.id when 1 then b.num else 0 end ) ,
'twoType_user' = sum(case b.id when 2 then b.num else 0 end ),
'threeType_user' = sum(case b.id when 3 then b.num else 0 end ),
'other_user' = sum(case b.id when 99 then b.num else 0 end ),
'local_rom' = sum(case c.id when 0 then c.num else 0 end ),
'out_rom' = sum(case c.id when 1 then c.num else 0 end ),
'in_rom' = sum(case c.id when 2 then c.num else 0 end ),
'nation_out_rom' = sum(case c.id when 3 then c.num else 0 end ),
'nation_in_rom' = sum(case c.id when 5 then c.num else 0 end )
from
(select count(user_id) as num,certify_user_type as id
from ${tableName} where certify_user_type not in (2) and city_id in ('001','002','003') //此处的数据是动态的
group by certify_user_type
) a
,
( select count(user_id) as num ,user_type_id as id
from ${tableName}
where user_type_id in (1,2,3,99) and certify_user_type=2 and city_id in ('001','002','003')
group by user_type_id
) b
,
( select count(user_id) as num,romflag as id
from ${tableName} where city_id in ('001','002','003')
group by romflag
) c
) aa
下面的city_id in(....) 这里数据是不同的,所以要多个sql union all 在一起,
比如sqlA如下:根据city_id 的范围不同还会有 同样的sql 需要union all 在一起。。求优化。
select aa.fixed_net,aa.school_net,aa.differ_net,aa.wlan_net,aa.other_net,aa.city_name,
aa.oneType_user,aa.twoType_user,aa.threeType_user,aa.other_user,
aa.local_rom,aa.out_rom,aa.in_rom ,aa.nation_out_rom,aa.nation_in_rom from
(select
'city_name' = '写死的',
'fixed_net' = sum(case a.id when 1 then a.num else 0 end ) ,
'school_net' = sum(case a.id when 3 then a.num else 0 end ),
'differ_net' = sum(case a.id when 4 then a.num else 0 end ),
'wlan_net' = sum(case a.id when 5 then a.num else 0 end ),
'other_net' = sum(case a.id when 0 then a.num else 0 end ),
'oneType_user' = sum(case b.id when 1 then b.num else 0 end ) ,
'twoType_user' = sum(case b.id when 2 then b.num else 0 end ),
'threeType_user' = sum(case b.id when 3 then b.num else 0 end ),
'other_user' = sum(case b.id when 99 then b.num else 0 end ),
'local_rom' = sum(case c.id when 0 then c.num else 0 end ),
'out_rom' = sum(case c.id when 1 then c.num else 0 end ),
'in_rom' = sum(case c.id when 2 then c.num else 0 end ),
'nation_out_rom' = sum(case c.id when 3 then c.num else 0 end ),
'nation_in_rom' = sum(case c.id when 5 then c.num else 0 end )
from
(select count(user_id) as num,certify_user_type as id
from ${tableName} where certify_user_type not in (2) and city_id in ('001','002','003') //此处的数据是动态的
group by certify_user_type
) a
,
( select count(user_id) as num ,user_type_id as id
from ${tableName}
where user_type_id in (1,2,3,99) and certify_user_type=2 and city_id in ('001','002','003')
group by user_type_id
) b
,
( select count(user_id) as num,romflag as id
from ${tableName} where city_id in ('001','002','003')
group by romflag
) c
) aa
解决方案 »
- 学生项目答辩,购物车怎么做?
- 现在用Java的CMS系统是不是很少?
- jsp的if判断出问题!
- 运行不了程序,出现Exception in thread"main"java.lang.NoClassDefFoundError:ServerProgram.java
- 紧急:谁能指导我在本机上调试网上下载的电子商务网站程序
- JSP中能否执行ORACLE中的PL/SQL语句!详细类容如下:
- 如何接收人家访问的是什么域名?如果我的站点绑有多个域名的话......
- [请教]有没有什么办法能让web页面利用ad用户帐户自动登录的?
- 求赵君老师JAVA web33天 开发类库 开发文档 开发工具
- 使用smartupload上传文件,当文件大于30m时出现错误,怎么解决???
- spring注入Action失败
- Tomcat7 不支持这句代码,为什么?
1、 你这个语句,a、b、c 三张表是直接 笛卡尔 连接(无连接条件)?
2、 a、b、c 三张表所选择出来的数据行大约有多少行?
3、 city_id in ('001','002','003') 里面的值大约有多少个?两个稍微复杂点的问题:
1、 ${tableName} 索引是怎么建的?
2 、这个SQL的执行计划是怎么样的?
其次,a b c 3个表的检索相似,没有必要特意写成3个表,直接和在一个就可以了select
'city_name' = '写死的',
'fixed_net' = sum(case certify_user_type when 1 then 1 else 0 end ) ,
'school_net' = sum(case certify_user_type when 3 then 1 else 0 end ),
'differ_net' = sum(case certify_user_type when 4 then 1 else 0 end ),
'wlan_net' = sum(case certify_user_type when 5 then 1 else 0 end ),
'other_net' = sum(case certify_user_type when 0 then 1 else 0 end ),
'oneType_user' = sum(case when user_type_id=1 and certify_user_type<>2 then 1 else 0 end ) ,
'twoType_user' = sum(case when user_type_id=2 and certify_user_type<>2 then b.num else 0 end ),
'threeType_user' = sum(case when user_type_id=3 and certify_user_type<>2 then 1 else 0 end ),
'other_user' = sum(case when user_type_id=99 and certify_user_type<>2 then 1 else 0 end ),
'local_rom' = sum(case romflag when 0 then 1 else 0 end ),
'out_rom' = sum(case romflag when 1 then 1 else 0 end ),
'in_rom' = sum(case romflag when 2 then 1 else 0 end ),
'nation_out_rom' = sum(case romflag when 3 then 1 else 0 end ),
'nation_in_rom' = sum(case romflag when 5 then 1 else 0 end )
from ${tableName}
where city_id in ('001','002','003') --你的动态条件
看看这样的执行计划如何?
你的a表是certify_user_type not in (2),我还以为b表也是一样
你把 certify_user_type<>2 改成 certify_user_type=2