67 lines
1.2 KiB
MySQL
67 lines
1.2 KiB
MySQL
|
-- 检查消息通知功能的数据
|
||
|
|
|
||
|
|
-- 1. 查看最近的通知记录
|
||
|
|
SELECT
|
||
|
|
id,
|
||
|
|
user_id,
|
||
|
|
title,
|
||
|
|
content,
|
||
|
|
type,
|
||
|
|
is_read,
|
||
|
|
related_id,
|
||
|
|
related_type,
|
||
|
|
create_time
|
||
|
|
FROM notification
|
||
|
|
ORDER BY create_time DESC
|
||
|
|
LIMIT 20;
|
||
|
|
|
||
|
|
-- 2. 查看陪伴员的userId映射关系
|
||
|
|
SELECT
|
||
|
|
t.id as teacher_id,
|
||
|
|
t.user_id,
|
||
|
|
t.name,
|
||
|
|
t.real_name,
|
||
|
|
t.phone,
|
||
|
|
t.audit_status,
|
||
|
|
u.id as user_table_id,
|
||
|
|
u.nickname,
|
||
|
|
u.phone as user_phone
|
||
|
|
FROM teacher t
|
||
|
|
LEFT JOIN user u ON t.user_id = u.id
|
||
|
|
WHERE t.audit_status = 1
|
||
|
|
ORDER BY t.id;
|
||
|
|
|
||
|
|
-- 3. 查看提醒表的数据
|
||
|
|
SELECT
|
||
|
|
id,
|
||
|
|
title,
|
||
|
|
description,
|
||
|
|
type,
|
||
|
|
status,
|
||
|
|
remind_time,
|
||
|
|
create_time
|
||
|
|
FROM reminder
|
||
|
|
ORDER BY create_time DESC
|
||
|
|
LIMIT 10;
|
||
|
|
|
||
|
|
-- 4. 统计各用户的通知数量
|
||
|
|
SELECT
|
||
|
|
user_id,
|
||
|
|
COUNT(*) as total_count,
|
||
|
|
SUM(CASE WHEN is_read = 0 THEN 1 ELSE 0 END) as unread_count,
|
||
|
|
MAX(create_time) as last_notification_time
|
||
|
|
FROM notification
|
||
|
|
GROUP BY user_id
|
||
|
|
ORDER BY user_id;
|
||
|
|
|
||
|
|
-- 5. 检查是否有userId为NULL的陪伴员
|
||
|
|
SELECT
|
||
|
|
id,
|
||
|
|
name,
|
||
|
|
real_name,
|
||
|
|
phone,
|
||
|
|
audit_status,
|
||
|
|
user_id
|
||
|
|
FROM teacher
|
||
|
|
WHERE audit_status = 1 AND user_id IS NULL;
|