MySQL排行榜周榜月榜

原创

mob64ca12e7f20c 2024-11-07 14:57:57 ©著作权

文章标签 SQL 数据 MySQL 文章分类 MySQL 数据库

©著作权归作者所有：来自51CTO博客作者mob64ca12e7f20c的原创作品，请联系作者获取转载授权，否则将追究法律责任

MySQL排行榜开发指南

1. 引言

排行榜是一项常见的功能，广泛应用于游戏、社交平台等场景，它可以根据用户的行为数据进行积分排行。本文将分享如何使用 MySQL 来实现一个简单的周榜和月榜。

2. 实现流程

在实现排行榜之前，我们需要明确整个过程的步骤。以下是实现排行榜的基本步骤：

步骤	描述
1	设计数据库表，用于存放用户和积分数据
2	插入用户积分数据
3	编写SQL查询语句，获取周榜和月榜的前十名用户
4	结果展示

3. 数据库设计

在做排行榜之前，我们需要设计数据库。以下是需要的表：

users 表：存放用户信息
scores 表：存放用户积分

数据库ER图

使用Mermaid语法来表示ER图如下：

erDiagram
    USERS {
        int id PK "用户ID"
        string username "用户名"
    }
    SCORES {
        int id PK "记录ID"
        int user_id FK "用户ID"
        int score "积分"
        datetime created_at "创建时间"
    }
    
    USERS ||--o| SCORES : has

SQL创建表的代码

首先，我们需要创建这两个表格。以下是相应的SQL语句：

-- 创建用户表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,  -- 用户ID，自增主键
    username VARCHAR(50) NOT NULL       -- 用户名，非空
);

-- 创建积分表
CREATE TABLE scores (
    id INT AUTO_INCREMENT PRIMARY KEY,   -- 记录ID，自增主键
    user_id INT,                         -- 用户ID，外键
    score INT NOT NULL,                  -- 积分，非空
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,  -- 创建时间，默认为当前时间
    FOREIGN KEY (user_id) REFERENCES users(id)    -- 设置外键约束
);

4. 实现周榜和月榜的查询逻辑

接下来，我们将编写SQL查询以获取周榜和月榜的数据。

查询周榜

周榜可以通过 created_at 字段来限制查询的范围，下面的SQL代码获取过去7天内的积分前10名用户。

SELECT u.username, SUM(s.score) AS total_score
FROM scores s
JOIN users u ON s.user_id = u.id
WHERE s.created_at >= NOW() - INTERVAL 7 DAY  -- 过去7天
GROUP BY u.id
ORDER BY total_score DESC  -- 按积分降序排列
LIMIT 10;  -- 只取前10名

查询月榜

类似的，月榜的查询也需要限制时间范围，但这次我们查询的是过去30天的数据。

SELECT u.username, SUM(s.score) AS total_score
FROM scores s
JOIN users u ON s.user_id = u.id
WHERE s.created_at >= NOW() - INTERVAL 30 DAY  -- 过去30天
GROUP BY u.id
ORDER BY total_score DESC  -- 按积分降序排列
LIMIT 10;  -- 只取前10名