按时间分组统计问题,再线等,80分求助

[zfwyyz]

我有如下一个表table
dw sj rq
a 12 2009-03-27 12:00:00
b 13 2009-03-27 11:30:00
a 11 2009-03-27 11:05:00
b 21 2009-03-26 16:00:00
a sd 2009-03-26 12:00:00
c sf 2009-03-26 11:00:00
a 12 2009-03-25 12:00:00
a 12 2009-03-20 12:30:00
a 12 2009-03-01 12:00:00
我想实现如下结果
dw 当日 三日 七日 一个月
a 2 4 5 6
b 1 2 2 2
c 0 1 1 1
这个统计查询怎么做,我只能一次查一串结果
select dw,count(dw) as 当日
from table
where rq>=('2009-03-26','yyyy-mm-dd hh24:mi:ss') and rq<=('2009-03-27','yyyy-mm-dd hh24:mi:ss')
group by dw

[yf520gn]

SELECT A.DW,nvl(A.当日,0),nvl(b.三日,),nvl(c.七日,0),nvl(d.一个月,0)
(SELECT DW,COUNT(*)当日FROM TABLE 
WHERE TRUNC(RQ)=TRUNC(SYSDATE)
GROUP BY DW) A
INNER JOIN 
(SELECT DW,COUNT(*) 三日 from table
where (trunc(sysdate)-trunc(rq)<=3) and (trunc(sysdate)<>trunc(rq))
GROUP BY DW) B
ON A.DW=B.DW
INNER JOIN
(SELECT DW,COUNT(*)七日 from table
where (trunc(sysdate)-trunc(rq)>3) and (trunc(sysdate)-trunc(rq)<=7)
group by dw) C
ON A.DW=C.DW
inner join
(select dw,count(*) 一个月 from table
where (trunc(sysdate)-trunc(rq)>7) and (trunc(sysdate)-trunc(rq)<=30)
group by dw) D
ON A.DW=D.DW

[yf520gn]

[code=SQL]
SELECT A.DW,nvl(A.当日,0) 当日,nvl(b.三日,0) 三日,nvl(c.七日,0) 七日,nvl(d.一个月,0) 一个月
FROM
((SELECT DW,COUNT(*)当日FROM TABLE 
WHERE TRUNC(RQ)=TRUNC(SYSDATE)
GROUP BY DW) A
INNER JOIN 
(SELECT DW,COUNT(*) 三日 from table
where (trunc(sysdate)-trunc(rq)<=3) and (trunc(sysdate)<>trunc(rq))
GROUP BY DW) B
ON A.DW=B.DW
INNER JOIN
(SELECT DW,COUNT(*)七日 from table
where (trunc(sysdate)-trunc(rq)>3) and (trunc(sysdate)-trunc(rq)<=7)
group by dw) C
ON A.DW=C.DW
inner join
(select dw,count(*) 一个月 from table
where (trunc(sysdate)-trunc(rq)>7) and (trunc(sysdate)-trunc(rq)<=30)
group by dw) D
ON A.DW=D.DW)

[/code]

[yhuib]

select
dw,
sum(case when trunc(sysdate)=trunc(rq) then 1
else 0
end
) 当日,
sum(case when trunc(sysdate)-trunc(rq)<=3 and then 1
else 0
end
) 三日,
sum(case when trunc(sysdate)-trunc(rq)<=7 and then 1
else 0
end
) 七日,
sum(case when trunc(sysdate)-trunc(rq)<=30 and then 1
else 0
end
) 一个月
from table
froup by dw

[yhuib]

[Quote=引用 3 楼 yhuib 的回复:]
select
dw,
sum(case when trunc(sysdate)=trunc(rq) then 1
else 0
end
) 当日,
sum(case when trunc(sysdate)-trunc(rq) <=3 and  then 1
else 0
end
) 三日,
sum(case when trunc(sysdate)-trunc(rq) <=7 and  then 1
else 0
end
) 七日,
sum(case when trunc(sysdate)-trunc(rq) <=30 and  then 1
else 0
end
) 一个月
from table
froup by dw
[/Quote]
select
dw,
sum(case when trunc(sysdate)=trunc(rq) then 1
else 0
end
) 当日,
sum(case when trunc(sysdate)-trunc(rq) <=3 and then 1
else 0
end
) 三日,
sum(case when trunc(sysdate)-trunc(rq) <=7 and then 1
else 0
end
) 七日,
sum(case when trunc(sysdate)-trunc(rq) <=30 and then 1
else 0
end
) 一个月
from table
group by dw

[yf520gn]

[Quote=引用 3 楼 yhuib 的回复:]
select
dw,
sum(case when trunc(sysdate)=trunc(rq) then 1
else 0
end
) 当日,
sum(case when trunc(sysdate)-trunc(rq) <=3 and then 1
else 0
end
) 三日,
sum(case when trunc(sysdate)-trunc(rq) <=7 and then 1
else 0
end
) 七日,
sum(case…
[/Quote]
不错，呵呵!

[yhuib]

多了and

select
dw,
sum(case when trunc(sysdate)=trunc(rq) then 1
else 0
end
) 当日,
sum(case when trunc(sysdate)-trunc(rq) <=3 then 1
else 0
end
) 三日,
sum(case when trunc(sysdate)-trunc(rq) <=7 then 1
else 0
end
) 七日,
sum(case when trunc(sysdate)-trunc(rq) <=30 then 1
else 0
end
) 一个月
from table
group by dw

[zfwyyz]

用inner join
这个的话,有ON的条件,如果之前没有出现的话不显示的,
比如C 当日为0,三日内为1 ,这个就不显示了

[zfwyyz]

[Quote=引用 6 楼 yhuib 的回复:]
多了and

select
dw,
sum(case when trunc(sysdate)=trunc(rq) then 1
else 0
end
) 当日,
sum(case when trunc(sysdate)-trunc(rq) <=3 then 1
else 0
end
) 三日,
sum(case when trunc(sysdate)-trunc(rq) <=7 then 1
else 0
end
) 七日,
sum(ca…
[/Quote]
这个不错哦,我正在试,不过好象当天的结果有点问题,当天的结果出不来,显示为0的

[yhuib]

应该没问题的阿
要不然
select
dw,
sum(case when to_char(sysdate,'YYYYMMDD')=to_char(rq,'YYYYMMDD') then 1
else 0
end
) 当日,
sum(case when trunc(sysdate)-trunc(rq) <=3 then 1
else 0
end
) 三日,
sum(case when trunc(sysdate)-trunc(rq) <=7 then 1
else 0
end
) 七日,
sum(case when trunc(sysdate)-trunc(rq) <=30 then 1
else 0
end
) 一个月
from table
group by dw

[yhuib]

select sysdate from dual
看看你的当前时间

[zfwyyz]

恩,谢谢哦,解决了,马上结贴