我有如下一个表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
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
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
[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]
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
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
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
这个的话,有ON的条件,如果之前没有出现的话不显示的,
比如C 当日为0,三日内为1 ,这个就不显示了
要不然
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
看看你的当前时间