为什么空值在mysql中排名不一样? [英] Why null values are not ranking same in mysql?

问题描述

昨天我问了一个有关按学生分数对学生进行排名的问题.

[

因此，我想知道 NULL 的值是否相同?这个查询是否应该为数据库中的每个用户提供排名1 ?为什么它对空值进行递增排序?

解决方案

许多人认为 NULL 表示无值或不存在.甚至维基百科都以此方式解释.

将其视为 unknown value (未知值)，一切都会变得更有意义.

一个人不能比较两个 unknown值，因为它们未知.它们不相等，但也不相同.它们都不比另一个大.

两个未知值的比较结果也是一个未知值(即 NULL ).

Yesterday I asked a question about ranking students by their points.

[Rank users in mysql by their points ]

I actually solved my problem with the help I've recieved from other question (thanks to everyone who helped me).
Meanwhile I figured out something accidently. If I try to rank my students by their points and if points column was empty (NULL) my query didn't gave an error also it sorted ranks like 1-2-3-4 but all values were NULL

this is the query I've tried

select er.*,
       (@rank := if(@points = points, 
                    @rank, 
                    if(@points := points,    
                       @rank + 1, 
                       @rank + 1                       
                      )
                   )                  
       ) as ranking
from examresults er cross join
     (select @rank := 0, @points := -1) params
order by points desc;

and this is the result.

So I wonder arent NULL values are the same ? isnt this query should gave rank 1 for every user in my database ? why it ranks null values incrementally ?

解决方案

Many people think of NULL as meaning no value or non-existent. Even Wikipedia explains it this way.

Think of it as unknown value and everything will make more sense.

One cannot compare two unknown values because, well, they are not known. They are not equal but they are also not different. None of them is greater than the other.

The result of comparison of two unknown values is also an unknown value (i.e. NULL).

这篇关于为什么空值在mysql中排名不一样?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！