使用SQLite将列中分隔数据拆分多行并提取唯一传感器名称

没问题，我帮你搞定这个SQLite的传感器数据拆分需求！要把Sensor列里用|分隔的多个值拆成单独行，再拿到所有唯一的传感器名称，这里有两种实用的方案，根据你的SQLite版本选就行：

方法1：递归CTE（推荐，适用于SQLite 3.8.3及以上版本） #

SQLite从3.8.3开始支持递归CTE，这是最灵活的拆分方法，不需要额外创建辅助表：

WITH RECURSIVE split_sensors AS (
    -- 初始步骤：提取每个行的第一个传感器名称和剩余未拆分的部分
    SELECT 
        RowID,
        SUBSTR(Sensor, 1, INSTR(Sensor || '|', '|') - 1) AS sensor_name,
        SUBSTR(Sensor, INSTR(Sensor || '|', '|') + 1) AS remaining_sensors
    FROM Data
    WHERE Sensor IS NOT NULL AND Sensor != ''
    
    UNION ALL
    
    -- 递归步骤：不断拆分剩余的传感器字符串，直到没有剩余内容
    SELECT 
        RowID,
        SUBSTR(remaining_sensors, 1, INSTR(remaining_sensors || '|', '|') - 1) AS sensor_name,
        SUBSTR(remaining_sensors, INSTR(remaining_sensors || '|', '|') + 1) AS remaining_sensors
    FROM split_sensors
    WHERE remaining_sensors IS NOT NULL AND remaining_sensors != ''
)
-- 去重并统一大小写（解决示例里Eyetracker/EyeTracker的大小写差异问题）
SELECT DISTINCT LOWER(sensor_name) AS unique_sensor
FROM split_sensors
ORDER BY unique_sensor;

这段代码的逻辑是：先把每个Sensor字符串拆成「第一个传感器名称」和「剩下的字符串」，然后递归处理剩下的字符串，直到所有部分都被拆成单独的行。最后用DISTINCT去重，LOWER()把名称统一为小写，避免大小写差异导致的重复。

方法2：辅助数字表（兼容旧版本SQLite） #

如果你的SQLite版本低于3.8.3，不支持递归CTE，可以用一个简单的数字辅助表来实现拆分：

首先创建一个包含足够多数字的辅助表（数字数量要大于你数据中最多的传感器个数，比如示例里最多3个，所以建到5个足够）：

CREATE TABLE IF NOT EXISTS nums(n INTEGER);
INSERT INTO nums(n) VALUES(1),(2),(3),(4),(5);

然后用这个表来拆分Sensor列并去重：

SELECT DISTINCT LOWER(
    SUBSTR(
        Sensor, 
        (n-1)*LENGTH('|') + 1, 
        INSTR(SUBSTR(Sensor, (n-1)*LENGTH('|') + 1) || '|', '|') - 1
    )
) AS unique_sensor
FROM Data
JOIN nums ON n <= (LENGTH(Sensor) - LENGTH(REPLACE(Sensor, '|', '')) + 1)
WHERE Sensor IS NOT NULL AND Sensor != ''
ORDER BY unique_sensor;

这个方法的逻辑是：先计算每个Sensor字符串里有多少个分隔符，从而知道有多少个传感器名称，然后用数字表的每个数字对应一个位置，截取对应的传感器名称，最后同样去重和统一大小写。

小提示 #

• 如果你不需要忽略大小写（比如Eyetracker和EyeTracker要视为不同的传感器），直接去掉LOWER()函数即可。
• 如果传感器名称前后可能有空格，可以在SUBSTR外面套一个TRIM()来清理。

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