select
h.departid,
c.departname,
a.displayname,
f.title_id,
f.title_name,g.count_media,
count(decode(b.info_status,'3','已发布',null)) status_publish,SUM(
decode(
(select count(*) from e_infodepart t, e_infomation d where t.content_id = b.content_id and t.content_id = d.content_id and d.info_status = 3)
,0,0,round(
(g.count_official /
(select count(*) from e_infodepart t, e_infomation d where t.content_id = b.content_id and t.content_id = d.content_id and d.info_status = 3)
),1
)
)
) as countA from user_t a,e_infomation b ,edepart c,e_title_info e,e_infotitle f,e_target g,e_infodepart h where h.content_id=b.content_id and e.title_id=f.title_id and c.departid =h.departid and
b.content_id=e.content_id and g.targetyear=f.title_id and
crtdate>=to_date(concat(to_char(trunc(sysdate,'mm'),'yyyy-MM-dd'),' 00:00:00'),'yyyy-MM-dd HH24:mi:ss') and
crtdate<=to_date(concat(to_char(last_day(sysdate),'yyyy-MM-dd'),' 23:59:59'),'yyyy-MM-dd HH24:mi:ss') group by h.departid,c.departname,a.displayname,f.title_id ,f.title_name,g.count_official,g.count_media
h.departid,
c.departname,
a.displayname,
f.title_id,
f.title_name,g.count_media,
count(decode(b.info_status,'3','已发布',null)) status_publish,SUM(
decode(
(select count(*) from e_infodepart t, e_infomation d where t.content_id = b.content_id and t.content_id = d.content_id and d.info_status = 3)
,0,0,round(
(g.count_official /
(select count(*) from e_infodepart t, e_infomation d where t.content_id = b.content_id and t.content_id = d.content_id and d.info_status = 3)
),1
)
)
) as countA from user_t a,e_infomation b ,edepart c,e_title_info e,e_infotitle f,e_target g,e_infodepart h where h.content_id=b.content_id and e.title_id=f.title_id and c.departid =h.departid and
b.content_id=e.content_id and g.targetyear=f.title_id and
crtdate>=to_date(concat(to_char(trunc(sysdate,'mm'),'yyyy-MM-dd'),' 00:00:00'),'yyyy-MM-dd HH24:mi:ss') and
crtdate<=to_date(concat(to_char(last_day(sysdate),'yyyy-MM-dd'),' 23:59:59'),'yyyy-MM-dd HH24:mi:ss') group by h.departid,c.departname,a.displayname,f.title_id ,f.title_name,g.count_official,g.count_media
SELECT h.departid,
c.departname,
a.displayname,
f.title_id,
f.title_name,
g.count_media,
COUNT(decode(b.info_status, '3', '已发布', NULL)) status_publish,
SUM( CASE WHEN SUM(CASE WHEN B.INFO_STATUS='3' THEN 1 ELSE 0 END) =0 THEN 0 ELSE
G.count_official /SUM(CASE WHEN B.INFO_STATUS='3' THEN 1 ELSE 0 END) END ) AS counta
FROM user_t a,
e_infomation b,
edepart c,
e_title_info e,
e_infotitle f,
e_target g,
e_infodepart h
WHERE h.content_id = b.content_id
AND e.title_id = f.title_id
AND c.departid = h.departid
AND b.content_id = e.content_id
AND g.targetyear = f.title_id
AND crtdate >= to_date(concat(to_char(trunc(SYSDATE, 'mm'), 'yyyy-MM-dd'),
' 00:00:00'),
'yyyy-MM-dd HH24:mi:ss')
AND crtdate <=
to_date(concat(to_char(last_day(SYSDATE), 'yyyy-MM-dd'), ' 23:59:59'),
'yyyy-MM-dd HH24:mi:ss')
如果有table access full就得考虑建索引。索引能够解决70%的问题。其他的,要考虑改写SQL逻辑等等。
就这么简单: truncate(sysdate,'mm')
你还要绕半天,猪!
16:40:55 SYS@tdwora > select cdate1, cdate2,
16:41:07 2 case when cdate1=cdate2 then 'True' else 'False' end as ifTheSame
16:41:07 3 from (
16:41:07 4 select trunc(sysdate,'mm') cdate1,
16:41:07 5 to_date(concat(to_char(trunc(sysdate,'mm'),'yyyy-MM-dd'),' 00:00:00'),'yyyy-MM-dd HH24:mi:ss') cdate2
16:41:07 6 from dual ) t;CDATE1 CDATE2 IFTHE
-------------- -------------- -----
01-7月 -12 01-7月 -12 True已选择 1 行。