如何在主查询的子查询中引用当前行的字段？

数据表定义与内容 #

创建表及插入数据的SQL语句：

CREATE TABLE t1 (col1 TEXT PRIMARY KEY, value INTEGER);
INSERT INTO t1 VALUES 
    ('IE/a', 1), ('IE/b', 2), ('IE/c', 3) ,
    ('00/a', 10), ('01/a', 20), ('02/a', 30), ('03/a', 40),
    ('00/b', 100), ('01/b', 200), ('02/b', 300), ('03/b', 400),
    ('00/c', 1000), ('01/c', 2000), ('02/c', 3000), ('03/c', 4000);

表内容如下：

col1    value
IE/a    1
IE/b    2
IE/c    3
00/a    10
01/a    20
02/a    30
03/a    40
00/b    100
01/b    200
02/b    300
03/b    400
00/c    1000
01/c    2000
02/c    3000
03/c    4000

期望输出 #

需要得到的查询结果：

IE/a  100          
IE/b  1000          
IE/c  10000

其中IE/a的值为00/a、01/a、02/a、03/a的value之和，以此类推。

初始尝试代码 #

用户的初始写法中，current_row_col1为伪代码，用于标识需要引用主查询当前行的col1字段：

SELECT
   col1
 , (SELECT sum(value) FROM t1 
     WHERE col1 = '00' || SUBSTRING( current_row_col1, 3)
        OR col1 = '01' || SUBSTRING( current_row_col1, 3)
        OR col1 = '02' || SUBSTRING( current_row_col1, 3)
        OR col1 = '03' || SUBSTRING( current_row_col1, 3)
    ) AS value
FROM t1
WHERE col1 LIKE 'IE/%';

修正后的查询语句 #

方法1：使用表别名明确引用 #

给主查询的表指定别名，子查询通过别名引用主查询当前行的字段，同时给子查询的表也指定别名避免字段混淆：

SELECT
    t_main.col1,
    (SELECT SUM(value) 
     FROM t1 t_sub
     WHERE t_sub.col1 = '00' || SUBSTRING(t_main.col1, 3)
        OR t_sub.col1 = '01' || SUBSTRING(t_main.col1, 3)
        OR t_sub.col1 = '02' || SUBSTRING(t_main.col1, 3)
        OR t_sub.col1 = '03' || SUBSTRING(t_main.col1, 3)
    ) AS value
FROM t1 t_main
WHERE t_main.col1 LIKE 'IE/%';

方法2：简化条件的优化写法 #

观察到目标行的col1都是两位前缀加/x的格式，可以用LIKE替代多个OR条件，让语句更简洁：

SELECT
    t_main.col1,
    (SELECT SUM(value) 
     FROM t1 t_sub
     WHERE t_sub.col1 LIKE '__' || SUBSTRING(t_main.col1, 3)
    ) AS value
FROM t1 t_main
WHERE t_main.col1 LIKE 'IE/%';

说明 #

• 给主查询的t1表设置别名t_main，子查询中通过t_main.col1就能准确引用主查询当前行的col1字段。
• 给子查询的t1表设置别名t_sub，避免和主查询的表名冲突，让数据库清晰区分两个表的字段，不会出现字段引用歧义。

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