如何在SQLite中比较版本字符串并查询低于指定版本的条目？

Great question! Let's break this down step by step—version string comparison in SQLite can be tricky without built-in parsing functions, but we'll get this sorted. First, let's address your specific questions, then share a complete, working solution.

Q1: How to integrate the split CTE with your actual table (instead of hardcoding a string) #

The example CTE you got works for a single hardcoded string, but to apply it to every row in your software table, you need to tie the CTE to each row individually. Your earlier attempt using (select version from software) failed because that returns all versions at once, while the CTE's initial row expects a single value.

Instead, reference the main table's version column directly in the CTE's starting SELECT statement. This way, the CTE runs once per row, splitting only that row's version string. For example, to extract the major version for a row s in software:

(SELECT CAST(word AS INTEGER) FROM (
    WITH split(word, str) AS (
        SELECT '', s.version || '.'  -- Reference the current row's version here
        UNION ALL
        SELECT substr(str, 0, instr(str, '.')), substr(str, instr(str, '.')+1)
        FROM split WHERE str != ''
    )
    SELECT word FROM split WHERE word != '' LIMIT 1 OFFSET 0
)) AS major

Q2: Where to place the target version (2.7.0.0) #

Version numbers are compared left-to-right, with each segment having higher priority than the ones to its right. For 2.7.0.0, split it into four integer segments (2, 7, 0, 0) and use these as thresholds in your WHERE clause. You'll build conditions that check:

1. Any version with a major version less than 2
2. Versions with major version equal to 2, but minor version less than 7
3. (Edge case) Versions with major=2, minor=7, revision less than 0 (unlikely, but included for completeness)
4. (Edge case) Versions with major=2, minor=7, revision=0, patch less than 0 (also unlikely)

Complete Working Query #

Here's a clean, maintainable version that pre-extracts all four version segments for every row, then applies the comparison logic:

WITH parsed_versions AS (
    SELECT 
        version,
        -- Extract major version (1st segment)
        (SELECT CAST(word AS INTEGER) FROM (
            WITH split(word, str) AS (
                SELECT '', s.version || '.'
                UNION ALL
                SELECT substr(str, 0, instr(str, '.')), substr(str, instr(str, '.')+1)
                FROM split WHERE str != ''
            )
            SELECT word FROM split WHERE word != '' LIMIT 1 OFFSET 0
        )) AS v1,
        -- Extract minor version (2nd segment)
        (SELECT CAST(word AS INTEGER) FROM (
            WITH split(word, str) AS (
                SELECT '', s.version || '.'
                UNION ALL
                SELECT substr(str, 0, instr(str, '.')), substr(str, instr(str, '.')+1)
                FROM split WHERE str != ''
            )
            SELECT word FROM split WHERE word != '' LIMIT 1 OFFSET 1
        )) AS v2,
        -- Extract revision version (3rd segment)
        (SELECT CAST(word AS INTEGER) FROM (
            WITH split(word, str) AS (
                SELECT '', s.version || '.'
                UNION ALL
                SELECT substr(str, 0, instr(str, '.')), substr(str, instr(str, '.')+1)
                FROM split WHERE str != ''
            )
            SELECT word FROM split WHERE word != '' LIMIT 1 OFFSET 2
        )) AS v3,
        -- Extract patch version (4th segment)
        (SELECT CAST(word AS INTEGER) FROM (
            WITH split(word, str) AS (
                SELECT '', s.version || '.'
                UNION ALL
                SELECT substr(str, 0, instr(str, '.')), substr(str, instr(str, '.')+1)
                FROM split WHERE str != ''
            )
            SELECT word FROM split WHERE word != '' LIMIT 1 OFFSET 3
        )) AS v4
    FROM software s
)
SELECT version
FROM parsed_versions
WHERE 
    v1 < 2
    OR (v1 = 2 AND v2 < 7)
    OR (v1 = 2 AND v2 = 7 AND v3 < 0)
    OR (v1 = 2 AND v2 = 7 AND v3 = 0 AND v4 < 0);

Key Notes: #

• Casting each segment to INTEGER avoids string comparison bugs (e.g., string '10' would be considered less than '2', but as integers it's larger).
• The OFFSET clause picks which segment we want: OFFSET 0 is the first segment, OFFSET 1 the second, etc.
• The edge cases for v3 <0 and v4 <0 can be omitted if you know your version segments are never negative—they're included here for full compliance with version comparison rules.

Simplified Alternative (SQLite 3.33.0+) #

If you're using SQLite 3.33.0 or newer, you can use LATERAL JOIN to avoid repeating the split CTE multiple times. This makes the code shorter and easier to maintain:

SELECT s.version
FROM software s
LATERAL (
    SELECT 
        CAST(word AS INTEGER) AS part,
        row_number() OVER () AS idx
    FROM (
        WITH split(word, str) AS (
            SELECT '', s.version || '.'
            UNION ALL
            SELECT substr(str, 0, instr(str, '.')), substr(str, instr(str, '.')+1)
            FROM split WHERE str != ''
        )
        SELECT word FROM split WHERE word != ''
    )
) AS segments
PIVOT (
    MAX(part) FOR idx IN (1 AS v1, 2 AS v2, 3 AS v3, 4 AS v4)
)
WHERE 
    v1 < 2
    OR (v1 = 2 AND v2 <7)
    OR (v1=2 AND v2=7 AND v3<0)
    OR (v1=2 AND v2=7 AND v3=0 AND v4<0)
GROUP BY s.version;

This uses PIVOT (available in SQLite 3.37.0+) to turn split segments into columns, keeping the comparison logic just as clear.

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