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

检查冲突，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

27 replies • 2020-09-03 11:10:59 +08:00

VoidChen

Sep 1, 2020

这里的 markdown 怎么不支持表格。。。我找个图床先 QAQ

lewis89

Sep 1, 2020

@VoidChen #1
其实.. 现在程序员都不怎么写复杂的 SQL

因为 SQL 本身难以维护，而在代码里面实现业务逻辑远比数据库要强

除了复杂的报表需求类似 OLTP 的系统可能还你这种 SQL 大部分场景下已经不需要复杂的 SQL 了

VoidChen

Sep 1, 2020

@lewis89 告诉你一个很恐怖的事。。。我过往 3 年经验都是做大数据开发的，实打实的写代码的那种，最近新入职公司，全上了阿里云。最最最恐怖的事来了，这里几乎 80%的业务都是 sql 写的。。。代码都很少。。

DelayNoMay

Sep 1, 2020

@VoidChen 哪家公司，学好 sql，可以到一些传统企业养老了，运气好还可以进央企

VoidChen

Sep 1, 2020

@DelayNoMay 我还年轻啊。。。晚点再去吧。。。

justgodlike1993

Sep 1, 2020

能给个数据吗

justgodlike1993

Sep 1, 2020

SELECT ct,GROUP_CONCAT(time) times,classroom,num FROM (SELECT classroom,time,GROUP_CONCAT(course1) ct,COUNT(course1) num FROM chongtu_test GROUP BY class_room,time HAVING num > 1) tmp GROUP BY ct;

需要一个索引，(classroom,time,course1)

VoidChen

Sep 1, 2020

@justgodlike1993 感谢大佬调试！
![dximHf.png]( https://s1.ax1x.com/2020/09/01/dximHf.png)
[![dxitbV.md.png]( https://s1.ax1x.com/2020/09/01/dxitbV.md.png)]( https://imgchr.com/i/dxitbV)
有一点点问题我调整了下之后的结果，关键其实是要把那种子集排除掉。。。

VoidChen

Sep 1, 2020

@justgodlike1993 数据。。。
INSERT INTO lin.chongtu_test (course1, course2, time, classroom, date) VALUES ('class2', 'class1', 15, '1', '20200831');
INSERT INTO lin.chongtu_test (course1, course2, time, classroom, date) VALUES ('class2', 'class1', 16, '1', '20200831');
INSERT INTO lin.chongtu_test (course1, course2, time, classroom, date) VALUES ('class2', 'class1', 17, '1', '20200831');
INSERT INTO lin.chongtu_test (course1, course2, time, classroom, date) VALUES ('class2', 'class1', 18, '1', '20200831');
INSERT INTO lin.chongtu_test (course1, course2, time, classroom, date) VALUES ('class3', 'class1', 17, '1', '20200831');
INSERT INTO lin.chongtu_test (course1, course2, time, classroom, date) VALUES ('class3', 'class2', 17, '1', '20200831');
INSERT INTO lin.chongtu_test (course1, course2, time, classroom, date) VALUES ('class3', 'class1', 18, '1', '20200831');
INSERT INTO lin.chongtu_test (course1, course2, time, classroom, date) VALUES ('class3', 'class2', 18, '1', '20200831');
INSERT INTO lin.chongtu_test (course1, course2, time, classroom, date) VALUES ('class4', 'class3', 19, '1', '20200831');
INSERT INTO lin.chongtu_test (course1, course2, time, classroom, date) VALUES ('class1', 'class2', 15, '1', '20200831');
INSERT INTO lin.chongtu_test (course1, course2, time, classroom, date) VALUES ('class1', 'class2', 16, '1', '20200831');
INSERT INTO lin.chongtu_test (course1, course2, time, classroom, date) VALUES ('class1', 'class2', 17, '1', '20200831');
INSERT INTO lin.chongtu_test (course1, course2, time, classroom, date) VALUES ('class1', 'class2', 18, '1', '20200831');
INSERT INTO lin.chongtu_test (course1, course2, time, classroom, date) VALUES ('class1', 'class3', 17, '1', '20200831');
INSERT INTO lin.chongtu_test (course1, course2, time, classroom, date) VALUES ('class2', 'class3', 17, '1', '20200831');
INSERT INTO lin.chongtu_test (course1, course2, time, classroom, date) VALUES ('class1', 'class3', 18, '1', '20200831');
INSERT INTO lin.chongtu_test (course1, course2, time, classroom, date) VALUES ('class2', 'class3', 18, '1', '20200831');
INSERT INTO lin.chongtu_test (course1, course2, time, classroom, date) VALUES ('class3', 'class4', 19, '1', '20200831');
INSERT INTO lin.chongtu_test (course1, course2, time, classroom, date) VALUES ('class1', 'class2', 23, '1', '20200901');
INSERT INTO lin.chongtu_test (course1, course2, time, classroom, date) VALUES ('class2', 'class1', 23, '1', '20200901');

VoidChen

Sep 1, 2020

@justgodlike1993 我加了个日期来区分不同天的冲突，这个可以不管他。。默认都在同一天就行了。。

justgodlike1993

Sep 1, 2020

SELECT ct,GROUP_CONCAT(time) times,classroom,num FROM (SELECT classroom,time,GROUP_CONCAT(DISTINCT course1) ct,COUNT( DISTINCT course1) num FROM chongtu_test GROUP BY date,classroom,time HAVING num > 1) tmp GROUP BY ct;

justgodlike1993

Sep 1, 2020

@VoidChen

justgodlike1993

Sep 1, 2020

GROUP BY ct,date

VoidChen

Sep 1, 2020

@justgodlike1993 不行啦，DISTINCT 我上一把就在你那个 sql 上加上测试过了。
"class1,class2","15,16,23",1,2
"class1,class2,class3","17,18",1,3
这个 class1,class2 其实是 class1,class2,class3 的子集，需要丢掉的

justgodlike1993

Sep 2, 2020

SELECT a.*,b.ct,b.times,b.date FROM (SELECT date,ct,GROUP_CONCAT(time) times,classroom,num FROM (SELECT date,classroom,time,GROUP_CONCAT(DISTINCT course1) ct,COUNT( DISTINCT course1) num FROM chongtu_test GROUP BY date,classroom,time HAVING num > 1) tmp GROUP BY date,ct) a,(SELECT date,ct,GROUP_CONCAT(time) times,classroom,num FROM (SELECT date,classroom,time,GROUP_CONCAT(DISTINCT course1) ct,COUNT( DISTINCT course1) num FROM chongtu_test GROUP BY date,classroom,time HAVING num > 1) tmp GROUP BY date,ct) b WHERE CONCAT(',',REPLACE(a.ct,',',',,'),',') LIKE CONCAT('%,',REPLACE(b.ct,',',',%,'),',%') AND a.date=b.date AND IF(LOCATE(',',a.times)=0,a.times=b.times,a.times!=b.times);
再试下#24

justgodlike1993

Sep 2, 2020

貌似还是有问题

VoidChen

Sep 2, 2020

@justgodlike1993 有啊，还有 groupby 查询的字段必须出现在 groupby 里。。。

justgodlike1993

Sep 2, 2020

SELECT date,classroom,MAX(ct) ct,GROUP_CONCAT(time) times FROM (SELECT t.*,CONCAT(t.c1,',',GROUP_CONCAT(DISTINCT 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);

justgodlike1993

Sep 2, 2020

4 个课程同时冲突会有多少记录啊

VoidChen

Sep 3, 2020

@justgodlike1993 理论上不多，不过我实际看数据就很多。。就是排课的人就一个课室一直选，弄完再慢慢改。。

VoidChen

Sep 3, 2020

@justgodlike1993 group by 有的才能 select
![w9xGGQ.png]( https://s1.ax1x.com/2020/09/03/w9xGGQ.png)

justgodlike1993

Sep 3, 2020

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

VoidChen

Sep 3, 2020

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

justgodlike1993

Sep 3, 2020

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

Sep 3, 2020

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

Sep 3, 2020

@justgodlike1993 这次很近了，就差一个没去掉！
![wCteAI.png]( https://s1.ax1x.com/2020/09/03/wCteAI.png)
我这多加了 2 条数据，一天内出现过的冲突都归入最多冲突那条就可以了

VoidChen

Sep 3, 2020

@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');