语句是这样的:
select count(1) as count,'25岁及以下' as tab from MV_UT_MEMBER where (sysdate-birthday)/365>=0 and (sysdate-birthday)/365<=25 and INSTR(BELONGDZB,'001.001.032.092')>0
union select count(1) as count,'26到35岁' as tab from MV_UT_MEMBER where (sysdate-birthday)/365>=26 and (sysdate-birthday)/365<=35 and INSTR(BELONGDZB,'001.001.032.092')>0
union select count(1) as count,'36到45岁' as tab from MV_UT_MEMBER where (sysdate-birthday)/365>=36 and (sysdate-birthday)/365<=45 and INSTR(BELONGDZB,'001.001.032.092')>0
union select count(1) as count,'46到54岁' as tab from MV_UT_MEMBER where (sysdate-birthday)/365>=46 and (sysdate-birthday)/365<=55 and INSTR(BELONGDZB,'001.001.032.092')>0
union select count(1) as count,'55到59岁' as tab from MV_UT_MEMBER where (sysdate-birthday)/365>=56 and (sysdate-birthday)/365<=59 and INSTR(BELONGDZB,'001.001.032.092')>0
union select count(1) as count,'60岁以上' as tab from MV_UT_MEMBER where (sysdate-birthday)/365>=60 and INSTR(BELONGDZB,'001.001.032.092')>0 order by TAb
MV_UT_MEMBER 表里有50W+的数据,这样查询一次,一条记录就要比较六次.这样算下来就要比较300W次再求和,这样效率很慢啊,有什么可以提高效率的方法吗??
select count(1) as count,'25岁及以下' as tab from MV_UT_MEMBER where (sysdate-birthday)/365>=0 and (sysdate-birthday)/365<=25 and INSTR(BELONGDZB,'001.001.032.092')>0
union select count(1) as count,'26到35岁' as tab from MV_UT_MEMBER where (sysdate-birthday)/365>=26 and (sysdate-birthday)/365<=35 and INSTR(BELONGDZB,'001.001.032.092')>0
union select count(1) as count,'36到45岁' as tab from MV_UT_MEMBER where (sysdate-birthday)/365>=36 and (sysdate-birthday)/365<=45 and INSTR(BELONGDZB,'001.001.032.092')>0
union select count(1) as count,'46到54岁' as tab from MV_UT_MEMBER where (sysdate-birthday)/365>=46 and (sysdate-birthday)/365<=55 and INSTR(BELONGDZB,'001.001.032.092')>0
union select count(1) as count,'55到59岁' as tab from MV_UT_MEMBER where (sysdate-birthday)/365>=56 and (sysdate-birthday)/365<=59 and INSTR(BELONGDZB,'001.001.032.092')>0
union select count(1) as count,'60岁以上' as tab from MV_UT_MEMBER where (sysdate-birthday)/365>=60 and INSTR(BELONGDZB,'001.001.032.092')>0 order by TAb
MV_UT_MEMBER 表里有50W+的数据,这样查询一次,一条记录就要比较六次.这样算下来就要比较300W次再求和,这样效率很慢啊,有什么可以提高效率的方法吗??
解决方案 »
- Oracle,SQL语句中/*+ */是什么技术呢?
- 数据库访问速度过慢~
- c/c++ oci大量数据的插入
- oracle8i导出的dmp文件如何导入oracle9i中?
- 问个简单问题 帮帮忙 多用户怎么向一个表里插值啊
- 请问这样的sql语句应该如何写?
- 关于存储过程的问题,关于传值的问题,一个varchar的变量,放在update 表名 set a=变量 where a_id in(varchar变量)会出错!
- 9i登录问题??急!!!
- 求一Sql语句,如何得到本周,本月及本年的两个日期
- 请问, Oracle 有没有图形界面,可以在里面进行对数据库的管理,如删除,建立表等。
- 如何导出Oracle用户时,导出指定表【test用户中有个t_log表,我想导出除t_log外的所有表】
- 没有备份的情况下对control进行了误删除,请问如何恢复
--改成下面的形式 并给birthday建索引
birthday between sysdate-365*25 and sysdate
----INSTR(BELONGDZB,'001.001.032.092') 建个函数索引 ---我的 你试试
select
case when (sysdate-birthday)/365>=0 and (sysdate-birthday)/365<=25 then '25岁及以下'
when (sysdate-birthday)/365>=26 and (sysdate-birthday)/365<=35 then '26到35岁'
when (sysdate-birthday)/365>=36 and (sysdate-birthday)/365<=45 then '36到45岁'
when (sysdate-birthday)/365>=46 and (sysdate-birthday)/365<=55 then '46到54岁'
when (sysdate-birthday)/365>=56 and (sysdate-birthday)/365<=59 then '55到59岁'
when (sysdate-birthday)/365>=60 then '60岁以上' end,
count(1) as cnt
from MV_UT_MEMBER
where INSTR(BELONGDZB,'001.001.032.092')>0---你的 把union 改成union allselect '25岁及以下' as tab,count(1) as cnt from MV_UT_MEMBER
where (sysdate-birthday)/365>=0 and (sysdate-birthday)/365<=25 and INSTR(BELONGDZB,'001.001.032.092')>0
union all
select '26到35岁' as tab,count(1) as cnt from MV_UT_MEMBER where (sysdate-birthday)/365>=26 and (sysdate-birthday)/365<=35 and INSTR(BELONGDZB,'001.001.032.092')>0
union all
select '36到45岁' as tab,count(1) as cnt from MV_UT_MEMBER where (sysdate-birthday)/365>=36 and (sysdate-birthday)/365<=45 and INSTR(BELONGDZB,'001.001.032.092')>0
union all
select '46到54岁' as tab,count(1) as cnt from MV_UT_MEMBER where (sysdate-birthday)/365>=46 and (sysdate-birthday)/365<=55 and INSTR(BELONGDZB,'001.001.032.092')>0
union all
select '55到59岁' as tab,count(1) as cnt from MV_UT_MEMBER where (sysdate-birthday)/365>=56 and (sysdate-birthday)/365<=59 and INSTR(BELONGDZB,'001.001.032.092')>0
union all
select '60岁以上' as tab,count(1) as cnt from MV_UT_MEMBER
where (sysdate-birthday)/365>=60 and INSTR(BELONGDZB,'001.001.032.092')>0
case when (sysdate-birthday)/365>=0 and (sysdate-birthday)/365<=25 then '25岁及以下'
when (sysdate-birthday)/365>=26 and (sysdate-birthday)/365<=35 then '26到35岁'
when (sysdate-birthday)/365>=36 and (sysdate-birthday)/365<=45 then '36到45岁'
when (sysdate-birthday)/365>=46 and (sysdate-birthday)/365<=55 then '46到54岁'
when (sysdate-birthday)/365>=56 and (sysdate-birthday)/365<=59 then '55到59岁'
when (sysdate-birthday)/365>=60 then '60岁以上' end,
count(1) as cnt
from MV_UT_MEMBER
where INSTR(BELONGDZB,'001.001.032.092')>0
group by case when (sysdate-birthday)/365>=0 and (sysdate-birthday)/365<=25 then '25岁及以下'
when (sysdate-birthday)/365>=26 and (sysdate-birthday)/365<=35 then '26到35岁'
when (sysdate-birthday)/365>=36 and (sysdate-birthday)/365<=45 then '36到45岁'
when (sysdate-birthday)/365>=46 and (sysdate-birthday)/365<=55 then '46到54岁'
when (sysdate-birthday)/365>=56 and (sysdate-birthday)/365<=59 then '55到59岁'
when (sysdate-birthday)/365>=60 then '60岁以上' end
SELECT Sum(
CASE WHEN (sysdate-birthday)/365>=0 and (sysdate-birthday)/365<=25 THEN 1
WHEN (sysdate-birthday)/365>=26 and (sysdate-birthday)/365<=35 THEN 1
WHEN (sysdate-birthday)/365>=36 and (sysdate-birthday)/365<=45 THEN 1
WHEN (sysdate-birthday)/365>=46 and (sysdate-birthday)/365<=55 THEN 1
WHEN (sysdate-birthday)/365>=56 and (sysdate-birthday)/365<=59 THEN 1
WHEN (sysdate-birthday)/365>=60 THEN 1
END) as count,
CASE WHEN (sysdate-birthday)/365>=0 and (sysdate-birthday)/365<=25 THEN '25岁及以下'
WHEN (sysdate-birthday)/365>=26 and (sysdate-birthday)/365<=35 THEN '26到35岁'
WHEN (sysdate-birthday)/365>=36 and (sysdate-birthday)/365<=45 THEN '36到45岁'
WHEN (sysdate-birthday)/365>=46 and (sysdate-birthday)/365<=55 THEN '46到54岁'
WHEN (sysdate-birthday)/365>=56 and (sysdate-birthday)/365<=59 THEN '55到59岁'
WHEN (sysdate-birthday)/365>=60 THEN '60岁以上'
END as tab
from MV_UT_MEMBER
where INSTR(BELONGDZB,'001.001.032.092')>0
GROUP BY (
CASE WHEN (sysdate-birthday)/365>=0 and (sysdate-birthday)/365<=25 THEN '25岁及以下'
WHEN (sysdate-birthday)/365>=26 and (sysdate-birthday)/365<=35 THEN '26到35岁'
WHEN (sysdate-birthday)/365>=36 and (sysdate-birthday)/365<=45 THEN '36到45岁'
WHEN (sysdate-birthday)/365>=46 and (sysdate-birthday)/365<=55 THEN '46到54岁'
WHEN (sysdate-birthday)/365>=56 and (sysdate-birthday)/365<=59 THEN '55到59岁'
WHEN (sysdate-birthday)/365>=60 THEN '60岁以上'
END )
建个基于函数的索引试试
INSTR(BELONGDZB,'001.001.032.092')这个不是必须的,我删除这个条件后查询只快了0.1秒