求一条SQL，100分送上

[cyberlijun]

小弟有有一个表TOTAL_BROWSE_LOGIN_INC，字段如下

ID NUMBER(18) N PK: SEQ_BROWSE_LOGIN
BROWSE_ AMOUNT NUMBER N
LOGIN_AMOUNT NUMBER N
USER_TYPE NUMBER(1) N 1、 管局用户 2、 前置用户 3、 接入商
ISPID NUMBER(18) Y FK：YHGL_BADWXX(ID)
RECORDTIME DATE N

现在要一条SQL统计，最终实现如下效果：

昨日数据 一个周平均 一个月平均 三个月平均
12 20 10 11

现在小弟不知道如何写了，请各位高手帮忙

[iqlife]

原始数据和想要的数据，你个统计规则什么样子的？？

[cyberlijun]

界面输入查询条件，然后统计出数据

[cyberlijun]

这个没法截图，高手你要是有QQ的话加我QQ：176125727，我从QQ上给你截图

[iqlife]

select sum(yesterday),sum(lastweek),sum(lastmonth),sum(lastquarter)
from (
--昨天统计
select sum(t.browse_amount) as yesterday,0 as lastweek,0 as lastmonth,0 as lastquarter
from total_browse_login_inc t
where to_char(t.recordtime,'yyyy-mm-dd')=to_char(sysdate-1,'yyyy-mm-dd')
union all
--本周统计
select 0 as yesterday,sum(t.browse_amount) as lastweek,0 as lastmonth,0 as lastquarter
from total_browse_login_inc t
where to_char(t.recordtime,'iw')=to_char(sysdate,'iw')
union all
--本月统计
select 0 as yesterday,0 as lastweek,sum(t.browse_amount) as lastmonth,0 as lastquarter
from total_browse_login_inc t
where to_char(t.recordtime,'mm')=to_char(sysdate,'mm')
union all
--本季度统计
select 0 as yesterday,0 as lastweek,0 as lastmonth,sum(t.browse_amount) as lastquarter
from total_browse_login_inc t
where to_char(t.recordtime,'q')=to_char(sysdate,'q')
)

[cosio]

不建议用UNION　ALL
用case when 来判断时间！效率会高一点！

[jack0603]

UNION ALL的方法可以用，但执行效率太低
可是先把总的数据找出，然后再CASE WHEN 将数据分开，此大大提高执行效率

[yonghengdizhen]

用移动窗口函数

[ldw2545398]

[Quote=引用 4 楼 java3344520 的回复:]
SQL code
select sum(yesterday),sum(lastweek),sum(lastmonth),sum(lastquarter)
from (
--昨天统计
select sum(t.browse_amount) as yesterday,0 as lastweek,0 as lastmonth,0 as lastquarter
from total_browse……
[/Quote]
就这么写最好

[碧水幽幽泉]

[Quote=引用 5 楼 cosio 的回复:]
不建议用UNION　ALL
用case when 来判断时间！效率会高一点！
[/Quote]
case when 来判断

[zhangwonderful]

[Quote=引用 5 楼 cosio 的回复:]
不建议用UNION　ALL
用case when 来判断时间！效率会高一点！
[/Quote]
支持

[matong1977]

zhichi