SQL OVER的运用。

1.OVER 的运用。
仔细看完这个后，我就在想，我以前做的ORACLE的报表，如果也有这个函数，
那要省多少事呀，不行，一会一定要查一下ORACLE有没有相对应的函数。
==========================================================================================
::分页::

select * from 
(select *
from (select row_number() over(order by nid) as rowid,nid from syslog)a
where a.i_key>100 and a.i_key<=200) b
left join syslog s on b.nid = s.nid

1.根据主键排序，并把行号查出来如下：
select row_number() over(order by nid) as rowid,nid from syslog
2.把要分页的开始行号及结束行号传入条件中(100：beginIndex,200:endIndex)
select *
from (select row_number() over(order by nid) as rowid,nid from syslog)a
where a.rowid>100 and a.rowid<=200
3.再与原表用主键关联即可
left join syslog s on b.nid = s.nid

=============================================================================================
select SUM(cast(sThread as int)) OVER(PARTITION BY sThread,slevel) AS 'Total'
    ,AVG(cast(sThread as int)) OVER(PARTITION BY sThread) AS 'Avg'
    ,COUNT(cast(sThread as int)) OVER(PARTITION BY sThread) AS 'Count'
    ,MIN(cast(sThread as int)) OVER(PARTITION BY sThread) AS 'Min'
    ,MAX(cast(sThread as int)) OVER(PARTITION BY sThread) AS 'Max'
    ,ROW_NUMBER() over(order by nid) rowid
    ,*
from Syslog
where cast(sThread as int) in(16);

用法，可以不用Group by下用聚合函数，PARTITION BY 跟要GROUP BY 的栏位。
聚合函数的 OVER（）里跟Partition BY
row_number() 的OVER（）里跟 Order by .

SUM(cast(sThread as int)) OVER(PARTITION BY sThread,slevel) AS 'Total'
一个完整的OVER子句，可以当做一个正常栏位来使用，可以进行计算等。
select SUM(cast(sThread as int)) OVER(PARTITION BY sThread,slevel) AS 'Total'
    ,AVG(cast(sThread as int)) OVER(PARTITION BY sThread) AS 'Avg'
    ,COUNT(cast(sThread as int)) OVER(PARTITION BY sThread) AS 'Count'
    ,MIN(cast(sThread as int)) OVER(PARTITION BY sThread) AS 'Min'
    ,MAX(cast(sThread as int)) OVER(PARTITION BY sThread) AS 'Max'
    ,SUM(cast(sThread as int)) OVER(PARTITION BY sThread,slevel) / AVG(cast(sThread as int)) OVER(PARTITION BY sThread) AS 'mod'
    ,sLevel
from Syslog
where cast(sThread as int) in(16);
同时，也可以先GROUP BY后的结果，再进行二次聚合。
select SUM(cast(sThread as int)) OVER(PARTITION BY sThread,slevel) AS 'Total'
	,SUM(cast(sThread as int)) OVER(PARTITION BY sThread) AS 'Total2'
    ,AVG(cast(sThread as int)) OVER(PARTITION BY sThread) AS 'Avg'
    ,COUNT(cast(sThread as int)) OVER(PARTITION BY sThread) AS 'Count'
    ,MIN(cast(sThread as int)) OVER(PARTITION BY sThread) AS 'Min'
    ,MAX(cast(sThread as int)) OVER(PARTITION BY sThread) AS 'Max'    
    ,slevel
from Syslog
where cast(sThread as int) in(5,16)
group by sThread,slevel
===============================================================================================