mysql - 使用 MAX() 和 GROUP BY 的关联子查询

Question

我在使用 MAX() 和 GROUP BY 时遇到问题。我有下一张 table :

个人奖品

 ___________ ___________ _________ __________|    id     |    userId |   specId| group    |  |___________|___________|_________|__________||    1      |    1      |   1     |     1    |  |___________|___________|_________|__________||    2      |    1      |   2     |     1    |  |___________|___________|_________|__________||    3      |    2      |   3     |     1    |  |___________|___________|_________|__________||     4     |    2      |   4     |     2    |  |___________|___________|_________|__________||     5     |    1      |   5     |     2    |  |___________|___________|_________|__________||     6     |    1      |   6     |     2    |  |___________|___________|_________|__________||    7      |    2      |   7     |     3    |  |___________|___________|_________|__________|prizes ___________ ___________ _________ |    id     |    title  |   group |     |___________|___________|_________||    1      |    First  |   1     | |___________|___________|_________||    2      |    Second |   1     | |___________|___________|_________||    3      |    Newby  |   1     | |___________|___________|_________||     4     |    General|   2     | |___________|___________|_________||     5     |    Leter  |   2     | |___________|___________|_________||     6     |    Ter    |   2     | |___________|___________|_________||     7     |    Mentor |   3     | |___________|___________|_________|

So, I need to select highest title for user.E.g. user with id = 1 must have prizes 'Second', 'Ter'. I don't know how to implement it in one query(((So, first of all, I try to select highest specID for user.I try next:

SELECT pp.specID 
FROM personal_prizes pp
WHERE pp.specID IN (SELECT MAX(pp1.id) 
                    FROM personal_prizes pp1
                    WHERE pp1.userId = 1 
                    GROUP BY pp1.group)

而且它不起作用。所以请帮我解决这个问题。如果您能帮助用户选择奖品那就太好了!

Answer 1

我在这里发现的问题是prizes.id并不是真正确定哪个是“最高”奖品的可靠方法。忽略这一点，但我建议使用 ROW_NUMBER() OVER() 来定位每个用户的“最高”奖励，如下所示:

引用这个SQL Fiddle

CREATE TABLE personal_prizes
    ([id] int, [userId] int, [specId] int, [group] int)
;

INSERT INTO personal_prizes
    ([id], [userId], [specId], [group])
VALUES
    (1, 1, 1, 1),
    (2, 1, 2, 1),
    (3, 2, 3, 1),
    (4, 2, 4, 2),
    (5, 1, 5, 2),
    (6, 1, 6, 2),
    (7, 2, 7, 3)
;


CREATE TABLE prizes
    ([id] int, [title] varchar(7), [group] int)
;

INSERT INTO prizes
    ([id], [title], [group])
VALUES
    (1, 'First', 1),
    (2, 'Second', 1),
    (3, 'Newby', 1),
    (4, 'General', 2),
    (5, 'Leter', 2),
    (6, 'Ter', 2),
    (7, 'Mentor', 3)
;

查询 1:

select
*
from (
    select
      pp.*, p.title
    , row_number() over(partition by pp.userId order by p.id ASC) as prize_order
    from personal_prizes pp
    inner join prizes p on pp.specid = p.id
    ) d
where prize_order = 1

<强> Results :

| id | userId | specId | group | title | prize_order |
|----|--------|--------|-------|-------|-------------|
|  1 |      1 |      1 |     1 | First |           1 |
|  3 |      2 |      3 |     1 | Newby |           1 |

可以通过更改 over 子句中的 ORDER BY 来“反转”结果:

select
*
from (
    select
      pp.*, p.title
    , row_number() over(partition by pp.userId order by p.id DESC) as prize_order
    from personal_prizes pp
    inner join prizes p on pp.specid = p.id
    ) d
where prize_order = 1

| id | userId | specId | group |  title | prize_order |
|----|--------|--------|-------|--------|-------------|
|  6 |      1 |      6 |     2 |    Ter |           1 |
|  7 |      2 |      7 |     3 | Mentor |           1 |

您也可以扩展此逻辑来找到“每组最高奖金”

select
*
from (
    select
      pp.*, p.title
    , row_number() over(partition by pp.userId, p.[group] order by p.id ASC) as prize_order
    from personal_prizes pp
    inner join prizes p on pp.specid = p.id
    ) d
where prize_order = 1


| id | userId | specId | group |   title | prize_order |
|----|--------|--------|-------|---------|-------------|
|  1 |      1 |      1 |     1 |   First |           1 |
|  5 |      1 |      5 |     2 |   Leter |           1 |
|  3 |      2 |      3 |     1 |   Newby |           1 |
|  4 |      2 |      4 |     2 | General |           1 |
|  7 |      2 |      7 |     3 |  Mentor |           1 |