열 값이 동일할 때의 합계 값

열 값이 동일할 때의 합계 값

연대표를 작성하려고 합니다.테이블에는 다음과 같은 date_start 및 date_end 유형의 열이 있습니다.

+------+---------------------+---------------------+----------+
| Type |        Start        |         End         |   Diff   |
+------+---------------------+---------------------+----------+
|    1 | 2020-11-23 23:40:00 | 2020-11-23 23:41:00 | 00:01:00 |
|    1 | 2020-11-23 23:42:00 | 2020-11-23 23:43:00 | 00:01:00 |
|    1 | 2020-11-23 23:44:00 | 2020-11-23 23:45:00 | 00:01:00 |
|    2 | 2020-11-23 23:46:00 | 2020-11-23 23:47:00 | 00:01:00 |
|    2 | 2020-11-23 23:48:00 | 2020-11-23 23:49:00 | 00:01:00 |
|    1 | 2020-11-23 23:50:00 | 2020-11-23 23:51:00 | 00:01:00 |
|    1 | 2020-11-23 23:52:00 | 2020-11-23 23:53:00 | 00:01:00 |
+------+---------------------+---------------------+----------+

열 값은 이전과 동일하게 유지하면서 차이를 합산해야 합니다.유형 열 값이 변경되면 새 행이 생성되고 다음과 같은 결과가 나타납니다.

+------+----------+
| Type |   Diff   |
+------+----------+
|    1 | 00:03:00 |
|    2 | 00:02:00 |
|    1 | 00:02:00 |
+------+----------+

MySQL에서 이러한 그룹화와 합계 결과를 얻으려면 어떻게 해야 합니까?PS: 시간 로직은 신경 쓰지 마세요.정수를 사용하여 예를 셋업하고 싶다면 문제 없습니다.

변수를 사용하여 블록 번호를 할당한 다음 집계

drop table if exists t;
create table t
( Type int,        Startdt datetime,  Enddt datetime,   Diff time);
insert into t values
(    1 ,'2020-11-23 23:40:00' ,'2020-11-23 23:41:00' , '00:01:00' ),
(    1 ,'2020-11-23 23:42:00' ,'2020-11-23 23:43:00' , '00:01:00' ),
(    1 ,'2020-11-23 23:44:00' ,'2020-11-23 23:45:00' , '00:01:00' ),
(    2 ,'2020-11-23 23:46:00' ,'2020-11-23 23:47:00' , '00:01:00' ),
(    2 ,'2020-11-23 23:48:00' ,'2020-11-23 23:49:00' , '00:01:00' ),
(    1 ,'2020-11-23 23:50:00' ,'2020-11-23 23:51:00' , '00:01:00' ),
(    1 ,'2020-11-23 23:52:00' ,'2020-11-23 23:53:00' , '00:01:00' );

    select type,block,sec_to_time(sum(time_to_sec(diff)))
from
(
select t.*,
         if(type <> @p, @b:=@b+1,@b:=@b) block,
         @p:=type p
from t
cross join (select @b:=0,@p:=0) b
order by startdt,type
) s
group by s.block,s.type;

------+-------+-------------------------------------+
| type | block | sec_to_time(sum(time_to_sec(diff))) |
+------+-------+-------------------------------------+
|    1 | 1     | 00:03:00                            |
|    2 | 2     | 00:02:00                            |
|    1 | 3     | 00:02:00                            |
+------+-------+-------------------------------------+
3 rows in set (0.148 sec)

SELECT t.type,sum(c.dif)
FROM TABLE t JOIN
(SELECT c.type TIMEDIFF(c.start,c.end) 
as dif FROM TABLE c)ta ON ta.type = t.type
group by t.type

언급URL : https://stackoverflow.com/questions/64988150/sum-values-while-the-column-value-is-the-same