SQL高级应用 Garin Zhang

追加：row_number, rank, dese_rank, ntile
1. row_number: 为查询出来的每一行记录生成一个序号。
SELECT row_number() OVER(ORDER BY field) AS row_n
from tablename;
分页查询：
with t_towtable
as (select row_number over(order by field1) as row_number from tb)
select * from t_rowtable where row_number > 1 and row_number > 4 order by field1;
ROW_NUMBER() OVER([<partition_by_caluse>] <order_by_clause>);
2. rank: 返回结果集的分区内每行的排名：
RANK() OVER([<partition_by_caluse_按照某个字段分区>]<order_by_clause_排序>);
Examples：
WITH CustomerSum AS
(SELECT CustomerID, SUM(totalDue) AS total
  from sales GROUP BY CustomerID)
SELECT * RANK() OVER (ORDER BY total DESC) AS RANK
from CustomerSum
3. dense_rank: 顺序排序（生成的序号是连续的）。
4. ntile：取前多少个名次的排名的行。
SELECT SalesID, NTILE(1000) OVER(ORDER BY CustomerID) AS NTile
from Sales
桶数的计算：
if(记录数%桶数 == 0)
      每桶记录数都为记数总数/桶数
else
      recordCount1 = total/tongNum + 1;
      int n = 1;
      m = recordCont1*n;
while((total - m) % (tongNum - n)) != 0)
{
      n++;
      m = recordCount1 * n;
}
recordCount2 = (total - m) / (tongNum - n);
将前n个桶记录设置为recordCount1
将n+1个到后面所有桶记为recordCount2