语句是这样的:
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次再求和,这样效率很慢啊,有什么可以提高效率的方法吗??
--改成下面的形式 并给birthday建索引
birthday between sysdate-365*25 and sysdate
...
sum(select case when birthday<=sysdate-60*365 then 1 else 0 end) col6
from MV_UT_MEMBER where INSTR(BELONGDZB,'001.001.032.092')
select col1,'25岁及以下' as tab from a
union all
....
union all
select col6,'60岁以上' as tab from a;
...
sum(select case when birthday<=sysdate-60*365 then 1 else 0 end) col6
from MV_UT_MEMBER where INSTR(BELONGDZB,'001.001.032.092')
select col1 count,'25岁及以下' as tab from a
union all
....
union all
select col6 count,'60岁以上' as tab from a;
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 )
--以回复
http://topic.csdn.net/u/20101206/16/199b8a19-c490-4294-a66a-079406156623.html