java - 执行SQL查询以排序列表，而不必将其存储为Java对象？

我在MySQL中有一个包含3列的表：CategoryID，Score，dateTime。我需要找到得分最高的前4个类别。我还必须考虑每个分数的时间戳，因为如果我在较旧的日期在某个类别中得分较高，而在最近的日期中该类别得分较低，则最近的分数将主导该类别的分数。 SQL中是否存在查询以生成结果集？我在Java中通过将数据存储为对象来完成此操作，但是如果有更有效的方法，我更愿意这样做。

注意：pickRandom是字符串的ArrayList，并且忽略方法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;
                }
            }

编辑：

表：分数

'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'

最佳答案

请尝试以下操作。 inr派生表依靠唯一的时间/秒时间分段。也许可以使用id列进行增强。 inr将在我的示例数据中获得所有6个类别。外部将加入并说它只想要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');

选项A ：（按date_time DESC排在前4位）

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)

选项B：（relevanceScore DESC排名前4）

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)