74 lines
1.8 KiB
SQL
74 lines
1.8 KiB
SQL
-- 测试邀请码功能
|
||
-- 执行时间：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;
|