我有一个表,描述如下:
CREATE TABLE REQUESTACTIVITY
(
TIMEFROM TIMESTAMP(6) NOT NULL,
REQUESTSTATUS INTEGER NOT NULL,
)TIMEFROM REQUESTSTATUS
4/12/2007 12:43:41.433000 AM 1
4/12/2007 12:44:41.433000 AM 2
4/12/2007 12:45:41.433000 AM 3
4/13/2007 12:43:41.433000 AM 1
4/13/2007 12:44:41.433000 AM 2
4/13/2007 12:45:41.433000 AM 3
4/14/2007 12:43:41.433000 AM 1
4/14/2007 12:44:41.433000 AM 2
4/14/2007 12:45:41.433000 AM 3
4/15/2007 12:43:41.433000 AM 1
4/15/2007 12:44:41.433000 AM 2
4/15/2007 12:45:41.433000 AM 3现在需要根据统计出
在4月13日到4月14日,每日requeststatus为1的记录的个数,每日requeststatus为2的记录的个数,每日requeststatus为3的记录的个数请问该怎么做?
CREATE TABLE REQUESTACTIVITY
(
TIMEFROM TIMESTAMP(6) NOT NULL,
REQUESTSTATUS INTEGER NOT NULL,
)TIMEFROM REQUESTSTATUS
4/12/2007 12:43:41.433000 AM 1
4/12/2007 12:44:41.433000 AM 2
4/12/2007 12:45:41.433000 AM 3
4/13/2007 12:43:41.433000 AM 1
4/13/2007 12:44:41.433000 AM 2
4/13/2007 12:45:41.433000 AM 3
4/14/2007 12:43:41.433000 AM 1
4/14/2007 12:44:41.433000 AM 2
4/14/2007 12:45:41.433000 AM 3
4/15/2007 12:43:41.433000 AM 1
4/15/2007 12:44:41.433000 AM 2
4/15/2007 12:45:41.433000 AM 3现在需要根据统计出
在4月13日到4月14日,每日requeststatus为1的记录的个数,每日requeststatus为2的记录的个数,每日requeststatus为3的记录的个数请问该怎么做?
from requestactivity
having to_char(timefrom,'MM/DD/YYYY') > '04/12/2007'
and to_char(timefrom,'MM/DD/YYYY') < '04/15/2007'
group by to_char(timefrom,'MM/DD/YYYY'), requeststatus;
select to_char(timefrom,'MM/DD/YYYY') day, requeststatus, count(requeststatus)
from requestactivity
having to_char(timefrom,'MM/DD/YYYY') > '04/12/2007'
and to_char(timefrom,'MM/DD/YYYY') < '04/15/2007'
group by to_char(timefrom,'MM/DD/YYYY'), requeststatus;
CREATE TABLE REQUESTSTATUSTYPE
(
REQUESTSTATUS INTEGER NOT NULL,
DESCRIPTION NVARCHAR2(254) NOT NULL
)这个表非常简单,只有简单的几天数据,如下:
table: REQUESTSTATUSTYPEREQUESTSTATUS DESCRIPTION
1 open
2 closed
3 Pending为什么我用如下语句会报错呢?select to_char(re.timefrom,'MM/DD/YYYY') day, rs.description status, count(re.requeststatus)
from requestactivity re, reqeststatustype rs
having to_char(re.timefrom,'MM/DD/YYYY') > '04/12/2007'
and to_char(re.timefrom,'MM/DD/YYYY') < '04/15/2007'
and re.requeststatus = rs.requeststatus
group by to_char(re.timefrom,'MM/DD/YYYY'), re.requeststatus;
select re.day day, rs.description status, re.amount amount
from requeststatustype rs,
(
select to_char(timefrom,'MM/DD/YYYY') day, requeststatus, count(requeststatus) amount
from requestactivity
having to_char(timefrom,'MM/DD/YYYY') > '04/12/2007'
and to_char(timefrom,'MM/DD/YYYY') < '04/15/2007'
group by to_char(timefrom,'MM/DD/YYYY'), requeststatus
) re
where rs.requeststatus = re.requeststatus
order by re.day;真是被这个group by 给搞得死了
(SELECT TO_CHAR(TIMEFROM,'MM/DD/YYYY') DAY,REQUESTSTATUS, COUNT(REQUESTSTATUS) COUNT FROM REQUESTACTIVITY
WHERE TO_CHAR(TIMEFROM,'MM/DD/YYYY') > '04/12/2007'
AND TO_CHAR(TIMEFROM,'MM/DD/YYYY') < '04/15/2007'
GROUP BY TO_CHAR(TIMEFROM,'MM/DD/YYYY'), REQUESTSTATUS) T1,
REQUESTSTATUSTYPE T2
WHERE T1.REQUESTSTATUS=T2.REQUESTSTATUS;
from requestactivity re, reqeststatustype rs
having to_char(re.timefrom,'MM/DD/YYYY') > '04/12/2007'
and to_char(re.timefrom,'MM/DD/YYYY') < '04/15/2007'
and re.requeststatus = rs.requeststatus
group by to_char(re.timefrom,'MM/DD/YYYY'), re.requeststatus;这条语句报错是因为rs.description status这个字段没内在group by 中是不能单取的
还是写成子表再关联吧