Chinaunix首页 | 论坛 | 博客
  • 博客访问: 89225
  • 博文数量: 6
  • 博客积分: 155
  • 博客等级:
  • 技术积分: 388
  • 用 户 组: 普通用户
  • 注册时间: 2010-10-27 11:13
文章分类

全部博文(6)

文章存档

2021年(1)

2017年(1)

2013年(2)

2012年(2)

我的朋友

分类: 其他平台

2021-04-28 11:10:32

  1. 先看看表的结构和数据:

    点击(此处)折叠或打开

    1. mysql> select * from group_order_test;
      +------------+-------+
      | group_name | value |
      +------------+-------+
      | a          | 1     |
      | a          | 2     |
      | a          | 3     |
      | a          | 4     |
      | a          | 5     |
      | b          | 11    |
      | b          | 22    |
      | b          | 33    |
      | b          | 44    |
      | b          | 55    |
      | b          | 66    |
      | c          | 111   |
      | c          | 222   |
      | c          | 333   |
      +------------+-------+
      14 rows in set
  2. 我们知道怎么查询某个组的第几名数据,比如查 a 组的第 4 名数据如下,注意 limit 后的数字比所需要的数字小 1:

    点击(此处)折叠或打开

      mysql> select * from group_order_test where group_name = 'a' order by value asc limit 3, 1;
      +------------+-------+
      | group_name | value |
      +------------+-------+
      | a          | 4     |
      +------------+-------+
      1 row in set
  3. 我们把上述查询结合到表数据中,这样我们增加了一列第 4 名的数值:

    点击(此处)折叠或打开

    1. mysql> select
          group_name,
          value,
          (select value from group_order_test as sub_table where sub_table.group_name = main_table.group_name order by value asc limit 3,1) as order_value
      from group_order_test as main_table;
      +------------+-------+-------------+
      | group_name | value | order_value |
      +------------+-------+-------------+
      | a          | 1     | 4           |
      | a          | 2     | 4           |
      | a          | 3     | 4           |
      | a          | 4     | 4           |
      | a          | 5     | 4           |
      | b          | 11    | 44          |
      | b          | 22    | 44          |
      | b          | 33    | 44          |
      | b          | 44    | 44          |
      | b          | 55    | 44          |
      | b          | 66    | 44          |
      | c          | 111   | NULL        |
      | c          | 222   | NULL        |
      | c          | 333   | NULL        |
      +------------+-------+-------------+
      14 rows in set
  4. 这时候要查询前 4 名的数据就好说了,直接比较大小:

    点击(此处)折叠或打开

    1. mysql> select group_name, value
      from
      (
          select
              group_name,
              value,
              (select value from group_order_test as sub_table where sub_table.group_name = main_table.group_name order by value asc limit 3,1) as order_value
          from group_order_test as main_table
      ) as t
      where value <= order_value;
      +------------+-------+
      | group_name | value |
      +------------+-------+
      | a          | 1     |
      | a          | 2     |
      | a          | 3     |
      | a          | 4     |
      | b          | 11    |
      | b          | 22    |
      | b          | 33    |
      | b          | 44    |
      +------------+-------+
      8 rows in set
  5. 等等,上面的 c 组呢?原来 NULL 值影响了,这样的话我们还得对少于所要排名的数据做一个处理,我们用相应组的最大值来做补充,如果没有相应排名的数值的话就用最大值来做比较:

    点击(此处)折叠或打开

    1. mysql> select *
      from
      (
          select
              group_name,
              value,
              (select value from group_order_test as sub_table where sub_table.group_name = main_table.group_name order by value asc limit 3,1) as order_value,
              (select max(value) from group_order_test as sub_table where sub_table.group_name = main_table.group_name group by group_name) as max_value
          from group_order_test as main_table
      ) as t
      where value <= ifnull(order_value, max_value);
      +------------+-------+-------------+-----------+
      | group_name | value | order_value | max_value |
      +------------+-------+-------------+-----------+
      | a          | 1     | 4           | 5         |
      | a          | 2     | 4           | 5         |
      | a          | 3     | 4           | 5         |
      | a          | 4     | 4           | 5         |
      | b          | 11    | 44          | 66        |
      | b          | 22    | 44          | 66        |
      | b          | 33    | 44          | 66        |
      | b          | 44    | 44          | 66        |
      | c          | 111   | NULL        | 333       |
      | c          | 222   | NULL        | 333       |
      | c          | 333   | NULL        | 333       |
      +------------+-------+-------------+-----------+
      11 rows in set
  6. 这下再做比较就能得到正确的前 4 名的数据了:

    点击(此处)折叠或打开

      mysql> select group_name, value
      from
      (
          select
              group_name,
              value,
              (select value from group_order_test as sub_table where sub_table.group_name = main_table.group_name order by value asc limit 3,1) as order_value,
              (select max(value) from group_order_test as sub_table where sub_table.group_name = main_table.group_name group by group_name) as max_value
          from group_order_test as main_table
      ) as t
      where value <= ifnull(order_value, max_value);
      +------------+-------+
      | group_name | value |
      +------------+-------+
      | a          | 1     |
      | a          | 2     |
      | a          | 3     |
      | a          | 4     |
      | b          | 11    |
      | b          | 22    |
      | b          | 33    |
      | b          | 44    |
      | c          | 111   |
      | c          | 222   |
      | c          | 333   |
      +------------+-------+
      11 rows in set
  7. 如果语句以后某个时候还要用,并且排名不确定,可以把排名做为一个变量来传入,这时候由于 limit 中不能用变量,可以使用预编译语句来实现:

    点击(此处)折叠或打开

      mysql>
      set @order := 3;

      set @limit_order := @order - 1;
      prepare stmt from '
                  select group_name, value
                  from
                  (
                      select
                          group_name,
                          value,
                          (select value from group_order_test as sub_table where sub_table.group_name = main_table.group_name order by value asc limit ?,1) as order_value,
                          (select max(value) from group_order_test as sub_table where sub_table.group_name = main_table.group_name group by group_name) as max_value
                      from group_order_test as main_table
                  ) as t
                  where value <= ifnull(order_value, max_value);';
      execute stmt using @limit_order;
      deallocate prepare stmt;

      Query OK, 0 rows affected

      Query OK, 0 rows affected

      Query OK, 0 rows affected
      Statement prepared

      +------------+-------+
      | group_name | value |
      +------------+-------+
      | a          | 1     |
      | a          | 2     |
      | a          | 3     |
      | b          | 11    |
      | b          | 22    |
      | b          | 33    |
      | c          | 111   |
      | c          | 222   |
      | c          | 333   |
      +------------+-------+
      9 rows in set

      Query OK, 0 rows affected
  8. 如果经常使用,使用存储过程也是个方便的主意,此处略

阅读(1788) | 评论(0) | 转发(0) |
0

上一篇:Shell贪吃蛇(38快乐版)(2017-03-09)

下一篇:没有了

给主人留下些什么吧!~~