我正在尝试在 SQLite DB 中构建一个支持条件组的查询。
这是我到目前为止尝试过的:
SELECT
case
when A>1 AND B>1 THEN 1
when X>1 AND Y>1 THEN 2
when C>1 AND D>1 THEN 3
END AS data_grp,
SUM(col1) AS col1,
SUM(col2) AS col2
FROM tbl
GROUP BY data_grp;
如果一次只有一个案例是真的,这工作得很好。如果连续多个情况为真,则返回第一个情况而不是所有满足条件的组。
我已经尝试过这个union
,效果很好,但速度很慢。有没有其他方法可以使用此条件组快速获取结果。
示例数据和预期结果:
DROP TABLE IF EXISTS tbl;
CREATE TABLE tbl
(
A INT,
B INT,
C INT,
D INT,
X INT,
Y INT,
col1 int,
col2 int
);
INSERT INTO tbl(A,B,C,D,X,Y,col1,col2) values (2,3,0,0,0,0,5,10);
INSERT INTO tbl(A,B,C,D,X,Y,col1,col2) values (0,0,0,0,8,10,3,2);
INSERT INTO tbl(A,B,C,D,X,Y,col1,col2) values (5,4,4,9,0,0,3,2);
SELECT
case
when A>1 AND B>1 THEN 1
when X>1 AND Y>1 THEN 2
when C>1 AND D>1 THEN 3
END AS data_grp,
SUM(col1) AS col1,
SUM(col2) AS col2
FROM tbl
GROUP BY data_grp;
查询输出:
"1" "8" "12"
"2" "3" "2"
预期输出:
"1" "8" "12"
"2" "3" "2"
"3" "3" "2"
GROUP BY
由于组重叠,您不能直接使用。您可以使用以下内容,尽管这也可能很慢。
WITH RECURSIVE
cnt(x) AS (
SELECT 1
UNION ALL
SELECT x+1 FROM cnt
LIMIT 3
)
SELECT x as data_grp, sum(col1), sum(col2)
FROM cnt,
(SELECT
case when A>1 AND B>1 THEN 1 ELSE 0 END as dg1,
case when X>1 AND Y>1 THEN 2 ELSE 0 END as dg2,
case when C>1 AND D>1 THEN 3 ELSE 0 END as dg3,
col1, col2
FROM tbl) t WHERE x=dg1 or x=dg2 or x=dg3
GROUP BY x
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句