mySQL“获得前100名得分"查询让我发疯 [英] mySQL "get TOP 100 scores" query is turning me crazy

问题描述

我从星期五开始问这个问题我一直在尝试我能想到的一切，但都没有成功

I have asked this question Friday and since then I have been trying everything I could think of with no success

我无法使它正常工作

SELECT * FROM WorldFlowers_table GROUP BY device_id ORDER BY score DESC LIMIT 100 

然后回报我:

• 得分最高的100行

• the 100 rows with the top 100 scores

已过滤，以便仅显示每个device_id的最高得分

按分数DESC排序

@ 1000111和Giorgos Betsos

一个男人走进一个房间，说:请帮助，我疯狂地尝试着所有的力量来解决一个简单的问题，任何人都可以在眨眼之间解决" >

2个家伙转过身，朝那个家伙开枪并说重复"

我精疲力尽:(

////////// 来草莓评论

///////// to adress Strawberry comment

我尝试在SQLFiddle中构建它

I tried building that in SQLFiddle

CREATE TABLE WorldFlowers_table 
    (
     id int identity primary key, 
     timestamp varchar(20), 
     name varchar(30),
     score int,
     color varchar(30),
     flower varchar(30),
     device_id varchar(30),
    );

INSERT INTO WorldFlowers_table
(timestamp, name,score, color, flower, device_id  )
VALUES
('1475151826', 'RI-RI', 42, '0|0|0|0|0|0|0|0|0|0', '[1475152768|42|4|0.0]', 'XYZ' ),
('1475151826', 'RO-RO', 46, '0|0|0|0|0|0|0|0|0|0', '[1475152768|42|4|0.0]', 'ABC' ),
('1475151826', 'RI-RI', 42, '0|0|0|0|0|0|0|0|0|0', '[1475152768|42|4|0.0]', 'XYZ' ),
('1475151826', 'RA-RA', 45, '0|0|0|0|0|0|0|0|0|0', '[1475152768|42|4|0.0]', 'ABC' ),
('1475151826', 'RU-RU', 42, '0|0|0|0|0|0|0|0|0|0', '[1475152768|42|4|0.0]', 'XYZ' ),
('1475151826', 'RE-RE', 44, '0|0|0|0|0|0|0|0|0|0', '[1475152768|42|4|0.0]', 'DEF' ),
('1475151826', 'RY-RY', 42, '0|0|0|0|0|0|0|0|0|0', '[1475152768|42|4|0.0]', 'XYZ' ),
('1475151826', 'RX-RX', 43, '0|0|0|0|0|0|0|0|0|0', '[1475152768|42|4|0.0]', 'XYZ' ),
('1475151826', 'RA-RA', 42, '0|0|0|0|0|0|0|0|0|0', '[1475152768|42|4|0.0]', 'DEF' );

但确实只在2中建立1次.

but is does only builds one time out of 2.

那么我想要的回报就是

+------+------------+--------------------------------------------+
| id   | timestamp  | name  | score | color | flower | device_id |                               
+------+------------+--------------------------------------------+
| 2    | blabla     | RO-RO |  46   |  ...  |   ...  |   ABC     | 
| 5    | blabla     | RE-RE |  44   |  ...  |   ...  |   DEF     |
| 7    | blabla     | RX-RX |  43   |  ...  |   ...  |   XYZ     |
+------+-------+-------------------------------------------------+

.每次返回每个设备ID中只有1个

. only 1 of each device-id per return

.每个设备ID最高得分

. the highest score per device-id

.结果在ORDER BY分数DESC中给出

. and the result being given in ORDER BY score DESC

更新:来自scaisEdge的此方法似乎工作正常

UPDATE: this from scaisEdge seems to work fine

选择*从分数 WHERE(名称，分数)IN(选择名称，MAX(分数) 从分数 GROUP BY名称
按分数排序 ) 按分数排序 极限100;

SELECT * FROM scores WHERE (name, score) IN ( SELECT name, MAX(score) FROM scores GROUP BY name
ORDER BY score DESC ) ORDER BY score DESC LIMIT 100 ;

推荐答案

如果我不理解，您希望获得设备得分最高的前100名

If i undestand right you want the top 100 highest device score

  SELECT *
  FROM WorldFlowers_table
  WHERE (device_id, score) IN (SELECT device_id, max(score) 
                               FROM WorldFlowers_table 
                               GROUP BY device_id )
  ORDER BY score DESC 
  LIMIT 100 

这篇关于mySQL“获得前100名得分"查询让我发疯的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！