如何在SQLite中使用Union All后统计整数出现次数（Python实现）

1. 修正SQL查询逻辑 #

你的原有SQL仅完成了两列合并，但缺少分组统计的核心步骤，同时存在列名/表名的问题（from是SQL保留关键字，需用反引号包裹；col1/col2要替换成实际列名from/to）。正确的SQL语句如下：

SELECT num AS Integers, COUNT(*) AS "No of Occurences"
FROM (
    SELECT `from` AS num FROM your_table
    UNION ALL
    SELECT `to` AS num FROM your_table
) AS combined_data
GROUP BY num
ORDER BY num;

• 替换your_table为你实际的表名
• UNION ALL保留所有重复值，确保统计的是整数的总出现次数
• GROUP BY num按整数分组，COUNT(*)统计每组的出现次数

2. 修正Python代码 #

你的DataFrame列名存在语法错误（[Occurences]是无效写法），同时优化代码可读性后的完整实现：

import sqlite3
import pandas as pd

# 连接SQLite数据库
conn = sqlite3.connect('your_database.db')
cursor = conn.cursor()

# 执行查询
query = """
SELECT num AS Integers, COUNT(*) AS "No of Occurences"
FROM (
    SELECT `from` AS num FROM your_table
    UNION ALL
    SELECT `to` AS num FROM your_table
) AS combined_data
GROUP BY num
ORDER BY num;
"""
cursor.execute(query)

# 生成DataFrame
df = pd.DataFrame(cursor.fetchall(), columns=['Integers', 'No of Occurences'])
# 也可以用更简洁的写法：df = pd.read_sql(query, conn)

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

最终结果 #

执行后会得到你期望的统计结果：

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