Executing an SQL query to order a list without hav

问题:

I have a table in MySQL with 3 columns: CategoryID, Score, dateTime. I need to find top 4 categories with the highest Scores. I must also consider the timestamps each of these scores has because if I score high in a category at an old date, but low on a category on a more recent date, then the recent Score dominates what the score of the category is. Is there a query in SQL to generate a result set? I have done it in java by storing the data as objects, but if there is a more efficient way, I prefer to do that.

Note: pickRandom is an ArrayList of Strings, and ignore the method checkIfThere()

Scores prevScore = new Scores(0, "AD",  new Timestamp(0));
            while (rs.next()) {

                categoryID = rs.getString("categoryID");
                int relScore = rs.getInt("relevanceScore");
                Timestamp ts = rs.getTimestamp("date_time");

                Scores currScore = new Scores(relScore, categoryID, ts);

                // ensures that a Random Barrier Cateogry is prompted
                if ((currScore.getScore() >= prevScore.getScore() && (currScore.getDate().compareTo(prevScore.getDate()) > 0) && !checkIfThere(user, categoryID))) {
                    pickRandom.add(categoryID);
                    prevScore = currScore;
                }
            }

Edit:

Table: Scores

'AD', '110', '2015-08-26 14:59:51' 
'DC', '110', '2015-08-26 14:59:51' 
'DO', '110', '2015-08-26 14:59:51' 
'EC', '8.087601288990395', '2015-08-26 14:59:53' 
'EC', '110', '2015-08-26 14:59:51'

回答1:

Give something like the following a try. The inr derived table is banking on a unique time/second timesplit. Perhaps can be enhanced with the id column. The inr will get all 6 categories in my sample data. The outer will join and say it only wants 4.

create table scores
(   id int auto_increment primary key,
    categoryID varchar(10) not null,
    relevanceScore float not null,
    date_time datetime not null
);
truncate table scores;
insert scores (categoryID,relevanceScore,date_time) values
('AD', '110', '2015-08-26 14:59:51'), 
('DC', '110', '2015-08-26 14:59:51'), 
('DO', '110', '2015-08-26 14:59:51'), 
('EC', '8.087601288990395', '2015-08-26 14:59:53'), 
('EC', '110', '2015-08-26 14:59:51'),
('OLD1', '110', '2014-08-26 14:59:51'),
('OLD2', '110', '2014-08-26 14:59:51');

Option A: (top 4 by date_time DESC)

select s.* from scores s 
join 
( select categoryID,max(date_time) as maxdt 
  from scores 
  group by categoryId 
) inr 
on inr.categoryID=s.categoryID and inr.maxdt=s.date_time 
order by s.date_time desc 
limit 4; 

+----+------------+----------------+---------------------+
| id | categoryID | relevanceScore | date_time           |
+----+------------+----------------+---------------------+
|  4 | EC         |         8.0876 | 2015-08-26 14:59:53 |
|  1 | AD         |            110 | 2015-08-26 14:59:51 |
|  2 | DC         |            110 | 2015-08-26 14:59:51 |
|  3 | DO         |            110 | 2015-08-26 14:59:51 |
+----+------------+----------------+---------------------+
4 rows in set (0.00 sec)

option B: (top 4 by relevanceScore DESC)

select s.* from scores s 
join 
( select categoryID,max(date_time) as maxdt 
  from scores 
  group by categoryId 
) inr 
on inr.categoryID=s.categoryID and inr.maxdt=s.date_time 
order by s.relevanceScore desc
limit 4; 
+----+------------+----------------+---------------------+
| id | categoryID | relevanceScore | date_time           |
+----+------------+----------------+---------------------+
|  1 | AD         |            110 | 2015-08-26 14:59:51 |
|  2 | DC         |            110 | 2015-08-26 14:59:51 |
|  3 | DO         |            110 | 2015-08-26 14:59:51 |
|  6 | OLD1       |            110 | 2014-08-26 14:59:51 |
+----+------------+----------------+---------------------+
4 rows in set (0.00 sec)

回答2:

Try this

---

select * from Scores order by date_time,relevanceScore;