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

问题描述

我有这样的表 records

---- ---------     ------
 id     name       points
---- ---------     ------
 1      aaaa         88
 2      bbbb         87
 3      cccc         88
 4      dddd         87
 5      eeee         86
 6      ffff         87
 7      gggg         87
 8      hhhh         85
 10     iiii         86
 11     iiii         86
 12     iiii         86
 13     iiii         86
 14     iiii         86
 15     iiii         86
 16     iiii         87
 17     iiii         82
 18     hhhh         85

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

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

1. 对于第一个最高记录，最高分将排在首位.(但只有一个记录).我有两条最高点88的记录，这里应该显示最近的一条(最大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 88, Here the recent one should be displayed (maximum id)

从第二条记录开始，应以 (n*2) 的格式检索字段.我的意思是，对于第二个最大记录，我可以允许 4 个值相等 (2*2=4).即在表中有几个 87，但我只选择最近的 4 条记录(最大 id).类似地，对于第三个最大值，我可以允许 3*2=6 条记录相同..依此类推..

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 equal (2*2=4). i.e in the table there are several 87s, but I select only recent 4 records (Maximum id). similarly for 3rd maximum, I can allow 3*2=6 records to be same.. and so on..

目前，我已经尝试过

select * from records group by points order by points desc

但它仅限于一个相同的记录.是否有可能根据需要在此查询中使用 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         88      -- maximum (only one)
 16     iiii         87      |
 6      ffff         87      | -- 2nd maximum (allow 2*2 =4 only)
 7      gggg         87      |
 4      dddd         87      |
 15     iiii         86      |
 14     iiii         86      |
 13     iiii         86      | -- 3rd maximum (allow 3*2 =6 only)
 12     iiii         86      |
 11     iiii         86      |
 10     iiii         86      | and so on for 4th and 5th
 18     hhhh         85
 8      hhhh         85
 17     iiii         82

推荐答案

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

INSERT INTO t
    (`id`, `name`, `points`)
VALUES

    ('1', 'aaaa', '88'),
    ('2', 'bbbb', '87'),
    ('3', 'cccc', '88'),
    ('4', 'dddd', '87'),
    ('5', 'eeee', '86'),
    ('6', 'ffff', '87'),
    ('7', 'gggg', '87'),
    ('8', 'hhhh', '85'),
    ('10', 'iiii', '86'),
    ('11', 'iiii', '86'),
    ('12', 'iiii', '86'),
    ('13', 'iiii', '86'),
    ('14', 'iiii', '86'),
    ('15', 'iiii', '86'),
    ('16', 'iiii', '87'),
    ('17', 'iiii', '82'),
    ('18', 'hhhh', '85')
;

<小时>

/*Query*/
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
;

<小时>

/*Result*/
| ID | NAME | POINTS |
|----|------|--------|
|  3 | cccc |     88 |
| 16 | iiii |     87 |
|  7 | gggg |     87 |
|  6 | ffff |     87 |
|  4 | dddd |     87 |
| 15 | iiii |     86 |
| 14 | iiii |     86 |
| 13 | iiii |     86 |
| 12 | iiii |     86 |
| 11 | iiii |     86 |
| 10 | iiii |     86 |
| 18 | hhhh |     85 |
|  8 | hhhh |     85 |
| 17 | iiii |     82 |

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