类列中每个Complete之间的SUM From_value和To_value

Question

我需要对每次出现"；完整"；在CCD_ 1列中。

位置从值到值7><1>><1>>>5L193>><1>><1>><1>><1>><1>><1>><1>><1>

ID	类	用户ID
2	完成	3075	1-C
3	ADJUST	3075	1-C	5
	添加	L146	2-D	0	0
5	完成	L151	5-B
	匹配
7	完成	L102	3-C
	匹配
9	匹配	L102	3-C	2
12	匹配	L102	3-C	1	2
13	匹配	L102	3-C	1
14	匹配	L102	3-C	3
15	匹配	L102	3-C	1
16	完成	L151	5-C
17	匹配	L151	5-C	6
18	完成	4-B
19	调整
20	调整
21	匹配	L193	4-B	1
22	匹配	L193	4-B	1
23	匹配	L193	4-B	1
24	匹配	L193	4-B	1
25	匹配	L193	4-B	1
26	匹配	L193	4-B	1
27	匹配	L193	4-B	1
28	匹配	L193	4-B	1

正如Dale告诉您的那样，等待您详细的预期结果，您可以使用以下查询(记住：在sql中，我建议尽可能简单(，并尝试给出如下示例数据：

CREATE TABLE mytable(
ID         INTEGER  NOT NULL PRIMARY KEY 
,Class      VARCHAR(8) NOT NULL
,User_ID    VARCHAR(4) NOT NULL
,Location   VARCHAR(3) NOT NULL
,From_value INTEGER 
,To_value   INTEGER 
);
INSERT INTO mytable(ID,Class,User_ID,Location,From_value,To_value) VALUES (2,'COMPLETE','3075','1-C',NULL,NULL);
INSERT INTO mytable(ID,Class,User_ID,Location,From_value,To_value) VALUES (3,'ADJUST','3075','1-C',5,7);
INSERT INTO mytable(ID,Class,User_ID,Location,From_value,To_value) VALUES (4,'ADD','L146','2-D',0,0);
INSERT INTO mytable(ID,Class,User_ID,Location,From_value,To_value) VALUES (5,'COMPLETE','L151','5-B',NULL,NULL);
INSERT INTO mytable(ID,Class,User_ID,Location,From_value,To_value) VALUES (6,'MATCH','L151','5-B',8,6);
INSERT INTO mytable(ID,Class,User_ID,Location,From_value,To_value) VALUES (7,'COMPLETE','L102','3-C',NULL,NULL);
INSERT INTO mytable(ID,Class,User_ID,Location,From_value,To_value) VALUES (8,'MATCH','L102','3-C',1,1);
INSERT INTO mytable(ID,Class,User_ID,Location,From_value,To_value) VALUES (9,'MATCH','L102','3-C',2,1);
INSERT INTO mytable(ID,Class,User_ID,Location,From_value,To_value) VALUES (12,'MATCH','L102','3-C',1,2);
INSERT INTO mytable(ID,Class,User_ID,Location,From_value,To_value) VALUES (13,'MATCH','L102','3-C',1,1);
INSERT INTO mytable(ID,Class,User_ID,Location,From_value,To_value) VALUES (14,'MATCH','L102','3-C',3,1);
INSERT INTO mytable(ID,Class,User_ID,Location,From_value,To_value) VALUES (15,'MATCH','L102','3-C',1,1);
INSERT INTO mytable(ID,Class,User_ID,Location,From_value,To_value) VALUES (16,'COMPLETE','L151','5-C',NULL,NULL);
INSERT INTO mytable(ID,Class,User_ID,Location,From_value,To_value) VALUES (17,'MATCH','L151','5-C',6,5);
INSERT INTO mytable(ID,Class,User_ID,Location,From_value,To_value) VALUES (18,'COMPLETE','L193','4-B',NULL,NULL);
INSERT INTO mytable(ID,Class,User_ID,Location,From_value,To_value) VALUES (19,'ADJUST','L193','4-B',0,1);
INSERT INTO mytable(ID,Class,User_ID,Location,From_value,To_value) VALUES (20,'ADJUST','L193','4-B',0,1);
INSERT INTO mytable(ID,Class,User_ID,Location,From_value,To_value) VALUES (21,'MATCH','L193','4-B',1,1);
INSERT INTO mytable(ID,Class,User_ID,Location,From_value,To_value) VALUES (22,'MATCH','L193','4-B',1,1);
INSERT INTO mytable(ID,Class,User_ID,Location,From_value,To_value) VALUES (23,'MATCH','L193','4-B',1,1);
INSERT INTO mytable(ID,Class,User_ID,Location,From_value,To_value) VALUES (24,'MATCH','L193','4-B',1,1);
INSERT INTO mytable(ID,Class,User_ID,Location,From_value,To_value) VALUES (25,'MATCH','L193','4-B',1,1);
INSERT INTO mytable(ID,Class,User_ID,Location,From_value,To_value) VALUES (26,'MATCH','L193','4-B',1,1);
INSERT INTO mytable(ID,Class,User_ID,Location,From_value,To_value) VALUES (27,'MATCH','L193','4-B',1,1);
INSERT INTO mytable(ID,Class,User_ID,Location,From_value,To_value) VALUES (28,'MATCH','L193','4-B',1,1);

FROM的第一部分提取Id、User_Id和Location；完整"；。在LEFT JOIN中，使用User_id和Location提取要求和的值。

SELECT A.id
, A.User_ID
, A.Location
, SUM(FROM_Value) AS From_Value
, SUM(TO_Value) AS TO_Value
FROM (SELECT id, User_ID, Location FROM mytable WHERE CLASS='COMPLETE')  A
LEFT JOIN mytable B ON A.User_ID= B.User_ID AND A.Location=B.Location AND B.CLASS<>'COMPLETE'
GROUP BY A.id, A.User_ID, B.Location 
ORDER BY A.Id, A.User_ID

输出：

+----+---------+----------+------------+----------+
| id | User_ID | Location | From_Value | TO_Value |
+----+---------+----------+------------+----------+
|  2 | 3075    | 1-C      |          5 |        7 |
|  5 | L151    | 5-B      |          8 |        6 |
|  7 | L102    | 3-C      |          9 |        7 |
| 16 | L151    | 5-C      |          6 |        5 |
| 18 | L193    | 4-B      |          8 |       10 |
+----+---------+----------+------------+----------+

-----添加----

如果由于某些你没有解释的原因，你想要"；以限制"；在两个"；完整的"；事件，我认为以下查询可以帮助您作为起点：

SELECT A.ID, A.USER_ID, A.LOCATION
, SUM(B.FROM_VALUE ) AS F_VALUE
, SUM(B.TO_VALUE ) AS T_VALUE
FROM  (SELECT USER_ID
, LOCATION  
, ID
, LEAD (ID, 1) OVER (ORDER BY ID ) AS ID_NEXT
FROM   MYTABLE 
WHERE  CLASS = 'COMPLETE' ) A
LEFT JOIN MYTABLE B  ON A.USER_ID = B.USER_ID 
AND A.LOCATION = B.LOCATION
AND  B.ID>A.ID 
AND B.ID <COALESCE(A.ID_NEXT,9999999)
GROUP BY  A.ID, A.USER_ID, A.LOCATION 

(对于您发布的数据，此查询给出的结果与我的第一个查询相同(。