表:create table (
id int ,
mid varchar(32), 用户IMEI
plat char(1), 平台
operations char(1), 运营商
time date, 时间
isNew char(1) 是否新用户
)这是我的SQL:select count(mid) total,sum(isnew) new,to_char(time,'yyyy-MM') time
from test_user
where
to_char(time,'yyyy-MM')
between
to_char(to_date('2007-01-12','yyyy-MM-dd'),'yyyy-MM')
and
to_char(to_date('2011-12-31','yyyy-MM-dd'),'yyyy-MM')
group by time
order by time desc结果:
TOTAL NEW TIME
---------- ---------- -------
2 0 2011-12
1 0 2011-11
1 0 2011-10
1 0 2010-12
1 0 2010-11
1 0 2010-10
1 0 2010-09
1 0 2010-09
1 0 2010-09
1 1 2009-12
1 1 2009-11
1 1 2009-10
1 0 2009-09
1 1 2009-09
1 1 2009-09
1 1 2009-08
1 1 2009-08
1 1 2009-08
1 1 2009-08
1 1 2007-11现在要查 总用户 数量 新用户数量 按月查询 但是显示的结果不对啊, 好多月份没累计 比如09年08月分就4个没累计大家看看这个SQL 怎样来做???
id int ,
mid varchar(32), 用户IMEI
plat char(1), 平台
operations char(1), 运营商
time date, 时间
isNew char(1) 是否新用户
)这是我的SQL:select count(mid) total,sum(isnew) new,to_char(time,'yyyy-MM') time
from test_user
where
to_char(time,'yyyy-MM')
between
to_char(to_date('2007-01-12','yyyy-MM-dd'),'yyyy-MM')
and
to_char(to_date('2011-12-31','yyyy-MM-dd'),'yyyy-MM')
group by time
order by time desc结果:
TOTAL NEW TIME
---------- ---------- -------
2 0 2011-12
1 0 2011-11
1 0 2011-10
1 0 2010-12
1 0 2010-11
1 0 2010-10
1 0 2010-09
1 0 2010-09
1 0 2010-09
1 1 2009-12
1 1 2009-11
1 1 2009-10
1 0 2009-09
1 1 2009-09
1 1 2009-09
1 1 2009-08
1 1 2009-08
1 1 2009-08
1 1 2009-08
1 1 2007-11现在要查 总用户 数量 新用户数量 按月查询 但是显示的结果不对啊, 好多月份没累计 比如09年08月分就4个没累计大家看看这个SQL 怎样来做???
select count(mid) total,sum(isnew) new, time
from test_user
where
to_char(time,'yyyy-MM')
between
to_char(to_date('2007-01-12','yyyy-MM-dd'),'yyyy-MM')
and
to_char(to_date('2011-12-31','yyyy-MM-dd'),'yyyy-MM')
group by to_char(time,'yyyy-MM') --这里有问题
order by to_char(time,'yyyy-MM') desc
from test_user
group by time
having
to_char(time,'yyyy-MM')
between
to_char(to_date('2007-01-12','yyyy-MM-dd'),'yyyy-MM')
and
to_char(to_date('2011-12-31','yyyy-MM-dd'),'yyyy-MM')
order by temptime desc我这样改 也一样的结果啊。。
(
select count(mid) total,sum(isnew) new,to_char(time,'yyyy-MM') time
from test_user
where
to_char(time,'yyyy-MM')
between
to_char(to_date('2007-01-12','yyyy-MM-dd'),'yyyy-MM')
and
to_char(to_date('2011-12-31','yyyy-MM-dd'),'yyyy-MM')
group by time )
group by time
order by time desc;
group by time ->group by to_char(time,'yyyy-MM')
group by没弄好
顺便给你优化下
select count(mid) total,sum(isnew) new,to_char(time,'yyyy-MM') time
from test_user
where
time >=date'2007-01-12'
and time<date'2012-01-01'
group by trunc(time,'MM')
order by trunc(time,'MM') desc
为什么要把时间 time>= time < 这样来写 不用BETWEEN AND 呢?? 效率会高吗?你这个ORA-00979: not a GROUP BY expression 会报这个错误shiyiwan 的能出结果
我只是把条件转换了一下
那几个to_char转换完全没有必要
要改成trunc(time,'MM') time