如何在数据库中构建记录分类关联并改造现有查询视图？

核心思路 #

要实现目标视图，关键是对每条记录，判断它是否属于每个分类，并将结果以列的形式展示。同一记录的所有行（对应不同分类的行），在分类列的标记要保持一致（比如Record1的所有行，Category1-3列都标X）。

因为有200个分类，手动编写每个分类的判断逻辑不现实，所以需要用动态SQL自动生成视图结构。

静态示例（针对少量分类） #

如果分类数量少，可以手动编写SQL创建视图。假设现有视图名为record_category_view，示例代码如下（以MySQL为例）：

CREATE OR REPLACE VIEW record_category_extended_view AS
SELECT 
    rc.Records,
    rc.Categories,
    -- 判断当前记录是否属于Category1
    CASE WHEN EXISTS (SELECT 1 FROM record_category_view rc2 WHERE rc2.Records = rc.Records AND rc2.Categories = 'Category1') THEN 'X' ELSE '' END AS Category1,
    CASE WHEN EXISTS (SELECT 1 FROM record_category_view rc2 WHERE rc2.Records = rc.Records AND rc2.Categories = 'Category2') THEN 'X' ELSE '' END AS Category2,
    CASE WHEN EXISTS (SELECT 1 FROM record_category_view rc2 WHERE rc2.Records = rc.Records AND rc2.Categories = 'Category3') THEN 'X' ELSE '' END AS Category3,
    CASE WHEN EXISTS (SELECT 1 FROM record_category_view rc2 WHERE rc2.Records = rc.Records AND rc2.Categories = 'Category4') THEN 'X' ELSE '' END AS Category4
FROM record_category_view rc;

动态SQL实现（适配200个分类） #

针对大量分类，用动态SQL自动生成所有分类列的判断逻辑，以下是主流数据库的实现方式：

MySQL 实现 #

通过存储过程自动生成视图：

DELIMITER //
CREATE PROCEDURE CreateExtendedRecordCategoryView()
BEGIN
    DECLARE category_list TEXT DEFAULT '';
    DECLARE done INT DEFAULT FALSE;
    DECLARE cat_name VARCHAR(255);
    -- 游标遍历所有唯一分类
    DECLARE cur CURSOR FOR SELECT DISTINCT Categories FROM record_category_view ORDER BY Categories;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO cat_name;
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- 拼接每个分类对应的CASE语句
        SET category_list = CONCAT(category_list,
            ', CASE WHEN EXISTS (SELECT 1 FROM record_category_view rc2 WHERE rc2.Records = rc.Records AND rc2.Categories = ''', cat_name, ''') THEN ''X'' ELSE '''' END AS `', cat_name, '`');
    END LOOP;
    CLOSE cur;

    -- 生成创建视图的完整SQL并执行
    SET @create_view_sql = CONCAT('CREATE OR REPLACE VIEW record_category_extended_view AS SELECT rc.Records, rc.Categories', category_list, ' FROM record_category_view rc');
    PREPARE stmt FROM @create_view_sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

-- 调用存储过程生成视图
CALL CreateExtendedRecordCategoryView();

SQL Server 实现 #

DECLARE @category_list NVARCHAR(MAX) = '';

-- 拼接所有分类的CASE语句
SELECT @category_list = @category_list + 
    ', CASE WHEN EXISTS (SELECT 1 FROM record_category_view rc2 WHERE rc2.Records = rc.Records AND rc2.Categories = ''' + Categories + ''') THEN ''X'' ELSE '''' END AS [' + Categories + ']'
FROM (SELECT DISTINCT Categories FROM record_category_view) AS cats
ORDER BY Categories;

-- 生成并执行创建视图的SQL
DECLARE @create_view_sql NVARCHAR(MAX) = 
    'CREATE OR ALTER VIEW record_category_extended_view AS SELECT rc.Records, rc.Categories' + @category_list + ' FROM record_category_view rc';

EXEC sp_executesql @create_view_sql;

PostgreSQL 实现 #

DO $$
DECLARE
    category_list TEXT := '';
    cat_record RECORD;
BEGIN
    -- 遍历所有唯一分类，拼接CASE语句
    FOR cat_record IN SELECT DISTINCT Categories FROM record_category_view ORDER BY Categories LOOP
        category_list := category_list || 
            ', CASE WHEN EXISTS (SELECT 1 FROM record_category_view rc2 WHERE rc2.Records = rc.Records AND rc2.Categories = ''' || cat_record.Categories || ''') THEN ''X'' ELSE '''' END AS "' || cat_record.Categories || '"';
    END LOOP;

    -- 创建视图
    EXECUTE 'CREATE OR REPLACE VIEW record_category_extended_view AS SELECT rc.Records, rc.Categories' || category_list || ' FROM record_category_view rc';
END $$;

注意事项 #

1. 分类更新同步：视图创建后，若新增或删除分类，需要重新执行动态SQL来更新视图结构，因为视图是静态的，不会自动同步分类变化。
2. 特殊字符处理：如果分类名称包含空格、引号等特殊字符，上述代码已通过反引号（MySQL）、方括号（SQL Server）、双引号（PostgreSQL）包裹列名，避免语法错误。

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