Oracle关联查询问题?

heyingss 2013-05-21 09:37:14
查询语句如下:
select to_char(t1.dt,'yyyy-mm-dd hh24:mi') as c1705_datetime,t2.c0003_stcode,t2.c0007_pcode
,t2.c0001_item_code
,round(avg(case c.c1705_qcode when 0 then c.c1705_value else null end ),3)
,0 from

(
select a.c0003_stcode,a.c0007_pcode
,b.c0001_item_code,b.c0008_pid

from t0007_substation a
inner join t0008_ai b on
a.c0007_substation_id in (select * from table(strtab(364)))
and a.c0007_substation_id =b.c0007_substation_id
inner join t0031_ap_code d
on a.c0003_year=d.c0003_year
and a.c0003_stcode=d.c0003_stcode
and a.c0007_pcode=d.c0031_pcode
and b.c0001_item_code=101
) t2
left join t1705_hisdata_1h c
on c.c1705_datetime>=to_date('2013-05-19 00:00:00','yyyy-mm-dd hh24:mi:ss')
and c.c1705_datetime<=to_date('2013-05-19 23:59:59','yyyy-mm-dd hh24:mi:ss')
and t2.c0008_pid=c.c0008_pid
right join
(SELECT to_date('2013-05-19 00:00:00','yyyy-MM-dd hh24:mi:ss' )+LEVEL/24-1/24 dt
From dual CONNECT BY LEVEL <=TRUNC(to_date('2013-05-19 23:59:59', 'yyyy-MM-dd hh24:mi:ss')+1-to_date('2013-05-19 00:00:00', 'yyyy-MM-dd hh24:mi:ss'))*24
)t1 on t1.dt=c.c1705_datetime where t1.dt<=to_date('2013-05-19 23:59:59','yyyy-mm-dd hh24:mi:ss')

group by t1.dt,t2.c0003_stcode,t2.c0007_pcode,t2.c0001_item_code
order by dt
查询结果如下列表:
------------------------------------
c1705_datetime c0003_stcode c0007_pcode c0001_item_code avg
1 2013-05-19 00:00 2 2013-05-19 01:00
3 2013-05-19 02:00
4 2013-05-19 03:00
5 2013-05-19 04:00
6 2013-05-19 05:00
7 2013-05-19 06:00
8 2013-05-19 07:00
9 2013-05-19 08:00
10 2013-05-19 09:00
11 2013-05-19 10:00 370102 218 101
12 2013-05-19 11:00 370102 218 101 0.091
13 2013-05-19 12:00 370102 218 101 0.017 14 2013-05-19 13:00 370102 218 101 0.036 15 2013-05-19 14:00 370102 218 101 0.047 16 2013-05-19 15:00 370102 218 101 0.044 17 2013-05-19 16:00 370102 218 101 0.047 18 2013-05-19 17:00 370102 218 101 0.045 19 2013-05-19 18:00 370102 218 101 0.045 20 2013-05-19 19:00 370102 218 101 0.069 21 2013-05-19 20:00 370102 218 101 0.051 22 2013-05-19 21:00 370102 218 101 0.049 23 2013-05-19 22:00 370102 218 101 0.082 24 2013-05-19 23:00 370102 218 101 0.082
---------------------------------------------
我实现的是和时间表关联,如果没数据也显示时间列。但是我想没数据时,c0003_stcode c0007_pcode c0001_item_code也显示出来,avg显示为空,就象如下样式:
c1705_datetime c0003_stcode c0007_pcode c0001_item_code avg
11 2013-05-19 10:00 370102 218 101
请问关联SQL语句怎么写?
...全文
24 点赞 收藏 回复
写回复
回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
相关推荐
发帖
Oracle
创建于2007-09-28

1.6w+

社区成员

Oracle开发相关技术讨论
申请成为版主
帖子事件
创建了帖子
2013-05-21 09:37
社区公告
暂无公告