Mysql查询n * 2格式的最大记录数，范围 [英] Mysql Query maximum records in n*2 format with range

查看：52
本文介绍了Mysql查询n * 2格式的最大记录数，范围的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

参考我之前的问题 我在问这个有类似问题的新问题.

referring to my previous question I'm asking this new question with similar issue.

我有这样的表 records

---- ---------     ------
 id     name       points
---- ---------     ------
 1      aaaa         90
 2      bbbb         87
 3      cccc         90
 4      dddd         80
 5      eeee         86
 6      ffff         82
 7      gggg         87
 8      hhhh         85
 10     iiii         86
 11     iiii         86
 12     iiii         86
 13     iiii         86
 14     iiii         87
 15     iiii         73
 16     iiii         86
 17     iiii         73
 18     hhhh         85
 19     hhhh         73
 20     hhhh         73
 21     hhhh         70
 22     hhhh         65
 23     hhhh         70
 24     hhhh         50

表格，我想选择符合以下条件的记录

form the table, I want to select the records with the following condition

  1. 对于第一个最高记录，最高分将排在首位.(但只有一个记录).我有两条最高点90的记录，这里应该显示最近的一条(最大id)

  1. For the first maximum record, Highest Points will be on top. (But only one record). I have two record with the highest point 90, Here the recent one should be displayed (maximum id)

从第二条记录开始，应以 (n*2) 的格式检索字段.我的意思是，对于第二个最大记录，我可以允许在第二个最大记录-10 的范围内下降 4 个值 (2*2=4).即在表中，我只选择了最近 4 条记录(最大 id)，范围为 77 到 87(87-10=77).现在第三个最大记录应该小于或等于 76 (77-1)，即在表中它是 73.同样对于第三个最大值，我可以允许 3*2=6 记录落在 63-73 (73-10=63) .. 等等..

From second record onwards, the fields should be retrieved in the format of (n*2). I mean, for the 2nd maximum records I can allow 4 values to be fallen (2*2=4) with in the range from 2nd maximum record-10. i.e in the table I select only recent 4 records (Maximum id) with in the range of 77 to 87 (87-10=77). now the 3rd maximum record, should be less than or equal to 76 (77-1) ie in the table it was 73. similarly for 3rd maximum, I can allow 3*2=6 records to be fallen within 63-73 (73-10=63) .. and so on..

目前，我正在使用另一个问题的答案

Currently, I'm using answer from my another question

SELECT id, name, points FROM (
    SELECT
    t.*
    , @n := IF(@prev_points != points, @n + 1, @n) AS n
    , @row := IF(@prev_points != points, 1, @row + 1) AS row
    , @prev_points := points
    FROM
    t
    , (SELECT @prev_points := null, @n := 1, @row := 0) var_init_subquery
    ORDER BY points DESC, id DESC
) sq
WHERE row <= CASE WHEN n = 1 THEN 1 ELSE n * 2 END
;

但它仅限于一个相同的记录.是否有可能根据需要在此查询中使用 count 或任何其他相关功能.希望你能帮助我.

But it limits to only one same record. Is there any possibilities to use count or any other relevant functions in this query based on the need. Hope you help me.

输出应该是这样的，

---- ---------     ------
 id     name       points
---- ---------     ------
 3      cccc         90      -- maximum (only one)

 14     iiii         87      |
 7      gggg         87      | -- 2nd maximum (allow 2*2 =4 only)
 2      bbbb         87      |
 16     iiii         86      |

 20     hhhh         73      |
 19     hhhh         73      | -- 3rd maximum (allow 3*2 =6 only)
 17     iiii         73      |
 14     iiii         73      |
 23     hhhh         70      | and so on for 4th and 5th
 21     hhhh         70      |

 24     hhhh         50      | -- 4th 4*2 = 8

推荐答案

/*Sample data*/
CREATE TABLE t
    (`id` int, `name` varchar(4), `points` int)
;

INSERT INTO t
    (`id`, `name`, `points`)
VALUES
    (1, 'aaaa', 90),
    (2, 'bbbb', 87),
    (3, 'cccc', 90),
    (4, 'dddd', 80),
    (5, 'eeee', 86),
    (6, 'ffff', 82),
    (7, 'gggg', 87),
    (8, 'hhhh', 85),
    (10, 'iiii', 86),
    (11, 'iiii', 86),
    (12, 'iiii', 86),
    (13, 'iiii', 86),
    (14, 'iiii', 87),
    (15, 'iiii', 73),
    (16, 'iiii', 86),
    (17, 'iiii', 73),
    (18, 'hhhh', 85),
    (19, 'hhhh', 73),
    (20, 'hhhh', 73),
    (21, 'hhhh', 70),
    (22, 'hhhh', 65),
    (23, 'hhhh', 70),
    (24, 'hhhh', 50)
;

<小时>

/*Query*/
(
  SELECT id, name, points, 'maximum (only one)' AS maximum, 'just the max' AS group_range
  FROM t
  ORDER BY points DESC, id DESC
  LIMIT 1
)
UNION ALL
(
SELECT id, name, points, CONCAT(n, ' maximum'), CONCAT('range from ', group_max, ' to ', group_max - 10) FROM (
    SELECT
    t.*
    , @n := IF(points < @group_max - 10, @n + 1, @n) AS n
    , @group_max := IF(@n != @prev_n, @group_max - 11, @group_max) AS group_max

    , @row := IF(@n != @prev_n, 1, @row + 1)
    , IF(@row > @n * 2, 0, 1) AS select_it
    , @prev_n := @n
    FROM
    t
    , (SELECT @prev_n := 2, @max := points, @group_max := (SELECT points FROM t WHERE points != (SELECT MAX(points) FROM t) ORDER BY points DESC LIMIT 1), @n := 2, @row := 0 FROM t ORDER BY points DESC LIMIT 1) var_init_subquery
    WHERE points != @max
    ORDER BY points DESC, id DESC
) sq
WHERE select_it = 1
);

<小时>

注意，我认为你把想要的结果搞砸了.添加了两列来证明:)

Note, you messed up your desired result a bit I think. Added two columns to prove that :)

/*Result*/
| ID | NAME | POINTS |            MAXIMUM |         GROUP_RANGE |
|----|------|--------|--------------------|---------------------|
|  3 | cccc |     90 | maximum (only one) |        just the max |
| 14 | iiii |     87 |          2 maximum | range from 87 to 77 |
|  7 | gggg |     87 |          2 maximum | range from 87 to 77 |
|  2 | bbbb |     87 |          2 maximum | range from 87 to 77 |
| 16 | iiii |     86 |          2 maximum | range from 87 to 77 |
| 20 | hhhh |     73 |          3 maximum | range from 76 to 66 |
| 19 | hhhh |     73 |          3 maximum | range from 76 to 66 |
| 17 | iiii |     73 |          3 maximum | range from 76 to 66 |
| 15 | iiii |     73 |          3 maximum | range from 76 to 66 |
| 23 | hhhh |     70 |          3 maximum | range from 76 to 66 |
| 21 | hhhh |     70 |          3 maximum | range from 76 to 66 |
| 22 | hhhh |     65 |          4 maximum | range from 65 to 55 |
| 24 | hhhh |     50 |          5 maximum | range from 54 to 44 |

  • 现场演示

    • 澄清后更新:

    (
  SELECT id, name, points, 'maximum (only one)' AS maximum, 'just the max' AS group_range
  FROM t
  ORDER BY points DESC, id DESC
  LIMIT 1
)
UNION ALL
(
SELECT id, name, points, CONCAT(n, ' maximum'), CONCAT('range from ', group_max, ' to ', group_max - 10) FROM (
    SELECT
    t.*
    , @n := IF(points < @group_max - 10, @n + 1, @n) AS n
    , @group_max := IF(@n != @prev_n, points, @group_max) AS group_max

    , @row := IF(@n != @prev_n, 1, @row + 1)
    , IF(@row > @n * 2, 0, 1) AS select_it
    , @prev_n := @n
    FROM
    t
    , (SELECT @prev_n := 2, @max := points, @group_max := (SELECT points FROM t WHERE points != (SELECT MAX(points) FROM t) ORDER BY points DESC LIMIT 1), @n := 2, @row := 0 FROM t ORDER BY points DESC LIMIT 1) var_init_subquery
    WHERE points != @max
    ORDER BY points DESC, id DESC
) sq
WHERE select_it = 1
);

    <小时>

    | ID | NAME | POINTS |            MAXIMUM |         GROUP_RANGE |
|----|------|--------|--------------------|---------------------|
|  3 | cccc |     90 | maximum (only one) |        just the max |
| 14 | iiii |     87 |          2 maximum | range from 87 to 77 |
|  7 | gggg |     87 |          2 maximum | range from 87 to 77 |
|  2 | bbbb |     87 |          2 maximum | range from 87 to 77 |
| 16 | iiii |     86 |          2 maximum | range from 87 to 77 |
| 20 | hhhh |     73 |          3 maximum | range from 73 to 63 |
| 19 | hhhh |     73 |          3 maximum | range from 73 to 63 |
| 17 | iiii |     73 |          3 maximum | range from 73 to 63 |
| 15 | iiii |     73 |          3 maximum | range from 73 to 63 |
| 23 | hhhh |     70 |          3 maximum | range from 73 to 63 |
| 21 | hhhh |     70 |          3 maximum | range from 73 to 63 |
| 24 | hhhh |     50 |          4 maximum | range from 50 to 40 |

    这篇关于Mysql查询n * 2格式的最大记录数，范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
PHP最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆