SQL技术需求：统计Collection表记录在Query Expression中的引用次数

首先明确需求：给collection表新增reference_count列，统计每个CollectionID在query_rule表的QueryExpression字段中的出现次数（可选择统计被引用的规则条数或总出现次数）。

1. 新增reference_count列 #

先执行DDL语句添加列，默认值设为0（未被引用时保持0）：

-- MySQL/PostgreSQL/SQL Server
ALTER TABLE collection ADD COLUMN reference_count INT DEFAULT 0;

-- Oracle
ALTER TABLE collection ADD reference_count NUMBER(10) DEFAULT 0;

2. 统计并更新引用次数 #

根据你使用的数据库类型，选择对应的实现方式：

场景1：统计被多少条规则引用（同一规则中多次出现只算1次） #

MySQL

UPDATE collection c
JOIN (
    SELECT c.CollectionID, COUNT(*) AS cnt
    FROM collection c
    JOIN query_rule q
    ON LOCATE(c.CollectionID, q.QueryExpression) > 0
    GROUP BY c.CollectionID
) AS counts
ON c.CollectionID = counts.CollectionID
SET c.reference_count = counts.cnt;

PostgreSQL

UPDATE collection c
SET reference_count = (
    SELECT COUNT(*)
    FROM query_rule q
    WHERE STRPOS(q.QueryExpression, c.CollectionID) > 0
);

SQL Server

UPDATE collection
SET reference_count = ISNULL(
    (SELECT COUNT(*) FROM query_rule q WHERE CHARINDEX(collection.CollectionID, q.QueryExpression) > 0),
    0
);

Oracle

UPDATE collection c
SET reference_count = (
    SELECT COUNT(*)
    FROM query_rule q
    WHERE INSTR(q.QueryExpression, c.CollectionID) > 0
);
-- 对未被引用的记录显式设为0（可选，因默认值已设为0）
UPDATE collection c
SET reference_count = 0
WHERE reference_count IS NULL;

场景2：统计总出现次数（同一规则中多次出现累加计数） #

如果需要统计CollectionID在所有QueryExpression中的实际出现次数（比如某条规则里引用2次就算2次），用字符串替换的方式计算：

MySQL

UPDATE collection c
JOIN (
    SELECT c.CollectionID, SUM(
        (LENGTH(q.QueryExpression) - LENGTH(REPLACE(q.QueryExpression, c.CollectionID, ''))) 
        / LENGTH(c.CollectionID)
    ) AS cnt
    FROM collection c
    JOIN query_rule q
    ON LOCATE(c.CollectionID, q.QueryExpression) > 0
    GROUP BY c.CollectionID
) AS counts
ON c.CollectionID = counts.CollectionID
SET c.reference_count = counts.cnt;

PostgreSQL

UPDATE collection c
SET reference_count = (
    SELECT SUM(
        (LENGTH(q.QueryExpression) - LENGTH(REPLACE(q.QueryExpression, c.CollectionID, ''))) 
        / LENGTH(c.CollectionID)
    )
    FROM query_rule q
    WHERE STRPOS(q.QueryExpression, c.CollectionID) > 0
);

SQL Server

UPDATE collection
SET reference_count = ISNULL(
    (SELECT SUM(
        (LEN(q.QueryExpression) - LEN(REPLACE(q.QueryExpression, collection.CollectionID, ''))) 
        / LEN(collection.CollectionID)
    ) FROM query_rule q WHERE CHARINDEX(collection.CollectionID, q.QueryExpression) > 0),
    0
);

Oracle

UPDATE collection c
SET reference_count = (
    SELECT SUM(
        (LENGTH(q.QueryExpression) - LENGTH(REPLACE(q.QueryExpression, c.CollectionID, ''))) 
        / LENGTH(c.CollectionID)
    )
    FROM query_rule q
    WHERE INSTR(q.QueryExpression, c.CollectionID) > 0
);

注意事项 #

• 如果CollectionID包含特殊字符（比如%、_），部分数据库的字符串匹配函数可能需要转义，避免误匹配。
• 若数据量较大，建议先测试查询语句的性能，必要时给query_rule.QueryExpression创建全文索引（根据数据库支持情况）。

内容的提问来源于stack exchange，提问作者karockiad