SQL> select * from tmp2;FGOOD FCUS FDATE FPRICE
---------- ---------- ----------- ----------
111 001 2005-9-10 5.5
111 001 2005-9-12 6.5
111 001 2005-9-13 6
122 001 2005-9-10 7.5
122 001 2005-9-12 9.5
122 001 2005-9-13 8.56 rows selectedSQL> select * from
2 (select t.*,
3 row_number() over(partition by fcus,fgood order by fdate desc) rn
4 from tmp2 t)
5 where rn <3;FGOOD FCUS FDATE FPRICE RN
---------- ---------- ----------- ---------- ----------
111 001 2005-9-13 6 1
111 001 2005-9-12 6.5 2
122 001 2005-9-13 8.5 1
122 001 2005-9-12 9.5 2
---------- ---------- ----------- ----------
111 001 2005-9-10 5.5
111 001 2005-9-12 6.5
111 001 2005-9-13 6
122 001 2005-9-10 7.5
122 001 2005-9-12 9.5
122 001 2005-9-13 8.56 rows selectedSQL> select * from
2 (select t.*,
3 row_number() over(partition by fcus,fgood order by fdate desc) rn
4 from tmp2 t)
5 where rn <3;FGOOD FCUS FDATE FPRICE RN
---------- ---------- ----------- ---------- ----------
111 001 2005-9-13 6 1
111 001 2005-9-12 6.5 2
122 001 2005-9-13 8.5 1
122 001 2005-9-12 9.5 2
--------------------------
下面给一个例子哈1!>Grouping和Rollup系列
select case when grouping(dtScrq)=1 then '合计'
else dbo.GetDatetimeDate(dtScrq,'-')
end as 上车日期 ,
case
when grouping(dtScrq)=1 and grouping(strCph)=1 then '--'
when grouping(dtScrq)=0 and grouping(strCph)=1 then '小计'
else strCph
end as 车牌号,
sum(fltJflc+fltKsLc) as 日总行驶里程,
sum(fltJflc) as 日总载客里程,
sum(intDdSj) as 日总计费时间,
count(ID) as 日总载客车次,
sum(curJfJe) as 日总营收金额
from IcYyMx
where dtScRq is not null
group by dtScrq,strCph
with RollUp(dtScrq,strCph)
> Over系列和Patition系列
select Id,
Fld2,
fld3,
--//排名
RANK( ) OVER (ORDER BY Fld2 DESC) as 排法一名次,
Dense_Rank( ) OVER (ORDER BY Fld2 DESC) as 排法二名次,
Row_Number( ) OVER (ORDER BY Fld2 DESC) as 排法三名次,
--//分组
Row_Number( ) OVER (PARTITION BY Fld2 ORDER BY fld3 DESC ) as 分组排名次,
max(fld3) over (PARTITION BY Fld2 ORDER BY fld3 DESC) as MaxOverPart结果,
--//当前记录的前一条和后一条记录
Lag(fld3) OVER (ORDER BY Fld2 DESC) as Lag结果,
Lead(fld3) OVER (ORDER BY Fld2 DESC) as Lead结果,
--//最值
Max (fld3) over( order by fld2) as MaxOver结果,
--//分组取百分比
ratio_to_report(fld3) over (PARTITION BY Fld2 ) as ratioRpt结果,
--//分组取首,末记录
first_value(fld3) over (PARTITION BY Fld2 ORDER BY fld3 DESC) as first结果,
--//当前记录的前N条和后N条
AVG(SUM(tot_sales)) OVER (ORDER BY month
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS WinFun结果1,
--//当前记录前的所有记录和当前记录
AVG(SUM(tot_sales)) OVER (ORDER BY month
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS WinFun结果2
from testtable;//一下是表结构和内容---
SQL> select * from testtable;ID FLD1 FLD2 FLD3
---------- ---------- ---------- ----------
1 ddss 12 12.22
2 ffdde 13 13.31
3 ffeedd 13 14.44
4 eddw 15 15.55
5 errrew 17 17.77
6 eefdds 18 18.88
7 eeddw 21 21.11
8 ssddf 22 22.22已选择8行。