现在有三张表(A,B,C).他们都有相同的字段。 RQ(日期) DW(单位) CH(车号) BM(编码)
---------------------------------------------------
然后我现在要统计它 在'2007-05-20'以后任意一个表中,有过记录的数.
分别按当日 当月 当年 累计统计 CH(车号)的数量.我的想法 是不是应该写一个存储过程啊?用个判断.假如今天这个表中有这个数了.明天的数就不记录.请高手帮忙.统计的结果应该是 当日 当月 当年
-------------------------
---------------------------------------------------
然后我现在要统计它 在'2007-05-20'以后任意一个表中,有过记录的数.
分别按当日 当月 当年 累计统计 CH(车号)的数量.我的想法 是不是应该写一个存储过程啊?用个判断.假如今天这个表中有这个数了.明天的数就不记录.请高手帮忙.统计的结果应该是 当日 当月 当年
-------------------------
解决方案 »
- date类型的数据检索出来以后,如何保留时分秒
- 如何把oracle 9i设置成mts方式?谢谢
- ORACLE:在PL/SQL中 怎样把一个游标赋值给另外一个游标变量?
- 怎样在pl/sql的job里使用alter system 命令
- xp上安装Oracle出错!信息是ORA-00988:missing or invalid password(s)
- 请问各位,配置数据库,管理数据库这些工作是放在程序中完成的吗?
- 简单问题,请指点日期函数问题。
- 为什么FORM BULIDER总是不能连接数据库
- 哪位大虾有《Oracle_8i_PL_SQL高级程序设计》完整下栽,我的只有前7章
- oracle 数据 上次到 db2数据库中
- 为什么同样算法的语句在MS SQL和Oralce中会得到不同的结果?
- oracle 数据库建库
decode(to_char(rq,'yyyy-mm-dd'), to_char(sysdate,'yyyy-mm-dd'), 1, 0) ,
decode(to_char(rq,'yyyy-mm'), to_char(sysdate,'yyyy-mm'), 1, 0),
decode(to_char(rq,'yyyy'), to_char(sysdate,'yyyy'), 1, 0)
from
(select * from a
union all
select * from b
union all
select * from c)
where rq> to_date('yyyy-mm-dd,'2007-05-20')
from (select count(*) as this_days
from (select *
from A
union all
select *
from B
union all
select * from C) tt
where to_char(tt.RQ, 'yyyy-mm-dd') > '2007-05-20'
and to_char(tt.RQ, 'yyyy-mm-dd') = to_char(sysdate, 'yyyy-mm-dd')),
(select count(*) as this_months
from (select *
from A
union all
select *
from B
union all
select * from C) tt
where to_char(RQ, 'yyyy-mm-dd') > '2007-05-20'
and to_char(RQ, 'yyyy-mm') = to_char(sysdate, 'yyyy-mm')),
(select count(*) as this_years
from (select *
from A
union all
select *
from B
union all
select * from C) tt
where to_char(RQ, 'yyyy-mm-dd') > '2007-05-20'
and to_char(RQ, 'yyyy') = to_char(sysdate, 'yyyy'));不知是否满足你的需求 ...
---------------------------------------------------
2007-06-15 141 3326850 P62NT
2007-06-11 141 3326850 P62NT
2007-03-18 144 3326850 P62NT
2006-03-15 341 4822410 C64T
2005-03-05 341 3111750 P62T
2003-07-12 144 3111750 P62T
2007-07-25 897 3111750 P62T
2007-08-15 144 3111750 P62T
2007-07-15 156 3111750 P62T
2003-01-15 141 3327063 P62NT 以上是我从表中取得的一点数据.然后我想得到的结果是: 假如今天是'2007-07-15'(是用户选择的,用户想查看那天的记录就选择那一天)单位 当日 当月 当年
---------------------------
141 0 0 2
144 0 1 2
156 1 1 1
341 0 0 0 --因为这个单位的数 不在'2007-05-20'的日期之后
897 0 1 1
select to_date('2007-06-15','yyyy-mm-dd') as RQ,141 as DW,'3326850' as CH,'P62NT' as BM from dual
union all
select to_date('2007-06-11','yyyy-mm-dd') as RQ,141 as DW,'3326850' as CH,'P62NT' as BM from dual
union all
select to_date('2007-03-18','yyyy-mm-dd') as RQ,144 as DW,'3326850' as CH,'P62NT' as BM from dual
union all
select to_date('2006-03-15','yyyy-mm-dd') as RQ,341 as DW,'4822410' as CH,'C64T' as BM from dual
union all
select to_date('2005-03-05','yyyy-mm-dd') as RQ,341 as DW,'3111750' as CH,'P62T' as BM from dual
union all
select to_date('2003-07-12','yyyy-mm-dd') as RQ,144 as DW,'3111750' as CH,'P62T' as BM from dual
union all
select to_date('2007-07-25','yyyy-mm-dd') as RQ,897 as DW,'3111750' as CH,'P62T' as BM from dual
union all
select to_date('2007-08-15','yyyy-mm-dd') as RQ,144 as DW,'3111750' as CH,'P62T' as BM from dual
union all
select to_date('2007-07-15','yyyy-mm-dd') as RQ,156 as DW,'3111750' as CH,'P62T' as BM from dual
union all
select to_date('2003-01-15','yyyy-mm-dd') as RQ,141 as DW,'3327063' as CH,'P62NT' as BM from dual
)t1,
(select tt.DW,nvl(count(*),0) as days
from(
select to_date('2007-06-15','yyyy-mm-dd') as RQ,141 as DW,'3326850' as CH,'P62NT' as BM from dual
union all
select to_date('2007-06-11','yyyy-mm-dd') as RQ,141 as DW,'3326850' as CH,'P62NT' as BM from dual
union all
select to_date('2007-03-18','yyyy-mm-dd') as RQ,144 as DW,'3326850' as CH,'P62NT' as BM from dual
union all
select to_date('2006-03-15','yyyy-mm-dd') as RQ,341 as DW,'4822410' as CH,'C64T' as BM from dual
union all
select to_date('2005-03-05','yyyy-mm-dd') as RQ,341 as DW,'3111750' as CH,'P62T' as BM from dual
union all
select to_date('2003-07-12','yyyy-mm-dd') as RQ,144 as DW,'3111750' as CH,'P62T' as BM from dual
union all
select to_date('2007-07-25','yyyy-mm-dd') as RQ,897 as DW,'3111750' as CH,'P62T' as BM from dual
union all
select to_date('2007-08-15','yyyy-mm-dd') as RQ,144 as DW,'3111750' as CH,'P62T' as BM from dual
union all
select to_date('2007-07-15','yyyy-mm-dd') as RQ,156 as DW,'3111750' as CH,'P62T' as BM from dual
union all
select to_date('2003-01-15','yyyy-mm-dd') as RQ,141 as DW,'3327063' as CH,'P62NT' as BM from dual
)tt
where to_char(tt.RQ, 'yyyy-mm-dd') = '2007-07-15'
group by tt.DW)d1, (select tt.DW,nvl(count(*),0) as months
from(
select to_date('2007-06-15','yyyy-mm-dd') as RQ,141 as DW,'3326850' as CH,'P62NT' as BM from dual
union all
select to_date('2007-06-11','yyyy-mm-dd') as RQ,141 as DW,'3326850' as CH,'P62NT' as BM from dual
union all
select to_date('2007-03-18','yyyy-mm-dd') as RQ,144 as DW,'3326850' as CH,'P62NT' as BM from dual
union all
select to_date('2006-03-15','yyyy-mm-dd') as RQ,341 as DW,'4822410' as CH,'C64T' as BM from dual
union all
select to_date('2005-03-05','yyyy-mm-dd') as RQ,341 as DW,'3111750' as CH,'P62T' as BM from dual
union all
select to_date('2003-07-12','yyyy-mm-dd') as RQ,144 as DW,'3111750' as CH,'P62T' as BM from dual
union all
select to_date('2007-07-25','yyyy-mm-dd') as RQ,897 as DW,'3111750' as CH,'P62T' as BM from dual
union all
select to_date('2007-08-15','yyyy-mm-dd') as RQ,144 as DW,'3111750' as CH,'P62T' as BM from dual
union all
select to_date('2007-07-15','yyyy-mm-dd') as RQ,156 as DW,'3111750' as CH,'P62T' as BM from dual
union all
select to_date('2003-01-15','yyyy-mm-dd') as RQ,141 as DW,'3327063' as CH,'P62NT' as BM from dual
)tt
where to_char(RQ, 'yyyy-mm') = to_char(to_date('2007-07-15','yyyy-mm-dd'), 'yyyy-mm')
group by tt.DW)m1, (select tt.DW,nvl(count(*),0) as years
from(
select to_date('2007-06-15','yyyy-mm-dd') as RQ,141 as DW,'3326850' as CH,'P62NT' as BM from dual
union all
select to_date('2007-06-11','yyyy-mm-dd') as RQ,141 as DW,'3326850' as CH,'P62NT' as BM from dual
union all
select to_date('2007-03-18','yyyy-mm-dd') as RQ,144 as DW,'3326850' as CH,'P62NT' as BM from dual
union all
select to_date('2006-03-15','yyyy-mm-dd') as RQ,341 as DW,'4822410' as CH,'C64T' as BM from dual
union all
select to_date('2005-03-05','yyyy-mm-dd') as RQ,341 as DW,'3111750' as CH,'P62T' as BM from dual
union all
select to_date('2003-07-12','yyyy-mm-dd') as RQ,144 as DW,'3111750' as CH,'P62T' as BM from dual
union all
select to_date('2007-07-25','yyyy-mm-dd') as RQ,897 as DW,'3111750' as CH,'P62T' as BM from dual
union all
select to_date('2007-08-15','yyyy-mm-dd') as RQ,144 as DW,'3111750' as CH,'P62T' as BM from dual
union all
select to_date('2007-07-15','yyyy-mm-dd') as RQ,156 as DW,'3111750' as CH,'P62T' as BM from dual
union all
select to_date('2003-01-15','yyyy-mm-dd') as RQ,141 as DW,'3327063' as CH,'P62NT' as BM from dual
)tt
where to_char(RQ, 'yyyy') = to_char(to_date('2007-07-15','yyyy-mm-dd'), 'yyyy')
group by tt.DW)y1
where t1.DW = d1.DW(+)
and t1.DW = m1.DW(+)
and t1.DW = y1.DW(+);
=====================result====================================== DW NVL(D1.DAYS,0) NVL(M1.MONTHS,0) NVL(Y1.YEARS,0)
---------- -------------- ---------------- ---------------
141 0 0 2
144 0 0 2
156 1 1 1
341 0 0 0
897 0 1 1
count(decode(to_char(trunc(rq),'yyyy-mm-dd'), to_char(:ad_dte,'yyyy-mm-dd'), 1, 0)) ,
count(decode(to_char(trunc(rq),'yyyy-mm'), to_char(:ad_dte,'yyyy-mm'), 1, 0),
count(decode(to_char(trunc(rq),'yyyy'), to_char(:ad_dte,'yyyy'), 1, 0)
from
(select * from a where trunc(rq,'y') = trunc(:ad_dte,'y')
union all
select * from b where trunc(rq,'y')= trunc(:ad_dte,'y')
union all
select * from c where trunc(rq,'y') = trunc(:ad_dte,'y')
)
group by trunc(rq)
having trunc(rq)= :ad_dte
SUM(DATE_NUM) OVER (PARTITION BY DW) AS "当日",
SUM(MON_NUM) OVER (PARTITION BY DW) AS "当月",
SUM(YEAR_NUM) OVER (PARTITION BY DW) AS "当年",
DW AS "单位"
FROM (
SELECT DECODE(TO_DATE('2007-07-15'),RQ,1,0) DATE_NUM,
DECODE(TO_CHAR(TO_DATE('2007-07-15'),'YYYY'),
TO_CHAR(RQ,'YYYY'),1,0) YEAR_NUM,
DECODE(TO_CHAR(TO_DATE('2007-07-15'),'MM'),
TO_CHAR(RQ,'MM'),1,0) MON_NUM,
RQ, DW
FROM yourtable
)
from (select count(*) as this_days
from (select *
from A
union all
select *
from B
union all
select * from C) tt
where to_char(tt.RQ, 'yyyy-mm-dd') > '2007-05-20'
and to_char(tt.RQ, 'yyyy-mm-dd') = to_char(sysdate, 'yyyy-mm-dd')),
(select count(*) as this_months
from (select *
from A
union all
select *
from B
union all
select * from C) tt
where to_char(RQ, 'yyyy-mm-dd') > '2007-05-20'
and to_char(RQ, 'yyyy-mm') = to_char(sysdate, 'yyyy-mm')),
(select count(*) as this_years
from (select *
from A
union all
select *
from B
union all
select * from C) tt
where to_char(RQ, 'yyyy-mm-dd') > '2007-05-20'
and to_char(RQ, 'yyyy') = to_char(sysdate, 'yyyy'));
create table a (RQ date, DW varchar2(3), CH varchar2(7) , BM varchar2(5));
---------------------------------------------------insert into a values(to_date('2007-06-15','yyyy-MM-dd'),'141','3326850','P62NT');
insert into a values(to_date('2007-06-11','yyyy-MM-dd'),'141','3326850','P62NT');
insert into a values(to_date('2007-03-18','yyyy-MM-dd'),'144','3326850','P62NT');
insert into a values(to_date('2006-03-15','yyyy-MM-dd'),'341','4822410','C64T');
insert into a values(to_date('2005-03-05','yyyy-MM-dd'),'341','3111750','P62T');
insert into a values(to_date('2003-07-12','yyyy-MM-dd'),'144','3111750','P62T');
insert into a values(to_date('2007-07-25','yyyy-MM-dd'),'897','3111750','P62T');
insert into a values(to_date('2007-08-15','yyyy-MM-dd'),'144','3111750','P62T');
insert into a values(to_date('2007-07-15','yyyy-MM-dd'),'156','3111750','P62T');
insert into a values(to_date('2003-01-15','yyyy-MM-dd'),'141','3327063','P62NT');
--'2007-07-15'
select
dw,
sum(decode(to_char(rq,'yyyy-MM-dd'),'2007-07-15',1,0)) D,
sum(decode(to_char(rq,'yyyy-MM'),'2007-07',1,0)) M,
sum(decode(to_char(rq,'yyyy'),'2007',1,0)) Y
from a
--where to_char(rq,'yyyy-MM-dd')>='2007-05-20' --这个条件有用吗
group by dw
order by dw;
DW D M Y
--- ---------- ---------- ----------
141 0 0 2
144 0 0 2
156 1 1 1
341 0 0 0
897 0 1 1另外,楼主给的结果是不对的.对应144这个,当月应该是当年当月,所以月统计是0