SQL高级应用

追加：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