36 lines
942 B
SQL
36 lines
942 B
SQL
-- 查询所有有邮箱的达人，检查是否已有知识库映射
|
|
SELECT
|
|
fc.id as creator_id,
|
|
fc.handle as handle,
|
|
fc.email as email,
|
|
fc.contact_person as contact_person,
|
|
fc.fans_count as fans_count,
|
|
fc.tiktok_url as tiktok_url,
|
|
gtm.knowledge_base_id as kb_id,
|
|
kb.name as kb_name,
|
|
kb.document_count as doc_count
|
|
FROM
|
|
feishu_creators fc
|
|
LEFT JOIN
|
|
gmail_talent_mappings gtm ON fc.email = gtm.talent_email AND gtm.is_active = 1
|
|
LEFT JOIN
|
|
knowledge_bases kb ON gtm.knowledge_base_id = kb.id
|
|
WHERE
|
|
fc.email IS NOT NULL AND fc.email != ''
|
|
ORDER BY
|
|
fc.updated_at DESC
|
|
LIMIT
|
|
50;
|
|
|
|
-- FIELD_MAPPING:
|
|
{
|
|
"creator_id": "达人ID",
|
|
"handle": "达人Handle",
|
|
"email": "达人邮箱",
|
|
"contact_person": "对接人",
|
|
"fans_count": "粉丝数",
|
|
"tiktok_url": "链接",
|
|
"kb_id": "知识库ID",
|
|
"kb_name": "知识库名称",
|
|
"doc_count": "文档数量"
|
|
} |