英文:
MariaDB: Show TOP 5 results per member (in pivot table)
问题
我有一个在我的MariaDB数据库中的表，看起来像这样（简化的示例）；
| 日期 | 会员 | 成绩 |
|---|---|---|
| 2023年1月3日 | 保罗 | 10 |
| 2023年2月3日 | 保罗 | 6 |
| 2023年3月3日 | 保罗 | 9 |
| 2023年4月3日 | 保罗 | 9 |
| 2023年5月3日 | 保罗 | 6 |
| 2023年6月3日 | 保罗 | 5 |
| 2023年7月3日 | 保罗 | 3 |
| 2023年2月3日 | 萨拉 | 8 |
| 2023年3月3日 | 萨拉 | 9 |
| 等等。 |
我想要做的是在透视表中显示每个会员的前5个成绩（最好按日期排序）。如果结果少于5个，我希望其余的成绩要么显示为0，要么为NULL（两者都可以）。每个成绩+日期的组合都应视为单独的结果。在上面的表中，保罗的前5个成绩应该是10、9、9、6、6。
因此，为了说明，期望的结果应该是：
| 会员 | 成绩1 | 成绩2 | 成绩3 | 成绩4 | 成绩5 |
|---|---|---|---|---|---|
| 保罗 | 10 | 6 | 9 | 9 | 6 |
| 萨拉 | 8 | 9 | 0 | 0 | 0 |
我似乎无法弄清楚如何做到这一点。我知道如何创建透视表，但我之前创建的那些是根据某个其他字段（如月份）进行分组的。我不太确定如何在顶部5个结果的情况下做到这一点，尤其是按成绩获得的日期顺序。
英文:
I have a table in my MariaDB database that looks like this (simplified example);
| Date | Member | Grade |
|---|---|---|
| 1-3-2023 | Paul | 10 |
| 2-3-2023 | Paul | 6 |
| 3-3-2023 | Paul | 9 |
| 4-3-2023 | Paul | 9 |
| 5-3-2023 | Paul | 6 |
| 6-3-2023 | Paul | 5 |
| 7-3-2023 | Paul | 3 |
| 2-3-2023 | Sarah | 8 |
| 3-3-2023 | Sarah | 9 |
and so on.
What I'd like to do is show the TOP 5 Grades per member (preferably sorted by date) in a pivot table. If there is less than 5 results I'd like the rest to be either displayed as 0 or NULL (either one is fine). Each combination of grade+date should be treated as a seperate result. In the above table the top 5 grades of Paul should be 10,9,9,6,6
So to illustrate, the desired result would be;
| Member | Grade 1 | Grade 2 | Grade 3 | Grade 4 | Grade 5 |
|---|---|---|---|---|---|
| Paul | 10 | 6 | 9 | 9 | 6 |
| Sarah | 8 | 9 | 0 | 0 | 0 |
I can't seem to figure out how to do this. I know how to create a pivot table but the ones I created before were grouped by a certain other field (like month). I'm not quite sure how to do this with the Top 5 results. Certainly not when having them in the order of the date the grade was achieved.
答案1
得分: 0
已根据Barmar的建议解决。
对于任何在这个问题上遇到困难的人，我通过使用以下查询获得了我想要的结果：
SELECT Member,
MAX(CASE WHEN Rank = 1 THEN Grade END) as '1',
MAX(CASE WHEN Rank = 2 THEN Grade END) as '2',
MAX(CASE WHEN Rank = 3 THEN Grade END) as '3',
MAX(CASE WHEN Rank = 4 THEN Grade END) as '4',
MAX(CASE WHEN Rank = 5 THEN Grade END) as '5'
FROM (SELECT RANK() OVER(PARTITION BY member ORDER BY Grade DESC) Rank, Member, Grade FROM table) pivot
GROUP BY Member
英文:
Solved after suggestion by Barmar.
For anyone struggling with this question, I got my desired result by using this query;
SELECT Member,
MAX(CASE WHEN Rank = 1 THEN Grade END) as '1',
MAX(CASE WHEN Rank = 2 THEN Grade END) as '2',
MAX(CASE WHEN Rank = 3 THEN Grade END) as '3',
MAX(CASE WHEN Rank = 4 THEN Grade END) as '4',
MAX(CASE WHEN Rank = 5 THEN Grade END) as '5',
FROM (SELECT RANK() OVER(PARTITION BY member ORDER BY Grade DESC) Rank, Member, Grade FROM table) pivot
GROUP BY Member
答案2
得分: -1
以下是您要翻译的内容：
What I do is
SELECT * FROM table ORDER BY Grade DESC;
This sorts the table in descending order and then stores it in a variable.
Then I iterate through while also creating a counter variable
i=1;
while(r.next()&&i<=5)
{
.....
i++;
}
英文:
What I do is
SELECT * FROM table ORDER BY Grade DESC;
This sort the table in descending order then store in a variable.
then I iterate through while also creating a counter variable
i=1;
while(r.next()&&i<=5)
{
.....
i++;
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论