几年没写 sql 之后脑子好像不够用了，有大佬看下怎么做不。。。

[VoidChen]

要求：

检查冲突，course1 和 course2 在 18 点冲突，course1 和 course3 在 18 点冲突，course2 和 course3 在 18 点冲突，则合并 course1 、course2 、course3 作为一条冲突记录，然后 1 和 2，1 和 3，2 和 3 在其它时间的冲突子集就不要了。然后 course3 和 course4 在 19 点冲突，course4 和 1 、2 没有冲突，那么 course3 、course4 作为一条冲突记录

数据：

结果：

我写的 sql：

select distinct a.ct,a.times,a.classroom,a.num
from (
      select IF(course1 < course2, concat(course1, course2), concat(course2, course1)) as co,b.*
      from chongtu_test a
      left join (
          select ct, group_concat(time) as times, classroom, a.num
          from (
               select time,classroom,group_concat(distinct course1) as ct,count(distinct course1) as num
               from chongtu_test c
               group by time, classroom
          ) a
          group by ct, classroom, num
      ) b on b.ct like concat('%', a.course1, '%') and
             b.ct like concat('%', a.course2, '%') and
             b.times like concat('%', a.time, '%')
)a,(
select IF(course1 < course2,concat(course1,course2),concat(course2,course1)) as co,max(num) as num
from chongtu_test a
left join (
    select ct, group_concat(time) as times, classroom, a.num
    from (
         select time, classroom, group_concat(distinct course1) as ct, count(distinct course1) as num
         from chongtu_test c
         group by time, classroom
     ) a
    group by ct, classroom, num
)b on b.ct like concat('%',a.course1,'%') and b.ct like concat('%',a.course2,'%') and b.times like concat('%',a.time,'%')
group by co)b
where a.co = b.co
and a.num = b.num

[VoidChen]

@justgodlike1993 group by 有的才能 select

[justgodlike1993]

版本的问题吧，我这能跑 - -

[VoidChen]

@justgodlike1993 mysql 吗？什么版本啊，话说这种是默认取第一条？如果能搞到这个可以好很多，相当于排序之后取第一条就出来了。我这 sql 最大的问题就在这

[justgodlike1993]

Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 8 Server version: 10.3.22-MariaDB-1ubuntu1 Ubuntu 20.04 Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Reading history-file /root/.mysql_history Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

[justgodlike1993]

SELECT date,classroom,MAX(ct) ct,GROUP_CONCAT(time) times,MAX(num) num FROM (SELECT date,classroom,time,MIN(c1) c1,MIN(c2) c2,CONCAT(MIN(t.c1),',',GROUP_CONCAT(DISTINCT t.c2 ORDER BY t.c2)) ct,1+COUNT(DISTINCT t.c2) num FROM (SELECT date,classroom,time,if(course1<course2,course1,course2) c1,IF(course1<course2,course2,course1) c2 FROM chongtu_test) t GROUP BY date,classroom,time) f GROUP BY date,classroom,CONCAT(c1,',',c2);

[VoidChen]

@justgodlike1993 这次很近了，就差一个没去掉！
我这多加了 2 条数据，一天内出现过的冲突都归入最多冲突那条就可以了

[VoidChen]

@justgodlike1993 最新的数据，我加了个 target，可以不用那个 if concat 了，感谢一直尝试。。。
INSERT INTO lin.chongtu_test (course1, course2, time, classroom, date, target) VALUES ('class2', 'class1', 15, '1', '20200831', 'class1class2');
INSERT INTO lin.chongtu_test (course1, course2, time, classroom, date, target) VALUES ('class2', 'class1', 16, '1', '20200831', 'class1class2');
INSERT INTO lin.chongtu_test (course1, course2, time, classroom, date, target) VALUES ('class2', 'class1', 17, '1', '20200831', 'class1class2');
INSERT INTO lin.chongtu_test (course1, course2, time, classroom, date, target) VALUES ('class2', 'class1', 18, '1', '20200831', 'class1class2');
INSERT INTO lin.chongtu_test (course1, course2, time, classroom, date, target) VALUES ('class3', 'class1', 17, '1', '20200831', 'class1class3');
INSERT INTO lin.chongtu_test (course1, course2, time, classroom, date, target) VALUES ('class3', 'class2', 17, '1', '20200831', 'class2class3');
INSERT INTO lin.chongtu_test (course1, course2, time, classroom, date, target) VALUES ('class3', 'class1', 18, '1', '20200831', 'class1class3');
INSERT INTO lin.chongtu_test (course1, course2, time, classroom, date, target) VALUES ('class3', 'class2', 18, '1', '20200831', 'class2class3');
INSERT INTO lin.chongtu_test (course1, course2, time, classroom, date, target) VALUES ('class4', 'class3', 22, '1', '20200831', 'class3class4');
INSERT INTO lin.chongtu_test (course1, course2, time, classroom, date, target) VALUES ('class1', 'class2', 15, '1', '20200831', 'class1class2');
INSERT INTO lin.chongtu_test (course1, course2, time, classroom, date, target) VALUES ('class1', 'class2', 16, '1', '20200831', 'class1class2');
INSERT INTO lin.chongtu_test (course1, course2, time, classroom, date, target) VALUES ('class1', 'class2', 17, '1', '20200831', 'class1class2');
INSERT INTO lin.chongtu_test (course1, course2, time, classroom, date, target) VALUES ('class1', 'class2', 18, '1', '20200831', 'class1class2');
INSERT INTO lin.chongtu_test (course1, course2, time, classroom, date, target) VALUES ('class1', 'class3', 17, '1', '20200831', 'class1class3');
INSERT INTO lin.chongtu_test (course1, course2, time, classroom, date, target) VALUES ('class2', 'class3', 17, '1', '20200831', 'class2class3');
INSERT INTO lin.chongtu_test (course1, course2, time, classroom, date, target) VALUES ('class1', 'class3', 18, '1', '20200831', 'class1class3');
INSERT INTO lin.chongtu_test (course1, course2, time, classroom, date, target) VALUES ('class2', 'class3', 18, '1', '20200831', 'class2class3');
INSERT INTO lin.chongtu_test (course1, course2, time, classroom, date, target) VALUES ('class3', 'class4', 22, '1', '20200831', 'class3class4');
INSERT INTO lin.chongtu_test (course1, course2, time, classroom, date, target) VALUES ('class1', 'class2', 23, '1', '20200901', 'class1class2');
INSERT INTO lin.chongtu_test (course1, course2, time, classroom, date, target) VALUES ('class2', 'class1', 23, '1', '20200901', 'class1class2');
INSERT INTO lin.chongtu_test (course1, course2, time, classroom, date, target) VALUES ('class2', 'class3', 19, '1', '20200831', 'class2class3');
INSERT INTO lin.chongtu_test (course1, course2, time, classroom, date, target) VALUES ('class3', 'class2', 19, '1', '20200831', 'class2class3');
INSERT INTO lin.chongtu_test (course1, course2, time, classroom, date, target) VALUES ('class2', 'class3', 19, '2', '20200831', 'class2class3');
INSERT INTO lin.chongtu_test (course1, course2, time, classroom, date, target) VALUES ('class3', 'class2', 19, '2', '20200831', 'class2class3');