如何在SQLite中批量查询所有非系统表的CATEGORY列去重值？

问题原因 #

SQLite不支持直接通过子查询动态指定表名，直接嵌套子查询会触发语法错误，必须通过动态SQL的方式实现需求。

解决方法 #

方法1：手动生成联合查询语句 #

先执行以下SQL，自动拼接出针对所有目标表的联合查询语句：

SELECT GROUP_CONCAT(
    -- 若表名含空格/关键字，改用 'SELECT DISTINCT CATEGORY FROM "' || name || '"'
    'SELECT DISTINCT CATEGORY FROM ' || name,
    ' UNION ALL ' -- 需要全局去重则替换为 ' UNION '
) AS combined_sql
FROM sqlite_schema 
WHERE type='table' 
    AND name NOT LIKE 'sqlite_%'
    -- 提前过滤无CATEGORY列的表，避免执行报错
    AND EXISTS (SELECT 1 FROM pragma_table_info(name) WHERE name = 'CATEGORY');

执行后会得到一个完整的SQL语句，复制该语句再次执行，即可获取所有表的CATEGORY去重值。

方法2：脚本自动化执行（以Python为例） #

如果需要批量自动化处理，可通过编程语言连接数据库动态生成并执行查询：

import sqlite3

# 连接目标数据库
conn = sqlite3.connect('你的数据库文件名.db')
cursor = conn.cursor()

# 获取所有包含CATEGORY列的目标表
cursor.execute("""
    SELECT name
    FROM sqlite_schema 
    WHERE type='table' 
        AND name NOT LIKE 'sqlite_%'
        AND EXISTS (SELECT 1 FROM pragma_table_info(name) WHERE name = 'CATEGORY');
""")
tables = [row[0] for row in cursor.fetchall()]

# 构建联合查询语句
query_parts = [f"SELECT DISTINCT CATEGORY FROM `{table}`" for table in tables]
final_query = ' UNION ALL '.join(query_parts)

# 执行查询并输出结果
cursor.execute(final_query)
for category in cursor.fetchall():
    print(category[0])

# 关闭数据库连接
conn.close()

注意事项 #

• UNION ALL会保留不同表中重复的CATEGORY值，若需要全局去重，替换为UNION即可。
• 若表名包含空格、SQL关键字等特殊字符，需用反引号（`）或双引号（"）包裹表名，避免语法错误。

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