SQL选择每个用户订单的3个最佳分数和其他数据

发布于 2025-01-25 17:34:36 字数 2996 浏览 0 评论 0原文

可能是你可以帮助我。我想从我的桌子上展示一个名人堂。但是我想选择难度选择，每个 pseudo 以得分< /strong> desc。对于每个人，都在行中显示所有信息。

我使用Mariadb 10.3.31。 My Table named 'Loopz'

Id	Pseudo	Score	Difficulty	Duration	PiecesPerMinute	LoopsPerMinute
1	Thy	1333	1	42932	44.00	1.33
2	P0lux	1433	1	43222	46.33	1.50
3	Sky	600	1	25348	20.28	0.88
4	Thy	2500	1	53124	46.94	2.05
5	Alex	1200	0	21000	30.35	1.23
6	Thy	4200	1	60652	48.17	2.24
7	P0LUX	3364	1	52734	39.37	1.96

等...

我尝试了这个

SELECT r.*
FROM
(
    SELECT
        r.*,
        ROW_NUMBER() OVER(PARTITION BY r.Pseudo
                          ORDER BY r.Score DESC) rn
    FROM Loopz r
) r
WHERE r.rn <= 3 and Difficulty=1
ORDER BY r.Score DESC;

，但我不明白为什么它缺少某些数据。选择了一些伪的音符，而有些时间仅显示2个最佳分数...

最好的问候

原文

May be you can help me.
I would like to display an Hall of Fame From my table with rank.
But i would like to select about Difficulty choice, 3 bests Score for each Pseudo in limit to 25 rows maximum order by Score desc.
And for each, display all information in row.

I use MariaDB 10.3.31. My Table named 'Loopz'

Id	Pseudo	Score	Difficulty	Duration	PiecesPerMinute	LoopsPerMinute
1	Thy	1333	1	42932	44.00	1.33
2	P0lux	1433	1	43222	46.33	1.50
3	Sky	600	1	25348	20.28	0.88
4	Thy	2500	1	53124	46.94	2.05
5	Alex	1200	0	21000	30.35	1.23
6	Thy	4200	1	60652	48.17	2.24
7	P0lux	3364	1	52734	39.37	1.96

etc...

i Try this

SELECT r.*
FROM
(
    SELECT
        r.*,
        ROW_NUMBER() OVER(PARTITION BY r.Pseudo
                          ORDER BY r.Score DESC) rn
    FROM Loopz r
) r
WHERE r.rn <= 3 and Difficulty=1
ORDER BY r.Score DESC;

But i don't understand why it's missing some data. Some Pseudo are note selected, and some time display only 2 best scores...

Best regards

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

苍风燃霜 2025-02-01 17:34:36

我认为这就是您要寻找的。我已经使用group_concat将3个分数放在一行上。我没有应用过滤器难度= 1，因为您已经说过您看不到想要的所有行。
第一种是desc <代码>订单，我们有限制25。这意味着，一旦您有25人难以= 1，就不会出现困难0。

 创建表Loopz（
  id int，
  伪VARCHAR（25）， 
  得分int，
  困难int， 
  持续时间int，
  零件的十进制（5,2），   
  Loopsperminute十进制（5,2）
  ）；

 插入Loopz值
（1，'thy'，1333,1,42932,44.00,1.33），
（2，'p0lux'，1433,1,43222,46.33,1.50），
（3，'Sky'，600,1,25348,20.28,0.88），
（4，'thy'，2500,1,53124,46.94,2.05），
（5，'Alex'，1200,0,21000,30.35,1.23），
（6，'thy'，4200,1,60652,48.17,2.24），
（7，'p0lux'，3364,1,52734,39.37,1.96）;

 排名为
（选择
  伪，
  分数，
  困难，
  级别（）超过（通过伪订单按得分desc分区）RN
来自loopz）
选择
  伪，
  group_concat（得分顺序）得分，
  最大（难度）困难
从排名
rn＆lt; 4
伪组的组
订购 
  困难，desc，
  最大（得分）desc
限制25;

伪|分数|困难
：----- | ：--------------- | ------------：
你| 1333,2500,4200 | 1
p0lux | 1433,3364 | 1
天空| 600 | 1
亚历克斯| 1200 | 0

dB＆lt;

I think that this is what you are looking for. I've used GROUP_CONCAT to put the 3 scores on one row. I haven't applied the filter Difficulty = 1 because you have already said that you don't see all the rows that you want.
The first sort is ORDER BY Difficulty DESC and we have LIMIT 25. This means that as soon you have 25 persons with Difficulty = 1 there will not be anyone shown with Difficulty 0.

CREATE TABLE Loopz (
  Id int,
  Pseudo VARCHAR(25), 
  Score   int,
  Difficulty int, 
  Duration int,
  PiecesPerMinute decimal(5,2),   
  LoopsPerMinute decimal(5,2)
  );

insert into Loopz values
(1,'Thy',1333,1,42932,44.00,1.33),
(2,'P0lux',1433,1,43222,46.33,1.50),
(3,'Sky',600,1,25348,20.28,0.88),
(4,'Thy',2500,1,53124,46.94,2.05),
(5,'Alex',1200,0,21000,30.35,1.23),
(6,'Thy',4200,1,60652,48.17,2.24),
(7,'P0lux',3364,1,52734,39.37,1.96);

WITH ranking as
(SELECT
  Pseudo,
  Score,
  Difficulty,
  Rank() OVER (PARTITION BY Pseudo ORDER BY SCORE DESC) rn
FROM Loopz)
SELECT
  Pseudo,
  GROUP_CONCAT(Score ORDER BY Score) Scores,
  MAX(Difficulty) Difficulty
FROM ranking
WHERE rn < 4
GROUP BY Pseudo
ORDER BY 
  Difficulty DESC,
  MAX(Score) DESC
LIMIT 25;

Pseudo | Scores         | Difficulty
:----- | :------------- | ---------:
Thy    | 1333,2500,4200 |          1
P0lux  | 1433,3364      |          1
Sky    | 600            |          1
Alex   | 1200           |          0

db<>fiddle here

回复收藏 0 原文

~没有更多了~