gpt4 book ai didi

sql - 从条件组返回多个行,不带联合

转载 作者:行者123 更新时间:2023-12-03 14:58:27 25 4
gpt4 key购买 nike

我正在尝试在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

关于sql - 从条件组返回多个行,不带联合,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/50770677/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com