MySQL ranking SQL statement notation

Typically, when using MySQL, you will find a ranking of data based on multiple ranking criteria, with the same conditions.

Suppose there are table tables, two criteria fields Conditiona descending sort and conditionb ascending sort, the same conditions are the same, ranking information is saved in the Dense_rank field.

Procedure: Define @Conditiona, @conditionb, @dense_rank, @id_rank four user variables. where @Conditiona, @conditionB to save the current row of data conditions , @dense_rank to save the current rank value,@id_rank to save the ordinal, Query results according to ORDER by Conditiona Desc, conditionb ASC, the final wording is as follows, mainly using case end.

SELECT

Case

When @Conditiona =-1 and @conditionb =-1 Then @dense_rank: = 1 #判断是否第一个记录, is ranked first

When @Conditiona = Conditiona and @conditionb = conditionb /c8> then @dense_rank #判断当前记录是否与之前的一样, is to keep the rankings unchanged

ELSE @dense_rank: = @dense_rank + 1 #其他情况, rank plus 1

END as Dense_rank,

@id_rank: = @id_rank +1 as I_rank, #递增序号字段

@Conditiona : = Conditiona as Conditiona, #更新条件变量为当前记录的值

@conditionb : = conditionb as conditionb # update condition variable is the value of the current record

From

(SELECT @id_rank: =0) I_rank, # Initialize ordinal variable

(SELECT @dense_rank: =0) d, #初始化排名变量

(SELECT @conditionA: =-1) s, # Initialize condition variable

(SELECT @conditionb: =-1) C, # Initialize condition variable

Table

ORDER BY Conditiona DESC, conditionb ASC

MySQL ranking SQL statement notation