SQL Ranking 技巧

/*给一个table的某个字段排序，分别用sub-query和self-join。这个例子会有很多变化，例如qty值是否有重复，关键的关系运算符是 >, >=, <, <=，其效果和涵义也不同*/declare @t table(qty int)insert @t select 740insert @t select 100insert @t select 560insert @t select 230insert @t select 980insert @t select 800/* sub query  */select ranking=(select count(qty) from @t B where A.qty >= B.qty), qtyfrom @t Aorder by 1/* self - join */select ranking=count(B.qty), A.qtyfrom @t A join @t B on A.qty >= B.qtygroup by A.qtyorder by 1