obsidian/技术支持/为知笔记/进阶.md

---

notion-id: 244ca4d7-cb7b-805c-8ffb-dbe8e073a2

查询wiz.kb和wiz.tag之间的关系语句

-- 查询能匹配上的wiz_tag记录（显示HEX格式的GUID）
SELECT DISTINCT
    HEX(wt.KB_GUID) as KB_GUID_HEX,
    wt.TAG_NAME,
    wk.KB_NAME,
    wk.KB_GUID as KB_GUID_ORIGINAL
FROM wizksent.wiz_tag wt
INNER JOIN wizasent.wiz_kb wk 
    ON HEX(wt.KB_GUID) = UPPER(REPLACE(wk.KB_GUID, '-', ''))
WHERE wk.KB_NAME IS NOT NULL
ORDER BY wt.TAG_NAME, wk.KB_NAME;

#查询没有TAG的文章
SELECT 
  HEX(d.DOCUMENT_GUID) AS DOCUMENT_GUID,
  d.DOCUMENT_TITLE,
  d.BODY_TEXT
FROM 
  wiz_document d
LEFT JOIN 
  wiz_document_tag t ON d.DOCUMENT_GUID = t.DOCUMENT_GUID
WHERE 
  d.BODY_TEXT IS NOT NULL 
  AND d.BODY_TEXT REGEXP '[[:graph:]]'
  AND t.DOCUMENT_GUID IS NULL;

SELECT HEX(DOCUMENT_GUID) AS DOCUMENT_GUID, DOCUMENT_TITLE, BODY_TEXT
FROM wiz_document 
#WHERE DOCUMENT_TITLE LIKE "%拒绝事%" 
WHERE BODY_TEXT IS NOT NULL 
AND BODY_TEXT REGEXP '[[:graph:]]';

SELECT HEX(DOCUMENT_GUID) AS DOCUMENT_GUID, HEX(TAG_GUID) AS TAG_GUID 
FROM wiz_document_tag
WHERE DOCUMENT_GUID = UNHEX('5152EA06297F4A13A11E2DF8D1388F8B');

SELECT TAG_NAME FROM
wiz_tag
where TAG_GUID = UNHEX('76925DF070B511EFB4BDC1083A359C1F');

SHOW TABLES FROM wizksent;

DESCRIBE wizksent.wiz_tag;

-- 正确显示标签数据
SELECT 
    HEX(TAG_GUID) as TAG_GUID,
    HEX(KB_GUID) as KB_GUID, 
    TAG_NAME,
    HEX(OPERATOR_GUID) as OPERATOR_GUID,
    DT_CREATED,
    IS_GROUP,
    POS
FROM wizksent.wiz_tag 
ORDER BY POS;

DESCRIBE wiz_document;

-- 查看关联数据（用HEX显示GUID）
SELECT 
    HEX(KB_GUID) as KB_GUID,
    HEX(DOCUMENT_GUID) as DOCUMENT_GUID,
    HEX(TAG_GUID) as TAG_GUID
FROM wizksent.wiz_document_tag 
LIMIT 10;



SELECT 
    d.ID,
    d.DOCUMENT_TITLE as 文章标题,
    t.TAG_NAME as 标签,
    d.DOCUMENT_AUTHOR as 作者,
    d.DT_CREATED as 创建时间
FROM wizksent.wiz_document d
JOIN wizksent.wiz_document_tag dt ON d.DOCUMENT_GUID = dt.DOCUMENT_GUID  
JOIN wizksent.wiz_tag t ON dt.TAG_GUID = t.TAG_GUID
ORDER BY d.ID DESC;


SELECT 
    d.ID,
    d.DOCUMENT_TITLE as 文章标题,
    CASE 
        WHEN t.TAG_NAME IS NULL THEN '未分类'
        ELSE GROUP_CONCAT(t.TAG_NAME SEPARATOR ', ')
    END as 标签,
    d.DOCUMENT_AUTHOR as 作者,
    d.DT_CREATED as 创建时间
FROM wizksent.wiz_document d
LEFT JOIN wizksent.wiz_document_tag dt ON d.DOCUMENT_GUID = dt.DOCUMENT_GUID  
LEFT JOIN wizksent.wiz_tag t ON dt.TAG_GUID = t.TAG_GUID
GROUP BY d.ID, d.DOCUMENT_TITLE, d.DOCUMENT_AUTHOR, d.DT_CREATED
ORDER BY d.ID DESC
LIMIT 100;

-- 查看wiz_kb表的GUID格式（前10条）
SELECT 
  KB_GUID, KB_NAME
FROM wizasent.wiz_kb 
where KB_NAME IS not null;

SELECT 
  HEX(KB_GUID) AS KB_GUID
FROM wizksent.wiz_tag;


-- 查询能匹配上的wiz_tag记录（显示HEX格式的GUID）
SELECT DISTINCT
    HEX(wt.KB_GUID) as KB_GUID_HEX,
    HEX(wt.TAG_GUID),
    wt.TAG_NAME,
    wk.KB_NAME,
    wk.KB_GUID as KB_GUID_ORIGINAL
FROM wizksent.wiz_tag wt
INNER JOIN wizasent.wiz_kb wk 
    ON HEX(wt.KB_GUID) = UPPER(REPLACE(wk.KB_GUID, '-', ''))
WHERE wk.KB_NAME IS NOT NULL
ORDER BY wt.TAG_NAME, wk.KB_NAME;



SELECT 
   HEX(dt.TAG_GUID),d.*
FROM wizksent.wiz_document d
INNER JOIN wizksent.wiz_document_tag dt 
    ON d.DOCUMENT_GUID = dt.DOCUMENT_GUID
ORDER BY DT_CREATED;


SELECT 
    HEX(dt.TAG_GUID) as DOC_TAG_GUID_HEX,
    d.*,
    wt.TAG_NAME,
    wk.KB_NAME
FROM wizksent.wiz_document d
INNER JOIN wizksent.wiz_document_tag dt 
    ON d.DOCUMENT_GUID = dt.DOCUMENT_GUID
INNER JOIN wizksent.wiz_tag wt 
    ON HEX(dt.TAG_GUID) = HEX(wt.TAG_GUID)
INNER JOIN wizasent.wiz_kb wk 
    ON HEX(wt.KB_GUID) = UPPER(REPLACE(wk.KB_GUID, '-', ''))
WHERE wk.KB_NAME IS NOT NULL
ORDER BY DT_CREATED;


SELECT 
    wk.KB_NAME as 一级目录,
    wt.TAG_NAME as 二级目录,
    COUNT(*) as 文档数量
FROM wizksent.wiz_document d
INNER JOIN wizksent.wiz_document_tag dt 
    ON d.DOCUMENT_GUID = dt.DOCUMENT_GUID
INNER JOIN wizksent.wiz_tag wt 
    ON dt.TAG_GUID = wt.TAG_GUID
INNER JOIN wizasent.wiz_kb wk 
    ON HEX(wt.KB_GUID) = UPPER(REPLACE(wk.KB_GUID, '-', ''))
WHERE wk.KB_NAME IS NOT NULL
GROUP BY wk.KB_NAME, wt.TAG_NAME
ORDER BY wk.KB_NAME, wt.TAG_NAME;

SELECT 
    wk.KB_NAME as 一级目录,
    wt.TAG_NAME as 二级目录,
    COUNT(*) as 文档数量,
    MIN(d.DT_CREATED) as 最早创建时间,
    MAX(d.DT_CREATED) as 最晚创建时间
FROM wizksent.wiz_document d
INNER JOIN wizksent.wiz_document_tag dt 
    ON d.DOCUMENT_GUID = dt.DOCUMENT_GUID
INNER JOIN wizksent.wiz_tag wt 
    ON dt.TAG_GUID = wt.TAG_GUID
INNER JOIN wizasent.wiz_kb wk 
    ON HEX(wt.KB_GUID) = UPPER(REPLACE(wk.KB_GUID, '-', ''))
WHERE wk.KB_NAME IS NOT NULL
    AND d.DT_CREATED >= '2025-10-01 00:00:00'
    AND d.DT_CREATED <= '2025-10-30 23:59:59'
GROUP BY wk.KB_NAME, wt.TAG_NAME
ORDER BY wk.KB_NAME, wt.TAG_NAME;

SELECT 
    wk.KB_NAME as 一级目录,
    wt.TAG_NAME as 二级目录,
    COUNT(*) as 文档数量,
    MIN(d.DT_CREATED) as 最早创建时间,
    MAX(d.DT_CREATED) as 最晚创建时间
FROM wizksent.wiz_document d
INNER JOIN wizksent.wiz_document_tag dt 
    ON d.DOCUMENT_GUID = dt.DOCUMENT_GUID
INNER JOIN wizksent.wiz_tag wt 
    ON dt.TAG_GUID = wt.TAG_GUID
INNER JOIN wizasent.wiz_kb wk 
    ON HEX(wt.KB_GUID) = UPPER(REPLACE(wk.KB_GUID, '-', ''))
WHERE wk.KB_NAME IS NOT NULL
    AND d.DT_CREATED >= '2025-10-01 00:00:00'
    AND d.DT_CREATED <= '2025-10-30 23:59:59'
GROUP BY wk.KB_NAME, wt.TAG_NAME
ORDER BY wk.KB_NAME, wt.TAG_NAME;