基于多表关联实现用户投注积分更新的技术问询

Alright, let's tackle this problem step by step. Here's how you can update user points in vm_table based on their bets and match results:

核心逻辑梳理 #

First, we need to connect the dots between three pieces of data:

• Completed matches (where vm_matches.res is not NULL)
• Each user's bets stored in their individual table (vm_{uid})
• The user's current points in vm_table

The basic flow is:

1. Identify matches that have a final result
2. Check each user's bets against these results
3. Calculate points based on how many bets were correct (per your scoring rules)
4. Add the calculated points to the user's total in vm_table

假设积分规则 #

Let's start with a common scoring rule to demonstrate:

• Exact score match: +3 points per correct bet
• If you need different rules (like +1 point for correct outcome, regardless of exact score), we can adjust the logic later.

单用户SQL示例 #

For a single user (e.g., uid 207), here's how to calculate their correct bets and update points:

Step 1: Count correct bets #

-- Count how many of user 207's bets match completed match results
SELECT COUNT(*) AS correct_bets
FROM vm_matches m
JOIN vm_207 b ON m.id = b.id  -- Assumes bet table `id` matches match table `id`
WHERE m.res IS NOT NULL AND m.res = b.bet;

Step 2: Update user's points #

-- Add 3 points per correct bet to user 207's total
UPDATE vm_table
SET points = points + (
    SELECT COUNT(*) * 3
    FROM vm_matches m
    JOIN vm_207 b ON m.id = b.id
    WHERE m.res IS NOT NULL AND m.res = b.bet
)
WHERE uid = 207;

批量处理所有用户（脚本示例） #

Since each user has their own bet table, we need to loop through all users in vm_table to process their bets. Here's a Python script using MySQL as an example:

import mysql.connector

# Database connection details - update these with your own
db_config = {
    "host": "your_database_host",
    "user": "your_username",
    "password": "your_password",
    "database": "your_database_name"
}

# Connect to the database
db = mysql.connector.connect(**db_config)
cursor = db.cursor()

# Scoring rule: 3 points per exact match
POINTS_PER_CORRECT = 3

# Get all user IDs from the leaderboard
cursor.execute("SELECT uid FROM vm_table")
users = cursor.fetchall()

for (uid,) in users:
    # Dynamically build the bet table name for the user
    bet_table_name = f"vm_{uid}"
    
    # Calculate total points to add
    calculate_query = f"""
        SELECT COUNT(*) * {POINTS_PER_CORRECT}
        FROM vm_matches m
        JOIN {bet_table_name} b ON m.id = b.id
        WHERE m.res IS NOT NULL AND m.res = b.bet
    """
    cursor.execute(calculate_query)
    points_to_add = cursor.fetchone()[0]
    
    # Update the user's points
    update_query = f"""
        UPDATE vm_table
        SET points = points + {points_to_add}
        WHERE uid = {uid}
    """
    cursor.execute(update_query)

# Save changes to the database
db.commit()

# Clean up connections
cursor.close()
db.close()

扩展积分规则（e.g., correct outcome + exact score） #

If you want to award points for both exact score and correct outcome (win/loss/draw), adjust the scoring logic like this:

-- For user 207, calculate points per bet
SELECT 
    SUM(
        CASE
            -- Exact score match: 3 points
            WHEN m.res = b.bet THEN 3
            -- Correct outcome (win/loss/draw): 1 point
            WHEN (SUBSTRING_INDEX(m.res, '-', 1) > SUBSTRING_INDEX(m.res, '-', -1) AND SUBSTRING_INDEX(b.bet, '-', 1) > SUBSTRING_INDEX(b.bet, '-', -1))
                 OR (SUBSTRING_INDEX(m.res, '-', 1) < SUBSTRING_INDEX(m.res, '-', -1) AND SUBSTRING_INDEX(b.bet, '-', 1) < SUBSTRING_INDEX(b.bet, '-', -1))
                 OR (SUBSTRING_INDEX(m.res, '-', 1) = SUBSTRING_INDEX(m.res, '-', -1) AND SUBSTRING_INDEX(b.bet, '-', 1) = SUBSTRING_INDEX(b.bet, '-', -1))
            THEN 1
            -- No points for incorrect bets
            ELSE 0
        END
    ) AS total_points
FROM vm_matches m
JOIN vm_207 b ON m.id = b.id
WHERE m.res IS NOT NULL;

You can plug this into the batch script or single-user update to use this scoring system.

表结构优化建议 #

Having a separate bet table for each user can get messy as your user base grows. A better approach is to use a single vm_bets table with all users' bets:

This makes bulk queries way easier. Here's how you'd update all users' points in one go with this structure:

UPDATE vm_table t
JOIN (
    SELECT 
        b.uid,
        SUM(
            CASE
                WHEN m.res = b.bet THEN 3
                WHEN (SUBSTRING_INDEX(m.res, '-', 1) > SUBSTRING_INDEX(m.res, '-', -1) AND SUBSTRING_INDEX(b.bet, '-', 1) > SUBSTRING_INDEX(b.bet, '-', -1))
                     OR (SUBSTRING_INDEX(m.res, '-', 1) < SUBSTRING_INDEX(m.res, '-', -1) AND SUBSTRING_INDEX(b.bet, '-', 1) < SUBSTRING_INDEX(b.bet, '-', -1))
                     OR (SUBSTRING_INDEX(m.res, '-', 1) = SUBSTRING_INDEX(m.res, '-', -1) AND SUBSTRING_INDEX(b.bet, '-', 1) = SUBSTRING_INDEX(b.bet, '-', -1))
                THEN 1
                ELSE 0
            END
        ) AS total_points
    FROM vm_matches m
    JOIN vm_bets b ON m.id = b.match_id
    WHERE m.res IS NOT NULL
    GROUP BY b.uid
) AS user_scores ON t.uid = user_scores.uid
SET t.points = t.points + user_scores.total_points;

This is far more efficient and maintainable than handling individual tables.

内容的提问来源于stack exchange，提问作者Daniel Jensen