SQL 语句 如何写？？

Question

表名： HISTORY
格式如下：
CREATE TABLE DTUSER_TJ.DT_NEWS_VISIT_HISTORY
(
  NUMNEWSHISID  NUMBER(15)                      NOT NULL,
  VC2CLIENTID   VARCHAR2(32 BYTE)               NOT NULL,
  NUMNEWSRID    NUMBER(15)                      NOT NULL,
  DATACCESS     DATE                            NOT NULL
)内容如下：
...
5073736,粤ACT7468,67598488,2008-11-7 14:43:31
5073738,匿ACC3694,74110447,2008-11-7 14:43:31
5073740,赣AAB9031,72009002,2008-11-7 14:43:31
5073741,闽AAN1007,74106560,2008-11-7 14:43:31
5073742,津AAD5937,74226905,2008-11-7 14:43:31
5073743,粤ACY9940,74292395,2008-11-7 14:43:31
5073744,匿AAM6741,74227151,2008-11-7 14:43:31
5073745,苏AAD4395,74244650,2008-11-7 14:43:31
....这个表记录了许多用户的访问记录。我的需求是： 从这个表中得到每个用户7天内的访问内容，有可能这个曾经在1个月前访问的，也能得到，而不是只是得到当前时间 7 天前的内容。请教大家！

Answer 1

VC2CLIENTID   是 用户字段从这个表中得到每个用户7天内的访问内容，有可能这个用户曾经在1个月前访问的，也能得到，而不是只是得到当前时间 7 天前的内容比如用户在11月1日最后访问的，则得到他11月1日前7天的访问记录。如果用户是11月17日访问的，则得到他11月11日到11月17的访问记录。

Answer 2

按用户ID group by并按时间进行倒序上面数据中不知道你哪个是用户ID.
大概这么写：
select NUMNEWSHISID,VC2CLIENTID,NUMNEWSRID,DATACCESS
from (select * ,rownum() over(partition by 用户ID order by DATACCESS desc) RN
        from HISTORY)t
where t.RN <=7

Answer 3

-- TRY IT ..
SQL> SELECT * FROM DT_NEWS_VISIT_HISTORY;    NUMNEWSHISID VC2CLIENTID                            NUMNEWSRID DATACCESS
---------------- -------------------------------- ---------------- -----------
         5073736 粤ACT7468                                67598488 2008-11-7 1
         5073738 匿ACC3694                                74110447 2008-11-7 1
         5073740 赣AAB9031                                72009002 2008-11-7 1
         5073741 闽AAN1007                                74106560 2008-11-7 1
         5073742 津AAD5937                                74226905 2008-11-7 1
         5073743 粤ACY9940                                74292395 2008-11-7 1
         5073744 匿AAM6741                                74227151 2008-11-7 1
         5073745 苏AAD4395                                74244650 2008-11-7 1
         5073746 苏AAD4395                                74244651 2008-10-11
         5073747 苏AAD4395                                74244652 2008-11-2 1
         5073748 苏AAD4395                                74244653 2008-11-1 111 rows selectedSQL> SELECT *
  2    FROM (SELECT DT.*,
  3                 MAX(DATACCESS) OVER(PARTITION BY VC2CLIENTID) - DATACCESS DIFF
  4            FROM DT_NEWS_VISIT_HISTORY DT) TT
  5   WHERE DIFF <= 6;    NUMNEWSHISID VC2CLIENTID                            NUMNEWSRID DATACCESS         DIFF
---------------- -------------------------------- ---------------- ----------- ----------
         5073740 赣AAB9031                                72009002 2008-11-7 1          0
         5073742 津AAD5937                                74226905 2008-11-7 1          0
         5073741 闽AAN1007                                74106560 2008-11-7 1          0
         5073744 匿AAM6741                                74227151 2008-11-7 1          0
         5073738 匿ACC3694                                74110447 2008-11-7 1          0
         5073745 苏AAD4395                                74244650 2008-11-7 1          0
         5073748 苏AAD4395                                74244653 2008-11-1 1          6
         5073747 苏AAD4395                                74244652 2008-11-2 1          5
         5073736 粤ACT7468                                67598488 2008-11-7 1          0
         5073743 粤ACY9940                                74292395 2008-11-7 1          010 rows selected

Answer 4

select 
*
from 
DTUSER_TJ.DT_NEWS_VISIT_HISTORY 
where
to_char(DATACCESS,'yyyy-mm-dd')>to_char(sysdate-DATACCESS,'yyyy-mm-dd')

Answer 5

报了一个 “ORA-01481: 无效的数字格式模型”

Answer 6

有点不明白  to_char(DATACCESS,'yyyy-mm-dd')>to_char(sysdate-DATACCESS,'yyyy-mm-dd')   什么意思〉？？

Answer 7

SELECT *
  FROM dt_news_visit_history a,
       (SELECT   vc2clientid, MAX (dataccess) acc
            FROM dt_news_visit_history
        GROUP BY vc2clientid) b
 WHERE a.vc2clientid = b.vc2clientid AND b.acc - a.dataccess < 1

Answer 8

select * from HISTORY where VC2CLIENTID=XXX and (rq between (select  max(rq)-interval '7' day from HISTORY 
where VC2CLIENTID=XXX) and (select max(rq) from HISTORY where VC2CLIENTID=XXX)  )