我的一张表 有name,time,id三列其中name和time都是可以重复的.time代表天,每一天,可以有多个name.我现在需要统计当天,有多少个name,及该name在该月一共有多少个,需要在一张表里
id name time rowid
1 111 2010-8-11 1 AAAwnCAAEAAB2xVAAA
2 111 2010-8-10 2 AAAwnCAAEAAB2xVAAB
3 111 2010-8-10 3 AAAwnCAAEAAB2xVAAC
4 111 2010-8-3 4 AAAwnCAAEAAB2xVAAD
5 111 2010-8-11 5 AAAwnCAAEAAB2xVAAE
6 222 2010-8-3 6 AAAwnCAAEAAB2xVAAF
7 222 2010-8-10 7 AAAwnCAAEAAB2xVAAG
8 222 2010-8-11 8 AAAwnCAAEAAB2xVAAH
9 222 2010-8-12 9 AAAwnCAAEAAB2xVAAI
10 222 2010-8-1 10 AAAwnCAAEAAB2xVAAJ
11 333 2010-8-9 11 AAAwnCAAEAAB2xVAAK
12 333 2010-8-11 12 AAAwnCAAEAAB2xVAAL
13 333 2010-8-12 13 AAAwnCAAEAAB2xVAAM
14 333 2010-8-9 14 AAAwnCAAEAAB2xVAAN
15 333 2010-8-10 15 AAAwnCAAEAAB2xVAAO
16 333 2010-8-9 16 AAAwnCAAEAAB2xVAAP
17 333 2010-8-2 17 AAAwnCAAEAAB2xVAAQ
18 222 2010-8-31 18 AAAwnCAAEAAB2xVAAR比如按日查是
select name,count(name) from mobiletest t where to_char(time,'yyyy-MM-dd')='2010-08-10' group by name
结果是1 333 1
2 222 1
3 111 2按月查
select name,count(name) from mobiletest t where to_char(time,'yyyy-MM')='2010-08' group by name
结果是
1 333 7
2 222 6
3 111 5现在需要得到这样的结果
name 当天累计 当月累计
1 333 1 7
2 222 1 6
3 111 2 5如何得到呢?急急急....建表语句在下面
-- Create table
create table MOBILETEST
(
NAME VARCHAR2(32),
TIME DATE,
ID NUMBER not null
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table MOBILETEST
add constraint AA primary key (ID)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
id name time rowid
1 111 2010-8-11 1 AAAwnCAAEAAB2xVAAA
2 111 2010-8-10 2 AAAwnCAAEAAB2xVAAB
3 111 2010-8-10 3 AAAwnCAAEAAB2xVAAC
4 111 2010-8-3 4 AAAwnCAAEAAB2xVAAD
5 111 2010-8-11 5 AAAwnCAAEAAB2xVAAE
6 222 2010-8-3 6 AAAwnCAAEAAB2xVAAF
7 222 2010-8-10 7 AAAwnCAAEAAB2xVAAG
8 222 2010-8-11 8 AAAwnCAAEAAB2xVAAH
9 222 2010-8-12 9 AAAwnCAAEAAB2xVAAI
10 222 2010-8-1 10 AAAwnCAAEAAB2xVAAJ
11 333 2010-8-9 11 AAAwnCAAEAAB2xVAAK
12 333 2010-8-11 12 AAAwnCAAEAAB2xVAAL
13 333 2010-8-12 13 AAAwnCAAEAAB2xVAAM
14 333 2010-8-9 14 AAAwnCAAEAAB2xVAAN
15 333 2010-8-10 15 AAAwnCAAEAAB2xVAAO
16 333 2010-8-9 16 AAAwnCAAEAAB2xVAAP
17 333 2010-8-2 17 AAAwnCAAEAAB2xVAAQ
18 222 2010-8-31 18 AAAwnCAAEAAB2xVAAR比如按日查是
select name,count(name) from mobiletest t where to_char(time,'yyyy-MM-dd')='2010-08-10' group by name
结果是1 333 1
2 222 1
3 111 2按月查
select name,count(name) from mobiletest t where to_char(time,'yyyy-MM')='2010-08' group by name
结果是
1 333 7
2 222 6
3 111 5现在需要得到这样的结果
name 当天累计 当月累计
1 333 1 7
2 222 1 6
3 111 2 5如何得到呢?急急急....建表语句在下面
-- Create table
create table MOBILETEST
(
NAME VARCHAR2(32),
TIME DATE,
ID NUMBER not null
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table MOBILETEST
add constraint AA primary key (ID)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
select 1 id,'333' name,1 daynum from dual
union all
select 2 id,'222' name,1 daynum from dual
union all
select 3 id,'111' name,2 daynum from dual
),b as(
select 1 id,'333' name,7 monthnum from dual
union all
select 2 id,'222' name,6 monthnum from dual
union all
select 3 id,'111' name,5 monthnum from dual
)
select a.id,a.name,daynum,monthnum from a,b where a.name = b.name
select
NAME,
count(decode(TIME,to_date('2010-08-03','yyyy-mm-dd'),1))
,count(decode(to_char(time,'yyyy-MM'),'2010-08',1))
from mobiletest t GROUP BY NAME