请教一个sql查询语句，请高手帮忙？

<3D <7D <14D <30D Date/Time Open
20080804
y 20080811
y 20080812
y 20080816
y 20080822
y 20080824
y 20080828
y 20080828时间做相减，当前一列Date/Time减去前面一列的Date/Time，如果小余3天，就在<3D这一列写上y，小余7天就在<7D这一列上写上y。
请大家帮忙解决。

解决方案 »

免费领取超大流量手机卡，每月29元包185G流量+100分钟通话, 中国电信官方发货

根据要求,我看数据都是同月的,所以....改为数值,你自己把它改为日期一样的情况!
create table c
(
       t01 int
);insert into c values(4);
insert into c values(11);
insert into c values(12);
insert into c values(16);
insert into c values(22);
insert into c values(24);
insert into c values(28);
insert into c values(28);--SQL
select t01,t02, case when t02-t01<3 then 'Y' end "<3",
       case when t02-t01<7 and t02 -t01 >=3 then 'Y' end "<7",
       case when t02-t01<14 and t02 -t01 >=7 then 'Y' end "<14",
       case when t02-t01<30 and t02 -t01 >=14 then 'Y' end "<30"
from
(
       select t01,lead(t01) over(order by t01) t02 from c
)
where t02 is not null;
LEAD ... CASE WHEN ...
<HTML>
<HEAD>
<TITLE>PL/SQL Developer Export</TITLE>
</HEAD>
<BODY>
<TABLE BORDER="1">
<TR><TH>T01</TH><TH>T02</TH><TH><3</TH><TH><7</TH><TH><14</TH><TH><30</TH></TR>
<TR><TD>4</TD><TD>11</TD><TD> </TD><TD> </TD><TD>Y</TD><TD> </TD></TR>
<TR><TD>11</TD><TD>12</TD><TD>Y</TD><TD> </TD><TD> </TD><TD> </TD></TR>
<TR><TD>12</TD><TD>16</TD><TD> </TD><TD>Y</TD><TD> </TD><TD> </TD></TR>
<TR><TD>16</TD><TD>22</TD><TD> </TD><TD>Y</TD><TD> </TD><TD> </TD></TR>
<TR><TD>22</TD><TD>24</TD><TD>Y</TD><TD> </TD><TD> </TD><TD> </TD></TR>
<TR><TD>24</TD><TD>28</TD><TD> </TD><TD>Y</TD><TD> </TD><TD> </TD></TR>
<TR><TD>28</TD><TD>28</TD><TD>Y</TD><TD> </TD><TD> </TD><TD> </TD></TR>
</TABLE>
</BODY>
</HTML>
SELECT CASE WHEN (LAG(Date/Time,1,0) OVER(ORDER BY Date/Time)-Date/Time)<3 THEN 'Y' ELSE NULL,
       CASE WHEN (LAG(Date/Time,1,0) OVER(ORDER BY Date/Time)-Date/Time)<7 AND (LAG(Date/Time,1,0) OVER(ORDER BY Date/Time)-Date/Time)>3 THEN 'Y' ELSE NULL,
       CASE WHEN (LAG(Date/Time,1,0) OVER(ORDER BY Date/Time)-Date/Time)<14 AND (LAG(Date/Time,1,0) OVER(ORDER BY Date/Time)-Date/Time)>7 THEN 'Y' ELSE NULL,
        CASE WHEN (LAG(Date/Time,1,0) OVER(ORDER BY Date/Time)-Date/Time)<30 AND (LAG(Date/Time,1,0) OVER(ORDER BY Date/Time)-Date/Time)>14 THEN 'Y' ELSE NULL
     FROM YOURTABLE
SELECT CASE WHEN (LEAD(Date/Time,1,0) OVER(ORDER BY Date/Time)-Date/Time) <3 THEN 'Y' ELSE NULL,
      CASE WHEN (LEAD(Date/Time,1,0) OVER(ORDER BY Date/Time)-Date/Time) <7 AND (LEAD(Date/Time,1,0) OVER(ORDER BY Date/Time)-Date/Time)>3 THEN 'Y' ELSE NULL,
      CASE WHEN (LEAD(Date/Time,1,0) OVER(ORDER BY Date/Time)-Date/Time) <14 AND (LEAD(Date/Time,1,0) OVER(ORDER BY Date/Time)-Date/Time)>7 THEN 'Y' ELSE NULL,
        CASE WHEN (LEAD(Date/Time,1,0) OVER(ORDER BY Date/Time)-Date/Time) <30 AND (LEAD(Date/Time,1,0) OVER(ORDER BY Date/Time)-Date/Time)>14 THEN 'Y' ELSE NULL
    FROM YOURTABLE
SQL> select case when "Date/Time"-new_col <3 and new_col is not null then 'y' end "<3D",
  2         case when "Date/Time"-new_col >=3 and "Date/Time"-new_col <7 then 'y' end "<7D",
  3         case when "Date/Time"-new_col >=7 and "Date/Time"-new_col <14 then 'y' end "<14D",
  4         case when "Date/Time"-new_col >=14 and "Date/Time"-new_col <30 then 'y' end "<30D",
  5         "Date/Time"
  6   from (
  7         select to_date(t."Date/Time",'yyyy-mm-dd') "Date/Time",
  8         lag(to_date(t."Date/Time",'yyyy-mm-dd')) over(order by rownum) new_col
  9           from test t
10        );<3D <7D <14D <30D Date/Time
--- --- ---- ---- -----------
                  2008-8-4
        y         2008-8-11
y                 2008-8-12
    y             2008-8-16
    y             2008-8-22
y                 2008-8-24
    y             2008-8-28
y                 2008-8-288 rows selectedSQL>