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