起因

客户想要将产品链接和产品型号导出来，本来是非常简单的事情，因为只涉及两个表格 ay_content,ay_content_sort关联一下就可以了

学习点

  1. sqlite用不了的函数

    连接字符串

    在SQLite中，确实没有内置的 CONCAT 函数，但你可以使用 || 运算符来连接字符串

    SQLite 不支持 INTO TEMP TABLE 语法,在 SQLite 中，我们可以使用 CREATE TEMPORARY TABLE 来创建临时表

  2. sql语句的递归函数

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    WITH RECURSIVE CategoryTree AS (
        -- 选择根节点
        SELECT scode
        FROM ay_content_sort
        WHERE scode = 12

        UNION ALL

        -- 递归地选择子节点
        SELECT c.scode
        FROM ay_content_sort c
        JOIN CategoryTree ct ON c.pcode = ct.scode
    )

  3. 非空判断

    1
    2
    3
    4
    CASE 
         WHEN m.filename IS NOT NULL THEN m.filename
         ELSE 'products_' || ifnull(m.scode, '') || ''
     END

  4. 长度判断

    1
    2
    3
    4
    CASE 
            WHEN length(IFNULL(content_filename, '')) > 0 THEN content_filename
            ELSE IFNULL(id, '')
    END

    IFNULL(expression1, expression2)是一个在某些数据库系统（如MySQL）中使用的函数，用于处理可能的NULL值。它的作用是如果expression1为NULL，则返回 expression2；否则，返回 expression1

  5. 临时表创建和删除

    1
    2
    CREATE TEMPORARY TABLE temp_product_data AS
     select * from ay_content;
    1
    DROP TABLE temp_product_data;

  6. 调试思想

    1
    2
    3
    4
    CASE WHEN m.filename IS NULL THEN 'NULL' ELSE 'NOT NULL' END AS debug_s_filename,
    CASE WHEN t.filename IS NULL THEN 'NULL' ELSE 'NOT NULL' END AS debug_c_filename,
    length(IFNULL(m.filename, '')) AS len_sort_filename,
    length(IFNULL(t.filename, '')) AS len_content_filename,

    正是调试出来，数据库里的字段看起来是空的，但是语句显示没有空，才改用长度来判断，最终解决了判断不出来的问题

结束

最后附上调试出来的SQL 语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
-- Part 1: Create the temporary table with enhanced debug info
CREATE TEMPORARY TABLE temp_product_data AS
WITH RECURSIVE CategoryTree AS (
    SELECT scode FROM ay_content_sort WHERE scode = 12
    UNION ALL
    SELECT c.scode
    FROM ay_content_sort c
    JOIN CategoryTree ct ON c.pcode = ct.scode
)
SELECT 
    t.title,
    m.filename AS sort_filename,
    t.filename AS content_filename,
    m.scode,
    t.id,
    'sort_filename: [' || IFNULL(m.filename, '') || ']' AS debug_sort_filename,
    'content_filename: [' || IFNULL(t.filename, '') || ']' AS debug_content_filename,
    'scode: [' || IFNULL(m.scode, '') || ']' AS debug_scode,
    'id: [' || IFNULL(t.id, '') || ']' AS debug_id,
    length(IFNULL(m.filename, '')) AS len_sort_filename,
    length(IFNULL(t.filename, '')) AS len_content_filename,
    length(IFNULL(m.scode, '')) AS len_scode,
    length(IFNULL(t.id, '')) AS len_id
FROM ay_content t
LEFT JOIN ay_content_sort m ON t.scode = m.scode
WHERE t.acode = 'en' AND t.scode IN (SELECT scode FROM CategoryTree);

-- Part 2: Query the temporary table, generate URLs, and show enhanced debug info
SELECT 
    title,
    debug_sort_filename,
    debug_content_filename,
    debug_scode,
    debug_id,
    len_sort_filename,
    len_content_filename,
    len_scode,
    len_id,
    'https://www.example.com/' || 
    CASE 
        WHEN length(IFNULL(sort_filename, '')) > 0 THEN sort_filename
        ELSE 'products_' || IFNULL(scode, '')
    END || '/' || 
    CASE 
        WHEN length(IFNULL(content_filename, '')) > 0 THEN content_filename
        ELSE IFNULL(id, '')
    END || '.html' AS url
FROM temp_product_data;

-- Optional: Drop the temporary table if you don't need it anymore
-- DROP TABLE temp_product_data;

上面的语句也可以不用临时表格来处理，精简成下面的语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
WITH RECURSIVE CategoryTree AS (
    SELECT scode FROM ay_content_sort WHERE scode = 12
    UNION ALL
    SELECT c.scode
    FROM ay_content_sort c
    JOIN CategoryTree ct ON c.pcode = ct.scode
)
SELECT 
    t.title,
    m.filename,
    t.filename,
    m.scode,
    t.id,('https://www.example.com/' || 
    CASE 
        WHEN length(IFNULL(m.filename, '')) > 0 THEN m.filename
        ELSE 'products_' || IFNULL(m.scode, '')
    END || '/' || 
    CASE 
        WHEN length(IFNULL(t.filename, '')) > 0 THEN t.filename
        ELSE IFNULL(t.id, '')
    END || '.html') as url
FROM ay_content t
LEFT JOIN ay_content_sort m ON t.scode = m.scode
WHERE t.acode = 'en' and t.status='1'  AND t.scode IN (SELECT scode FROM CategoryTree);