Mysql编程实战三之排序问题

Mysql编程实战三之排序问题

1.需求

使用SQL语句将表中的数据排序。分别有两种排序规则，一种是全局增序；一种相同名次同序但全局增序。

2.表数据

• 建表语句如下：

CREATE TABLE `t` (
  `a` char(1) DEFAULT NULL
) ENGINE=InnoDB

• 插入数据

INSERT INTO insidemysql.t (a) 
VALUES 
('x'),
('x'),
('x'),
('y'),
('y'),
('z');

3.实现代码

• 全局增序：按照字母顺序排名，但是相同排名依然增序，比如现在有一组字母，排序结果应该如下：

+------+------+
| chara| rank |
+------+------+
| x    |    1 |
| x    |    2 |
| x    |    3 |
| y    |    4 |
| y    |    5 |
| z    |    6 |
+------+------+

select 
a
,(
    select 
    count(*)    
    from t t_2
    where t_1.a >= t_2.a
)as rank
from t t_1;
+------+------+
| a    | rank |
+------+------+
| x    |    3 |
| x    |    3 |
| x    |    3 |
| y    |    5 |
| y    |    5 |
| z    |    6 |
+------+------+
6 rows in set (0.00 sec)

可以看到，排序结果并非我们想要的

• 再次修改代码如下

select
a
,case when  @field = a
then @rank 
else @rank := @rank + 1 end as rank
,@field := a
from 
(
    select 
    a 
    from t
) as t_1,
(
    select 
    @rank := 0,
    @field := null  
)as t_2 

+------+------+-------------+
| a    | rank | @field := a |
+------+------+-------------+
| x    |    1 | x           |
| x    |    1 | x           |
| x    |    1 | x           |
| y    |    2 | y           |
| y    |    2 | y           |
| z    |    3 | z           |
+------+------+-------------+
6 rows in set (0.00 sec)

还不是想要的结果

select
a
,@rank := @rank + 1  as rank
from 
(
    select 
    a 
    from t
) as t_1,
(
    select 
    @rank := 0,
    @field := null  
)as t_2 
+------+------+
| a    | rank |
+------+------+
| x    |    1 |
| x    |    2 |
| x    |    3 |
| y    |    4 |
| y    |    5 |
| z    |    6 |
+------+------+
6 rows in set (0.00 sec)

这么来看，这个SQL像是解决了问题，但是真的是这样么？插入两条数据：

insert into t select 'a';
insert into t select 'z';

再次执行这个语句，结果发现出了问题：

+------+------+
| a    | rank |
+------+------+
| x    |    1 |
| x    |    2 |
| x    |    3 |
| y    |    4 |
| y    |    5 |
| z    |    6 |
| a    |    7 |
| z    |    8 |
+------+------+

这里没有把a，z按照字母顺序存储，即没有排序，使用order by的SQL如下：

select
a
,@rank := @rank + 1  as rank
from 
(
    select 
    a 
    from t
    order by a
) as t_1,
(
    select 
    @rank := 0,
    @field := null  
)as t_2;
+------+------+
| a    | rank |
+------+------+
| a    |    1 |
| x    |    2 |
| x    |    3 |
| x    |    4 |
| y    |    5 |
| y    |    6 |
| z    |    7 |
| z    |    8 |
+------+------+
8 rows in set (0.00 sec)

从结果看，没有问题。但是接着问题又来了，现在的需求是：

• 2.相同名次同序但全局增序：按照字母顺序排名，但是相同的字母有着相同的排名，比如说，现在有一组字符，他们的排序结果应该如下：

+------+------+
| chara| rank |
+------+------+
| x    |    1 |
| x    |    1 |
| x    |    1 |
| y    |    4 |
| y    |    4 |
| z    |    6 |
+------+------+

select
a
,case when @field != a
then @rank := @rank + 1  
else @rank 
end as rank

,@field := a
from 
(
    select 
    a 
    from t
    order by a
) as t_1,
(
    select 
    @rank := 0,
    @field := null  
)as t_2;

执行结果如下：

+------+------+-------------+
| a    | rank | @field := a |
+------+------+-------------+
| a    |    0 | a           |
| x    |    1 | x           |
| x    |    1 | x           |
| x    |    1 | x           |
| y    |    2 | y           |
| y    |    2 | y           |
| z    |    3 | z           |
| z    |    3 | z           |
+------+------+-------------+
8 rows in set (0.00 sec)

这个SQL实现了相同字母相同排序的功能，但是没有实现全局增量排序，修改SQL如下：

select
a
,case when @field = a
then @rank := @rank
else @rank := @ord end as rank
,@field := a
,@ord := @ord +1
from 
(
    select 
    a 
    from t
    order by a
) as t_1,
(
    select 
    @rank := 1,
    @ord := 1,
    @field := null  
)as t_2;

+------+------+-------------+-----------------+
| a    | rank | @field := a | @ord := @ord +1 |
+------+------+-------------+-----------------+
| a    |    1 | a           |               2 |
| x    |    2 | x           |               3 |
| x    |    2 | x           |               4 |
| x    |    2 | x           |               5 |
| y    |    5 | y           |               6 |
| y    |    5 | y           |               7 |
| z    |    7 | z           |               8 |
| z    |    7 | z           |               9 |
+------+------+-------------+-----------------+
8 rows in set (0.00 sec)

最终排序成功。