SELECT
username,
CONCAT_WS(' / ', username, email) AS name_card,
UPPER(username) AS uname_upper,
SUBSTRING_INDEX(email, '@', 1) AS email_local,
SUBSTRING_INDEX(email, '@', -1) AS email_domain,
CHAR_LENGTH(username) AS name_chars,
LENGTH(username) AS name_bytes
FROM cf_user;
SELECT
s.status,
CASE s.status
WHEN 'accepted' THEN '通过'
WHEN 'wrong_answer' THEN '答案错误'
WHEN 'runtime_error' THEN '运行时错误'
WHEN 'time_limit' THEN '超时'
END AS status_cn,
COUNT(*) AS cnt
FROM cf_submission s
GROUP BY s.status, status_cn
ORDER BY cnt DESC;
SELECT
CASE
WHEN runtime_ms >= 1000 THEN '超慢'
WHEN runtime_ms >= 50 THEN '偏慢'
ELSE '正常'
END AS speed_tier,
COUNT(*) AS cnt
FROM cf_submission
GROUP BY speed_tier;
-- 预期：超慢 1（2000ms）、正常 11
步骤 3：空值三件套
-- 空集聚合返回 NULL，用 COALESCE 兜底
SELECT COALESCE(SUM(runtime_ms), 0) AS total_runtime
FROM cf_submission WHERE user_id = 6; -- qian 无提交，结果 0 而不是 NULL
-- IFNULL 是两参数版 COALESCE 的 MySQL 简写
SELECT IFNULL(NULL, '默认值') AS a, COALESCE(NULL, NULL, '兜底') AS b;
-- NULLIF(a, b)：相等则返回 NULL，常用于防除零
SELECT 100 / NULLIF(0, 0) AS safe_div; -- NULL 而不是报错
步骤 4：日期函数——日报与月报
-- 按月统计
SELECT DATE_FORMAT(submitted_at, '%Y-%m') AS ym, COUNT(*) AS cnt
FROM cf_submission
GROUP BY DATE_FORMAT(submitted_at, '%Y-%m');
-- 预期：2025-06 → 12
-- 按天统计 + 两个间隔函数
SELECT
DATE(submitted_at) AS d,
COUNT(*) AS cnt,
DATEDIFF('2025-06-08', DATE(submitted_at)) AS days_before_0608,
TIMESTAMPDIFF(HOUR, submitted_at, '2025-06-08 00:00:00') AS hours_before
FROM cf_submission
GROUP BY DATE(submitted_at)
ORDER BY d;
SELECT
DATE_FORMAT(submitted_at, '%Y-%m-%d') AS d,
COUNT(*) AS total_sub,
SUM(status = 'accepted') AS ac_cnt,
ROUND(SUM(status = 'accepted') / COUNT(*) * 100, 1) AS ac_rate,
ROUND(AVG(runtime_ms), 1) AS avg_ms
FROM cf_submission
GROUP BY DATE_FORMAT(submitted_at, '%Y-%m-%d')
ORDER BY d;