add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table groups ( id int, grpid int , changeDate date, userid int, pct double , hobby int );
SELECT * from groups;
insert into groups(id , grpid , changeDate ,userid ,pct ,hobby ) values
(1 , 1 ,'2020-01-01', 1 , 1 , 1 ),
(2 , 1 ,'2020-01-02', 1 , 1 , 2 ),
(3 , 1 ,'2020-01-03', 1 , 1 , 1 ),
(4 , 2 ,'2020-01-01', 1 , 0.5 , 1 ),
(5 , 2 ,'2020-01-01', 2 , 0.5 , 2 ),
(6 , 2 ,'2020-01-02', 1 , 0.5 , 1 ),
(7 , 2 ,'2020-01-02', 2 , 0.5 , 3 ),
(8 , 3 ,'2020-01-01', 1 , 0.5 , 1 ),
(9 , 3 ,'2020-01-01', 2 , 0.5 , 2 ),
(10 , 3 ,'2020-01-02', 1 , 0.4 , 1 ),
(11 , 3 ,'2020-01-02', 2 , 0.6 , 2 ),
(12 , 4 ,'2020-01-01', 1 , 0.6 , 1 ),
(13 , 4 ,'2020-01-01', 2 , 0.4 , 2 ),
(14 , 4 ,'2020-01-02', 1 , 0.6 , 1 ),
(15 , 4 ,'2020-01-02', 2 , 0.4 , 2 ),
(16 , 5 ,'2020-01-01', 1 , 0.2 , 2 ),
(17 , 5 ,'2020-01-01', 2 , 0.5 , 1 ),
(18 , 5 ,'2020-01-01', 3 , 0.3 , 2 ),
(19 , 6 ,'2020-01-01', 1 , 0.3 , 2 ),
(20 , 6 ,'2020-01-01', 2 , 0.5 , 1 ),
(21 , 6 ,'2020-01-01', 3 , 0.2 , 2 ),
(22 , 6 ,'2020-02-01', 1 , 0.2 , 2 ),
(23 , 6 ,'2020-02-01', 2 , 0.5 , 1 ),
(24 , 6 ,'2020-02-01', 3 , 0.3 , 2 ),
(25 , 6 ,'2020-03-01', 1 , 0.3 , 2 ),
(26 , 6 ,'2020-03-01', 2 , 0.3 , 1 ),
(27 , 6 ,'2020-03-01', 3 , 0.4 , 2 ),
(28 , 7 ,'2020-01-01', 1 , 0.3 , 2 ),
(29 , 7 ,'2020-01-01', 2 , 0.5 , 1 ),
(30 , 7 ,'2020-01-01', 3 , 0.2 , 2 ),
(31 , 7 ,'2020-02-01', 1 , 0.3 , 2 ),
(32 , 7 ,'2020-02-01', 2 , 0.5 , 1 ),
(33 , 7 ,'2020-02-01', 3 , 0.2 , 2 ),
(34 , 7 ,'2020-03-01', 1 , 0.3 , 2 ),
(35 , 7 ,'2020-03-01', 2 , 0.3 , 1 ),
SELECT * from groups;
id grpid changeDate userid pct hobby
1 1 2020-01-01 1 1 1
2 1 2020-01-02 1 1 2
3 1 2020-01-03 1 1 1
4 2 2020-01-01 1 0.5 1
5 2 2020-01-01 2 0.5 2
6 2 2020-01-02 1 0.5 1
7 2 2020-01-02 2 0.5 3
8 3 2020-01-01 1 0.5 1
9 3 2020-01-01 2 0.5 2
10 3 2020-01-02 1 0.4 1
11 3 2020-01-02 2 0.6 2
12 4 2020-01-01 1 0.6 1
13 4 2020-01-01 2 0.4 2
14 4 2020-01-02 1 0.6 1
15 4 2020-01-02 2 0.4 2
16 5 2020-01-01 1 0.2 2
17 5 2020-01-01 2 0.5 1
18 5 2020-01-01 3 0.3 2
19 6 2020-01-01 1 0.3 2
20 6 2020-01-01 2 0.5 1
21 6 2020-01-01 3 0.2 2
22 6 2020-02-01 1 0.2 2
23 6 2020-02-01 2 0.5 1
24 6 2020-02-01 3 0.3 2
25 6 2020-03-01 1 0.3 2
26 6 2020-03-01 2 0.3 1
27 6 2020-03-01 3 0.4 2
28 7 2020-01-01 1 0.3 2
29 7 2020-01-01 2 0.5 1
30 7 2020-01-01 3 0.2 2
31 7 2020-02-01 1 0.3 2
32 7 2020-02-01 2 0.5 1
33 7 2020-02-01 3 0.2 2
34 7 2020-03-01 1 0.3 2
35 7 2020-03-01 2 0.3 1
36 7 2020-03-01 3 0.4 2
37 8 2020-02-01 1 0.3 1
38 8 2020-03-01 1 0.3 1
39 8 2020-03-01 3 0.4 2
41 9 2020-02-01 1 0.3 1
42 9 2020-03-01 1 0.3 1
43 9 2020-03-01 3 0.4 2
44 9 2020-04-01 1 0.3 1
45 9 2020-04-01 3 0.4 2
46 9 2020-04-01 4 0.3 3
47 10 2020-04-01 1 1 1
SELECT DISTINCT g1.grpid
FROM
( SELECT grpid, changeDate
FROM groups AS g
GROUP BY grpid, changeDate
) AS g1
JOIN
( SELECT grpid, changeDate
FROM groups AS g
GROUP BY grpid, changeDate
) AS g2
ON g1.grpid = g2.grpid
AND g1.changeDate < g2.changeDate
WHERE NOT EXISTS
( SELECT 1
FROM groups AS gi
WHERE gi.grpid = g1.grpid
AND gi.changeDate = g1.changeDate
AND NOT EXISTS
( SELECT 1
FROM groups AS gk
WHERE gk.grpid = g2.grpid
AND gk.changeDate = g2.changeDate
AND gk.userid = gi.userid
AND gk.pct = gi.pct
AND gk.hobby = gi.hobby
)
)
AND NOT EXISTS
( SELECT 1
FROM groups AS gi
WHERE gi.grpid = g2.grpid
AND gi.changeDate = g2.changeDate
AND NOT EXISTS
( SELECT 1
FROM groups AS gk
grpid
1
4
7
SELECT DISTINCT grpid
FROM
( SELECT grpid, changeDate,
GROUP_CONCAT( CONCAT_WS('-', userid, hobby, pct)
ORDER BY userid, hobby, pct
SEPARATOR ' '
) AS groupdata
FROM groups AS gr
GROUP BY grpid, changeDate
) AS g
GROUP BY grpid, groupdata
HAVING COUNT(*) > 1 ;
grpid
1
4
7