SQLite新手求助：如何生成Station与Port设备的全组合记录

要生成所有Station与Port设备的两两组合，你需要用**交叉连接（CROSS JOIN）**来获取两个数据集的笛卡尔积，这正是你需要的全组合效果。

直接查询获取组合（临时结果） #

执行以下SQL即可直接得到所有Station和Port的组合：

SELECT 
    s.device_id AS station, 
    p.device_id AS port
FROM 
    (SELECT device_id FROM device WHERE LOWER(type) = 'station') s
CROSS JOIN 
    (SELECT device_id FROM device WHERE LOWER(type) = 'port') p;

• 使用LOWER(type)是为了兼容数据中type字段的大小写差异（比如你示例里的Port/port、Station），确保所有目标设备都能被匹配到。

持久化到新表 #

如果需要把组合结果存储到新表中，可以先创建表再插入数据：

-- 创建存储组合的表（如果不存在）
CREATE TABLE IF NOT EXISTS device_combinations (
    station INTEGER,
    port INTEGER
);

-- 插入所有组合数据
INSERT INTO device_combinations (station, port)
SELECT 
    s.device_id AS station, 
    p.device_id AS port
FROM 
    (SELECT device_id FROM device WHERE LOWER(type) = 'station') s
CROSS JOIN 
    (SELECT device_id FROM device WHERE LOWER(type) = 'port') p;

为什么之前的方法不生效？ #

• 递归CTE：用于处理层级结构（如树形数据）或递归迭代场景，不适合生成全组合。
• 联合查询（UNION）：是用来合并多个查询的结果集（去重或不去重），不是生成两个数据集的两两组合。
• 普通连接（INNER/LEFT JOIN）：需要关联条件，而你要的是无关联的全组合，交叉连接才是正确选择。

示例验证 #

针对你给出的示例数据：

执行上述SQL后，结果会是：

完全符合你需要的所有组合要求。

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