74 lines
1.8 KiB
MySQL
74 lines
1.8 KiB
MySQL
|
-- 测试邀请码功能
|
|||
|
|
-- 执行时间：2026-02-03
|
|||
|
|
|
|||
|
|
USE fastadmin;
|
|||
|
|
|
|||
|
|
-- 1. 查看当前用户的邀请码信息
|
|||
|
|
SELECT
|
|||
|
|
id,
|
|||
|
|
username,
|
|||
|
|
nickname,
|
|||
|
|
invite_code AS '邀请码',
|
|||
|
|
invited_by AS '被谁邀请',
|
|||
|
|
invite_count AS '邀请人数',
|
|||
|
|
invite_reward_total AS '邀请奖励',
|
|||
|
|
money AS '金币余额'
|
|||
|
|
FROM nf_user
|
|||
|
|
WHERE id IN (
|
|||
|
|
SELECT id FROM nf_user
|
|||
|
|
ORDER BY id DESC
|
|||
|
|
LIMIT 10
|
|||
|
|
)
|
|||
|
|
ORDER BY id DESC;
|
|||
|
|
|
|||
|
|
-- 2. 查看有邀请码的用户
|
|||
|
|
SELECT
|
|||
|
|
id,
|
|||
|
|
username,
|
|||
|
|
nickname,
|
|||
|
|
invite_code AS '邀请码',
|
|||
|
|
invite_count AS '邀请人数',
|
|||
|
|
invite_reward_total AS '邀请奖励',
|
|||
|
|
money AS '金币余额'
|
|||
|
|
FROM nf_user
|
|||
|
|
WHERE invite_code IS NOT NULL
|
|||
|
|
ORDER BY invite_count DESC, id DESC
|
|||
|
|
LIMIT 20;
|
|||
|
|
|
|||
|
|
-- 3. 查看使用了邀请码的用户
|
|||
|
|
SELECT
|
|||
|
|
id,
|
|||
|
|
username,
|
|||
|
|
nickname,
|
|||
|
|
invited_by AS '使用的邀请码',
|
|||
|
|
money AS '金币余额',
|
|||
|
|
createtime AS '注册时间'
|
|||
|
|
FROM nf_user
|
|||
|
|
WHERE invited_by IS NOT NULL
|
|||
|
|
ORDER BY id DESC
|
|||
|
|
LIMIT 20;
|
|||
|
|
|
|||
|
|
-- 4. 查看邀请相关的金币日志
|
|||
|
|
SELECT
|
|||
|
|
l.id,
|
|||
|
|
l.user_id AS '用户ID',
|
|||
|
|
u.username AS '用户名',
|
|||
|
|
l.money AS '金币变动',
|
|||
|
|
l.before AS '变动前',
|
|||
|
|
l.after AS '变动后',
|
|||
|
|
l.memo AS '备注',
|
|||
|
|
FROM_UNIXTIME(l.createtime) AS '时间'
|
|||
|
|
FROM nf_user_money_log l
|
|||
|
|
LEFT JOIN nf_user u ON l.user_id = u.id
|
|||
|
|
WHERE l.memo LIKE '%邀请%'
|
|||
|
|
ORDER BY l.createtime DESC
|
|||
|
|
LIMIT 20;
|
|||
|
|
|
|||
|
|
-- 5. 统计邀请数据
|
|||
|
|
SELECT
|
|||
|
|
COUNT(DISTINCT CASE WHEN invite_code IS NOT NULL THEN id END) AS '有邀请码的用户数',
|
|||
|
|
COUNT(DISTINCT CASE WHEN invited_by IS NOT NULL THEN id END) AS '使用邀请码的用户数',
|
|||
|
|
SUM(CASE WHEN invite_code IS NOT NULL THEN invite_count ELSE 0 END) AS '总邀请人数',
|
|||
|
|
SUM(CASE WHEN invite_code IS NOT NULL THEN invite_reward_total ELSE 0 END) AS '总邀请奖励'
|
|||
|
|
FROM nf_user;
|