初用oracle,pl/sql语句如下:select '2012-09-18' 日期,lyjzm,TO_CHAR(sum(jzll),'99999999999.9') 井排液量,TO_CHAR(sum(jzyl),'99999999999.9') 井排油量
from ys_dhb12 t where zm='利津联合站'
and
((rq=to_date('2012-09-17','yyyy-MM-dd') and cjsj in('10:00','12:00','14:00','16:00','18:00','20:00','22:00')) or (rq=to_date('2012-09-18','yyyy-MM-dd') and cjsj in('0:00','2:00','4:00','6:00','8:00')))
and (lyjzm='注采站' or exists(select 1 from YS_DHA14 where zm=t.lyjzm and zb='11')) group by lyjzm这个是查询某一天的产量,我想改成循环查询当月每天的产量,请问如何改啊
解决方案 »
- 十万火急,“Oracle”和“SQLServer”数据库对接问题,期待大师
- 关于Oracle中存储过程的问题,执行总报错,有时编译不成功,使用工具是PLSQL
- 如何在本机连接服务器上的Oracle实例?
- Enterprise Manager Console 连接数据库自动消失了。。。
- 关于PL/sql的问题
- 关于序列的问题
- 數據庫加載失敗
- 可以在嵌入式SQL中添加作业吗?
- 刚在自己机器上装了oracle!还要做什么才能导入一些表啊?
- 面试题大家讨论下
- 如何把同一个表中不同条件的聚合函数放到一起行显示啊?
- select min(id) , (select min(id) from B) from A
begin
select i 日期,lyjzm,TO_CHAR(sum(jzll),'99999999999.9') 井排液量,TO_CHAR(sum(jzyl),'99999999999.9') 井排油量
from ys_dhb12 t where zm='利津联合站'
and
((rq=to_date(i-1,'yyyy-MM-dd') and cjsj in('10:00','12:00','14:00','16:00','18:00','20:00','22:00')) or (rq=to_date(i,'yyyy-MM-dd') and cjsj in('0:00','2:00','4:00','6:00','8:00')))
and (lyjzm='注采站' or exists(select 1 from YS_DHA14 where zm=t.lyjzm and zb='11')) group by lyjzm
end;
SELECT TO_CHAR(rq,'yyyy-mm-dd') --'2012-09-18' 日期,
lyjzm,
TO_CHAR(SUM(jzll),'99999999999.9') 井排液量,
TO_CHAR(SUM(jzyl),'99999999999.9') 井排油量
FROM ys_dhb12 t
WHERE zm ='利津联合站'
/*
AND ((rq =to_date('2012-09-17','yyyy-MM-dd')
AND cjsj IN('10:00','12:00','14:00','16:00','18:00','20:00','22:00'))
OR (rq =to_date('2012-09-18','yyyy-MM-dd')
AND cjsj IN('0:00','2:00','4:00','6:00','8:00')))
*/
AND to_date(rq
|| cjsj
|| ':00','yyyy-mm-dd hh24:mi:ss') BETWEEN to_date(rq
|| '10:00:00','yyyy-mm-dd hh24:mi:ss')
AND to_date((rq +1)
|| '08:00:00','yyyy-mm-dd hh24:mi:ss')--取值范围=当天10点至第二天8点
and mod(to_char(to_date(cjsj,'hh24:mi'),'hh'),2) = 0 --每两小时的整点
and to_char(rq,'yyyy-mm') = '2012-09'--指定月份
AND (lyjzm='注采站'
OR EXISTS
(SELECT 1 FROM YS_DHA14 WHERE zm=t.lyjzm AND zb='11'
))
GROUP BY lyjzm
order by rq --按日期排序
while 条件 loop
......
end loop;
用last_date(sysdate)得到本月的最后一天,
然后写个循环就好
--按照你的sql表达的意思就是,当天的10:00以后-到第二天8点之前 每两个小时的总量算做一天的
--不用循环呀兄弟,找出分组规律就ok了哦
SELECT TO_CHAR(RQ + 14 / 24, 'YYYY-MM-DD') 日期,
--这个分组在rq的基础上加了14个小时,那么自然前一天的10:00之后的数据显示的就是下一天
LYJZM,
TO_CHAR(SUM(JZLL), '99999999999.9') 井排液量,
TO_CHAR(SUM(JZYL), '99999999999.9') 井排油量
FROM YS_DHB12 T
WHERE ZM = '利津联合站'
/*
AND ((RQ = TO_DATE('2012-09-17', 'yyyy-MM-dd') AND
CJSJ IN
('10:00', '12:00', '14:00', '16:00', '18:00', '20:00', '22:00')) OR
(RQ = TO_DATE('2012-09-18', 'yyyy-MM-dd') AND
CJSJ IN ('0:00', '2:00', '4:00', '6:00', '8:00')))
*/
--下面这个条件就是 一个月的数据 只取偶数小时的
AND RQ BETWEEN TO_DATE('2012-08-31 10:00:00', 'YYYY-MM-DD HH24:MI:SS') AND
TO_DATE('2012-10-01 08:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND MOD(TO_NUMBER(TO_CHAR(RQ, 'HH24')), 2) = 0 AND (LYJZM = '注采站' OR EXISTS (SELECT 1
FROM YS_DHA14
WHERE ZM = T.LYJZM
AND ZB = '11'))
GROUP BY TO_CHAR(RQ + 14 / 24, 'YYYY-MM-DD'), LYJZM;
select
to_char(trunc(to_date(to_char(rq,'yyyy-MM-dd') ||' ' || cjsj,'yyyy-MM-dd HH24:MI')+14/24,'dd'),'yyyy-MM-dd') 日期
,lyjzm,TO_CHAR(sum(jzll),'99999999999.9') 井排液量,TO_CHAR(sum(jzyl),'99999999999.9') 井排油量
from ys_dhb12 t where zm='滨五站'
and
to_date(to_char(RQ,'yyyy-mm-dd')||' '||cjsj,'yyyy-mm-dd hh24:mi')>= TO_DATE('2012-08-31 10:00', 'YYYY-MM-DD HH24:MI') AND to_date(to_char(RQ,'yyyy-mm-dd')||' '||cjsj,'yyyy-mm-dd hh24:mi')<=
TO_DATE('2012-10-01 08:00', 'YYYY-MM-DD HH24:MI')
and (lyjzm='注采站' or exists(select 1 from YS_DHA14 where zm=t.lyjzm and zb='11')) group by lyjzm
,trunc(to_date(to_char(rq,'yyyy-MM-dd') ||' ' || cjsj,'yyyy-MM-dd HH24:MI')+14/24,'dd')
order by trunc(to_date(to_char(rq,'yyyy-MM-dd') ||' ' || cjsj,'yyyy-MM-dd HH24:MI')+14/24,'dd') desc不过我还有一个问题,这是另一个SQL语句:
select
to_char(trunc(to_date(to_char(rq,'yyyy-MM-dd') ||' ' || cjsj,'yyyy-MM-dd HH24:MI')+14/24,'dd'),'yyyy-MM-dd') 日期
,TO_CHAR(sum(jzll),'99999999999.9') 站处理液量,TO_CHAR(sum(jzyl),'99999999999.9') 站处理油量 from ys_dhb12 t where zm='滨五站'
and
to_date(to_char(RQ,'yyyy-mm-dd')||' '||cjsj,'yyyy-mm-dd hh24:mi')>= TO_DATE('2012-08-31 10:00', 'YYYY-MM-DD HH24:MI') AND to_date(to_char(RQ,'yyyy-mm-dd')||' '||cjsj,'yyyy-mm-dd hh24:mi')<=
TO_DATE('2012-10-01 08:00', 'YYYY-MM-DD HH24:MI')
and
SFJCLLC='1'
group by trunc(to_date(to_char(rq,'yyyy-MM-dd') ||' ' || cjsj,'yyyy-MM-dd HH24:MI')+14/24,'dd')
order by trunc(to_date(to_char(rq,'yyyy-MM-dd') ||' ' || cjsj,'yyyy-MM-dd HH24:MI')+14/24,'dd') desc我想把它们合并到一起显示,就是排列在一行,怎么能实现啊?
'yyyy-MM-dd HH24:MI') + 14 / 24,
'dd'),
'yyyy-MM-dd') 日期,
LYJZM,
TO_CHAR(SUM(JZLL), '99999999999.9') 井排液量,
TO_CHAR(SUM(JZYL), '99999999999.9') 井排油量,
TO_CHAR(SUM(SUM(JZLL))
OVER(PARTITION BY
TO_CHAR(TRUNC(TO_DATE(TO_CHAR(RQ, 'yyyy-MM-dd') || ' ' || CJSJ,
'yyyy-MM-dd HH24:MI') + 14 / 24,
'dd'),
'yyyy-MM-dd')),
'99999999999.9') 站处理液量,
TO_CHAR(SUM(SUM(JZYL))
OVER(PARTITION BY
TO_CHAR(TRUNC(TO_DATE(TO_CHAR(RQ, 'yyyy-MM-dd') || ' ' || CJSJ,
'yyyy-MM-dd HH24:MI') + 14 / 24,
'dd'),
'yyyy-MM-dd')),
'99999999999.9') 站处理油量
FROM YS_DHB12 T
WHERE ZM = '滨五站'
AND TO_DATE(TO_CHAR(RQ, 'yyyy-mm-dd') || ' ' || CJSJ,
'yyyy-mm-dd hh24:mi') >=
TO_DATE('2012-08-31 10:00', 'YYYY-MM-DD HH24:MI')
AND TO_DATE(TO_CHAR(RQ, 'yyyy-mm-dd') || ' ' || CJSJ,
'yyyy-mm-dd hh24:mi') <=
TO_DATE('2012-10-01 08:00', 'YYYY-MM-DD HH24:MI')
AND (LYJZM = '注采站' OR EXISTS (SELECT 1
FROM YS_DHA14
WHERE ZM = T.LYJZM
AND ZB = '11'))
GROUP BY LYJZM,
TRUNC(TO_DATE(TO_CHAR(RQ, 'yyyy-MM-dd') || ' ' || CJSJ,
'yyyy-MM-dd HH24:MI') + 14 / 24,
'dd')
ORDER BY TRUNC(TO_DATE(TO_CHAR(RQ, 'yyyy-MM-dd') || ' ' || CJSJ,
'yyyy-MM-dd HH24:MI') + 14 / 24,
'dd') DESC
--我的颜色
SELECT TO_CHAR(TRUNC(TO_DATE(TO_CHAR(RQ, 'yyyy-MM-dd') || ' ' || CJSJ,
'yyyy-MM-dd HH24:MI') + 14 / 24,
'dd'),
'yyyy-MM-dd') 日期,
LYJZM,
TO_CHAR(SUM(JZLL), '99999999999.9') 井排液量,
TO_CHAR(SUM(JZYL), '99999999999.9') 井排油量,
TO_CHAR(SUM(SUM(JZLL))
OVER(PARTITION BY
TO_CHAR(TRUNC(TO_DATE(TO_CHAR(RQ, 'yyyy-MM-dd') || ' ' || CJSJ,
'yyyy-MM-dd HH24:MI') + 14 / 24,
'dd'),
'yyyy-MM-dd')),
'99999999999.9') 站处理液量,
TO_CHAR(SUM(SUM(JZYL))
OVER(PARTITION BY
TO_CHAR(TRUNC(TO_DATE(TO_CHAR(RQ, 'yyyy-MM-dd') || ' ' || CJSJ,
'yyyy-MM-dd HH24:MI') + 14 / 24,
'dd'),
'yyyy-MM-dd')),
'99999999999.9') 站处理油量
FROM YS_DHB12 T
WHERE ZM = '滨五站'
AND TO_DATE(TO_CHAR(RQ, 'yyyy-mm-dd') || ' ' || CJSJ,
'yyyy-mm-dd hh24:mi') >=
TO_DATE('2012-08-31 10:00', 'YYYY-MM-DD HH24:MI')
AND TO_DATE(TO_CHAR(RQ, 'yyyy-mm-dd') || ' ' || CJSJ,
'yyyy-mm-dd hh24:mi') <=
TO_DATE('2012-10-01 08:00', 'YYYY-MM-DD HH24:MI')
AND (LYJZM = '注采站' OR EXISTS (SELECT 1
FROM YS_DHA14
WHERE ZM = T.LYJZM
AND ZB = '11'))
GROUP BY LYJZM,
TRUNC(TO_DATE(TO_CHAR(RQ, 'yyyy-MM-dd') || ' ' || CJSJ,
'yyyy-MM-dd HH24:MI') + 14 / 24,
'dd')
ORDER BY TRUNC(TO_DATE(TO_CHAR(RQ, 'yyyy-MM-dd') || ' ' || CJSJ,
'yyyy-MM-dd HH24:MI') + 14 / 24,
'dd') DESC
谢谢啊,我用下面这个语句实现了,我就是为了把一个单位每天的各项统计数据在一行里显示:select * --日期,井排液量,井排油量,站处理油量 from
(select
to_char(trunc(to_date(to_char(rq,'yyyy-MM-dd') ||' ' || cjsj,'yyyy-MM-dd HH24:MI')+14/24,'dd'),'yyyy-MM-dd') 日期
,TO_CHAR(sum(jzll),'99999999999.9') 井排液量,TO_CHAR(sum(jzyl),'99999999999.9') 井排油量
from ys_dhb12 t where zm='滨五站'
and
to_date(to_char(RQ,'yyyy-mm-dd')||' '||cjsj,'yyyy-mm-dd hh24:mi')>= TO_DATE('2012-08-31 10:00', 'YYYY-MM-DD HH24:MI') AND to_date(to_char(RQ,'yyyy-mm-dd')||' '||cjsj,'yyyy-mm-dd hh24:mi')<=
TO_DATE('2012-10-01 08:00', 'YYYY-MM-DD HH24:MI')
and (lyjzm='注采站' or exists(select 1 from YS_DHA14 where zm=t.lyjzm and zb='11'))
group by
trunc(to_date(to_char(rq,'yyyy-MM-dd') ||' ' || cjsj,'yyyy-MM-dd HH24:MI')+14/24,'dd')
order by trunc(to_date(to_char(rq,'yyyy-MM-dd') ||' ' || cjsj,'yyyy-MM-dd HH24:MI')+14/24,'dd') desc
)
a left join
(
select
to_char(trunc(to_date(to_char(rq,'yyyy-MM-dd') ||' ' || cjsj,'yyyy-MM-dd HH24:MI')+14/24,'dd'),'yyyy-MM-dd') 日期
,TO_CHAR(sum(jzyl),'99999999999.9') 站处理油量 from ys_dhb12 t where zm='滨五站'
and
to_date(to_char(RQ,'yyyy-mm-dd')||' '||cjsj,'yyyy-mm-dd hh24:mi')>= TO_DATE('2012-08-31 10:00', 'YYYY-MM-DD HH24:MI') AND to_date(to_char(RQ,'yyyy-mm-dd')||' '||cjsj,'yyyy-mm-dd hh24:mi')<=
TO_DATE('2012-10-01 08:00', 'YYYY-MM-DD HH24:MI')
and
SFJCLLC='1'
group by trunc(to_date(to_char(rq,'yyyy-MM-dd') ||' ' || cjsj,'yyyy-MM-dd HH24:MI')+14/24,'dd')
order by trunc(to_date(to_char(rq,'yyyy-MM-dd') ||' ' || cjsj,'yyyy-MM-dd HH24:MI')+14/24,'dd') desc
)
b
on a.日期=b.日期
但是,第一句用*可以执行,用具体的字段名,如“日期,井排液量,井排油量,站处理油量”就报错,改成英文的也不行,不知是什么原因?
解决方法:把这俩【日期】改成不一样的名字。
--还有一种解决方法,把表别名加上去
select a.日期,a.井排液量,a.井排油量,b.站处理油量