使用Springboot和mybatis框架开发,获取中奖奖品的周排名 月排名 年排名 降序排列 取前5条数据
时间: 2024-03-28 19:42:28 浏览: 23
好的,根据您的要求,可以使用以下代码实现:
首先,在mapper.xml中编写获取中奖奖品周排名的语句:
```xml
<select id="getWeeklyRanking" resultType="java.util.Map">
SELECT prize_name, COUNT(*) AS count
FROM prize_table
WHERE YEARWEEK(DATE_FORMAT(prize_time,'%Y-%m-%d')) = YEARWEEK(NOW())
GROUP BY prize_name
ORDER BY count DESC
LIMIT 5;
</select>
```
然后,在mapper接口中定义对应的方法:
```java
public interface PrizeMapper {
List<Map<String, Object>> getWeeklyRanking();
}
```
接着,在service中调用mapper的方法,实现获取中奖奖品周排名的功能:
```java
@Service
public class PrizeService {
@Autowired
private PrizeMapper prizeMapper;
public List<Map<String, Object>> getWeeklyRanking() {
return prizeMapper.getWeeklyRanking();
}
}
```
同理,编写获取中奖奖品月排名和年排名的语句:
```xml
<!-- 获取中奖奖品月排名 -->
<select id="getMonthlyRanking" resultType="java.util.Map">
SELECT prize_name, COUNT(*) AS count
FROM prize_table
WHERE DATE_FORMAT(prize_time,'%Y-%m')=DATE_FORMAT(NOW(),'%Y-%m')
GROUP BY prize_name
ORDER BY count DESC
LIMIT 5;
</select>
<!-- 获取中奖奖品年排名 -->
<select id="getYearlyRanking" resultType="java.util.Map">
SELECT prize_name, COUNT(*) AS count
FROM prize_table
WHERE YEAR(prize_time) = YEAR(NOW())
GROUP BY prize_name
ORDER BY count DESC
LIMIT 5;
</select>
```
同样在mapper接口中定义对应的方法:
```java
public interface PrizeMapper {
List<Map<String, Object>> getWeeklyRanking();
List<Map<String, Object>> getMonthlyRanking();
List<Map<String, Object>> getYearlyRanking();
}
```
在service中调用mapper的方法,实现获取中奖奖品月排名和年排名的功能:
```java
@Service
public class PrizeService {
@Autowired
private PrizeMapper prizeMapper;
public List<Map<String, Object>> getWeeklyRanking() {
return prizeMapper.getWeeklyRanking();
}
public List<Map<String, Object>> getMonthlyRanking() {
return prizeMapper.getMonthlyRanking();
}
public List<Map<String, Object>> getYearlyRanking() {
return prizeMapper.getYearlyRanking();
}
}
```
最后,在controller中调用service的方法,并返回数据给前端:
```java
@RestController
public class PrizeController {
@Autowired
private PrizeService prizeService;
@GetMapping("/weeklyRanking")
public List<Map<String, Object>> getWeeklyRanking() {
return prizeService.getWeeklyRanking();
}
@GetMapping("/monthlyRanking")
public List<Map<String, Object>> getMonthlyRanking() {
return prizeService.getMonthlyRanking();
}
@GetMapping("/yearlyRanking")
public List<Map<String, Object>> getYearlyRanking() {
return prizeService.getYearlyRanking();
}
}
```
以上代码实现了使用Springboot和mybatis框架开发,获取中奖奖品的周排名、月排名、年排名,降序排列,取前5条数据的功能。