sqlite 如何在主查询中为子查询获取current_row？

bmvo0sr5 于 2023-02-05 发布在 SQLite

关注(0)|答案(2)|浏览(149)

假设下表：

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 的值之和。
我的第一种方法如下所示，其中current_row_id作为我的pseudeo_code_variable来证明a想要考虑当前行：

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/%';

sqlite

来源：https://stackoverflow.com/questions/75335729/how-to-get-the-current-row-in-a-main-query-for-a-subquery

2条答案

按热度按时间

jfgube3f1#

使用自联接和聚合：

SELECT t1.col1,
       TOTAL(t2.value) AS total
FROM tablename t1 LEFT JOIN tablename t2
ON SUBSTR(t2.col1, INSTR(t2.col1, '/') + 1) = SUBSTR(t1.col1, INSTR(t1.col1, '/') + 1)
AND t2.rowid <> t1.rowid  
WHERE t1.col1 LIKE 'IE/%'
GROUP BY t1.col1;

或者，对于条件聚合：

SELECT MAX(CASE WHEN col1 LIKE 'IE/%' THEN col1 END) AS col1,
       TOTAL(CASE WHEN col1 NOT LIKE 'IE/%' THEN value END) AS total
FROM tablename
GROUP BY SUBSTR(col1, INSTR(col1, '/') + 1);

或者，使用窗口函数：

SELECT DISTINCT
       MAX(CASE WHEN col1 LIKE 'IE/%' THEN col1 END) OVER (PARTITION BY SUBSTR(col1, INSTR(col1, '/') + 1)) AS col1,
       TOTAL(CASE WHEN col1 NOT LIKE 'IE/%' THEN value END) OVER (PARTITION BY SUBSTR(col1, INSTR(col1, '/') + 1)) AS total
FROM tablename;

请参见demo。

赞(0）回复(0）举报 2023-02-05

llew8vvj2#

这应该会给你预期的结果。记住，当处理['IE/g']这样的额外类别时，你需要在where子句的条件语句中添加最后一个字母。我使用val而不是value作为列名，因为在许多DBMS中，它是一个受限制的关键字。
代码：

select 'IE/' || substr(col1,4,1) col, sum(val) my_sum from t1 where col1 LIKE '%/%' and col1 not like 'IE/%' and substr(col1,4,1) in ('a', 'b', 'c') group by substr(col1,4,1);

输出：

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

赞(0）回复(0）举报 2023-02-05

我来回答

sqlite 如何在主查询中为子查询获取current_row？

2条答案

相关问题

热门标签

最新问答