请问一个SQL查询语句
S_id s_Date S_AmountA 2007-9-1 100
A 2007-9-2 95
A 2007-9-3 105
A 2007-9-4 101
A 2007-9-5 110
A 2007-9-6 200
A 2007-9-7 153
A 2007-9-8 99
A 2007-9-9 65
B 2007-9-5 110
B 2007-9-6 200
B 2007-9-7 153
B 2007-9-8 99
B 2007-9-9 65
C 2007-9-3 105
C 2007-9-4 101
C 2007-9-5 88
C 2007-9-6 200
C 2007-9-7 153
C 2007-9-8 99
要求：
查询表中指定日期范围内，连续N天s_amount > 100 的S _id.
比如：
日期范围：2007-9-1 -----2007-9-10
N=3 , 结果为A，B
N =4 ，结果为A 要求：
查询表中指定日期范围内，连续N天s_amount > 100 的S _id.
比如：
日期范围：2007-9-1 -----2007-9-10
N=3 , 结果为A，B
N =4 ，结果为A
N？连续的日期？N=3；N=4返回的结果还小，是因为他们的开始时间不一样吗？
我是没看啥明白。。。 帮你写了一个，你稍微改一下吧：(其中n为连续的天数)
select distinct s_id from
(select s_id,s_date,s_amount,
last_value(s_date) over (partition by s_id order by s_date rows between current row and &&n-1 following) last_date,
count(s_date) over (partition by s_id order by s_date rows between current row and &&n-1 following) window_size
from sale_record
where s_date between '01-SEP-2007' and '10-SEP-2007'
and s_amount>100) t
where exists (select 1 from dual where t.window_size=&&n and t.last_date-t.s_date+1=&&n)
/ SQL> select * from test;
S_ID S_DATE S_AMOUNT
---- -------- ----------
A 20070901 100
A 20070902 95
A 20070903 105
A 20070904 101
A 20070905 110
A 20070906 200
A 20070907 153
A 20070908 99
A 20070909 65
B 20070905 110
B 20070906 200
B 20070907 153
B 20070908 99
B 20070909 65
C 20070903 105
C 20070904 101
C 20070905 88
C 20070906 200
C 20070907 153
C 20070908 99
20 rows selected
SELECT DISTINCT S_ID
FROM (SELECT S_ID,
S_DATE,
LAG(S_DATE, &N - 1) OVER(PARTITION BY S_ID ORDER BY S_DATE) PRE
FROM TEST T
WHERE T.S_DATE BETWEEN &BEGIN_DATE AND &END_DATE
AND T.S_AMOUNT >= &AMOUNT) U
WHERE U.S_DATE - U.PRE = &N - 1
N ＝ 3
BEGIN_DATE = 20070901
END_DATE = 20070910
AMOUNT = 1000
A
B -- before this procedure, it's suggested that a certain index is created on sid, so does sdate
-- order the records by sid and sdate
declare
cursor c_sid is
select s_id, s_date, s_amount
from table_name
where s_amount > 100
and s_date >= &c_date_from
and s_date < &c_date_end
order by s_id, s_date;
l_count number;
l_temp_id varchar2(1);
l_temp_date date;
begin
-- init
l_count := 0;
l_temp_id := '-1';
l_temp_date := to_date('0','J');
-- check each record to find continous "n" records
for c_sid_rec in c_sid loop
if c_sid_rec = l_temp_id then
if c_sid_rec.s_date = l_temp_date + 1 then
l_count := l_count + 1;
else
l_count := 1;
end if;
else
l_count := 1;
end if;
-- if up to "n" records, putout the id
if l_count = &n then
dbms_output.put_line(l_temp_id);
end if;
-- reset the temp value
l_temp_id := c_sid_rec.s_id;
l_temp_date := c_sid_rec.s_date;
end loop;
end; 3. lag和lead函数介绍
取出每个月的上个月和下个月的话费总额
1 select area_code,bill_month, local_fare cur_local_fare,
2 lag(local_fare,2,0) over (partition by area_code order by bill_month ) pre_local_fare,
3 lag(local_fare,1,0) over (partition by area_code order by bill_month ) last_local_fare,
4 lead(local_fare,1,0) over (partition by area_code order by bill_month ) next_local_fare,
5 lead(local_fare,2,0) over (partition by area_code order by bill_month ) post_local_fare
6 from (
7 select area_code,bill_month,sum(local_fare) local_fare
8 from t
9 group by area_code,bill_month
10* )
SQL> /
AREA_CODE BILL_MONTH CUR_LOCAL_FARE PRE_LOCAL_FARE LAST_LOCAL_FARE NEXT_LOCAL_FARE POST_LOCAL_FARE
--------- ---------- -------------- -------------- --------------- --------------- ---------------
5761 200405 13060.433 0 0 13318.93 13710.265
5761 200406 13318.93 0 13060.433 13710.265 14135.781
5761 200407 13710.265 13060.433 13318.93 14135.781 0
5761 200408 14135.781 13318.93 13710.265 0 0
5762 200405 12643.791 0 0 12795.06 13224.297
5762 200406 12795.06 0 12643.791 13224.297 13376.468
5762 200407 13224.297 12643.791 12795.06 13376.468 0
5762 200408 13376.468 12795.06 13224.297 0 0
5763 200405 13060.433 0 0 13318.93 13710.265
5763 200406 13318.93 0 13060.433 13710.265 14135.781
5763 200407 13710.265 13060.433 13318.93 14135.781 0
5763 200408 14135.781 13318.93 13710.265 0 0
5764 200405 12487.791 0 0 13295.187 13444.093
5764 200406 13295.187 0 12487.791 13444.093 13929.694
5764 200407 13444.093 12487.791 13295.187 13929.694 0
5764 200408 13929.694 13295.187 13444.093 0 0
5765 200405 25057.736 0 0 26058.46 26301.881
5765 200406 26058.46 0 25057.736 26301.881 27130.638
5765 200407 26301.881 25057.736 26058.46 27130.638 0
5765 200408 27130.638 26058.46 26301.881 0 0
20 rows selected.
利用lag和lead函数,我们可以在同一行中显示前n行的数据,也可以显示后n行的数据.
页:
[1]