我试图找到最好的方法来获得累积显示的总体概述的概述,即通过扣除不满足每个步骤中的累积要求的观察结果。
这是表脚本和示例数据:
CREATE TABLE #Table_A(
id INTEGER NOT NULL PRIMARY KEY
,totalAmount INTEGER NOT NULL
,requirement1 VARCHAR(6) NOT NULL
,requirement2 INTEGER NOT NULL
,requirement3 BIT NOT NULL
,requirement4 VARCHAR(10) NOT NULL
);
INSERT INTO #Table_A(id,totalAmount,requirement1,requirement2,requirement3,requirement4) VALUES (1,6580,'GROUP1',100,0,'TEST');
INSERT INTO #Table_A(id,totalAmount,requirement1,requirement2,requirement3,requirement4) VALUES (2,3667,'GROUP1',100,1,'PRODUKTION');
INSERT INTO #Table_A(id,totalAmount,requirement1,requirement2,requirement3,requirement4) VALUES (3,2907,'GROUP1',100,1,'TEST');
INSERT INTO #Table_A(id,totalAmount,requirement1,requirement2,requirement3,requirement4) VALUES (4,5271,'GROUP2',100,1,'TEST');
INSERT INTO #Table_A(id,totalAmount,requirement1,requirement2,requirement3,requirement4) VALUES (5,91630,'GROUP1',200,0,'PRODUKTION');
INSERT INTO #Table_A(id,totalAmount,requirement1,requirement2,requirement3,requirement4) VALUES (6,9925,'GROUP1',100,1,'TEST');
INSERT INTO #Table_A(id,totalAmount,requirement1,requirement2,requirement3,requirement4) VALUES (7,4730,'GROUP1',100,1,'TEST');
INSERT INTO #Table_A(id,totalAmount,requirement1,requirement2,requirement3,requirement4) VALUES (8,5171,'GROUP2',100,1,'TEST');
INSERT INTO #Table_A(id,totalAmount,requirement1,requirement2,requirement3,requirement4) VALUES (9,1250,'GROUP1',100,1,'TEST');
INSERT INTO #Table_A(id,totalAmount,requirement1,requirement2,requirement3,requirement4) VALUES (10,11223,'GROUP1',100,1,'TEST');
这是我想要实现的概述:
+------+-------------+-------+-----------+
| step | totalAmount | total | comment |
+------+-------------+-------+-----------+
| 1 | 40282 | 7 | comment 1 |
| 2 | 30035 | 5 | comment 2 |
| ... | ... | ... | ... |
| n | X | Y | comment n |
+------+-------------+-------+-----------+
最后,这是我到目前为止所做的 SQL 代码:
-- drop tables if they exists
drop table if exists #table_step1
drop table if exists #table_step2
-- select data from the different steps
-- select data step 1
select *
into #table_step1
from #Table_A
where
requirement1 = 'GROUP1'
and requirement2 = 100
-- select data step 2
select *
into #table_step2
from #table_step1
where
requirement3 = 1
and requirement4 = 'TEST'
...
-- aggregate the data for each step and use UNION ALL to get overall overview
select 1 as step, sum(totalAmount) as totalAmount, count(*) as total, 'comment 1' as comment
from #table_step1
UNION ALL
select 2 as step, sum(totalAmount) as totalAmount, count(*) as total, 'comment 2' as comment
from #table_step2
UNION ALL
...