首页 注册 登录
V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
› MySQL 5.5 Community Server
› MySQL 5.6 Community Server
› Percona Configuration Wizard
› XtraBackup 搭建主从复制
Great Sites on MySQL
› Percona
› MySQL Performance Blog
› Severalnines
推荐管理工具
› Sequel Pro
› phpMyAdmin
推荐书目
› MySQL Cookbook
MySQL 相关项目
› MariaDB
› Drizzle
参考文档
› http://mysql-python.sourceforge.net/MySQLdb.html
广告
VoidChen
V2EX  ›  MySQL

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

  •   
  •   VoidChen · 2020-09-01 11:01:30 +08:00 · 1334 次点击
    这是一个创建于 1545 天前的主题，其中的信息可能已经有所发展或是发生改变。

    要求：

    检查冲突，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 作为一条冲突记录

    数据：

    dvC5EF.png

    结果：

    dvCOu6.png

    我写的 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
  • course1
  • class1
  • class2
  • class3
    27 条回复  •  2020-09-03 11:10:59 +08:00
    VoidChen
        1
    VoidChen  
    OP
       2020-09-01 11:04:08 +08:00
    这里的 markdown 怎么不支持表格。。。我找个图床先 QAQ
    lewis89
        2
    lewis89  
       2020-09-01 11:21:05 +08:00
    @VoidChen #1
    其实.. 现在程序员都不怎么写复杂的 SQL

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

    除了复杂的报表需求类似 OLTP 的系统可能还你这种 SQL 大部分场景下 已经不需要复杂的 SQL 了
    VoidChen
        3
    VoidChen  
    OP
       2020-09-01 11:33:32 +08:00
    @lewis89 告诉你一个很恐怖的事。。。我过往 3 年经验都是做大数据开发的，实打实的写代码的那种，最近新入职公司，全上了阿里云。最最最恐怖的事来了，这里几乎 80%的业务都是 sql 写的。。。代码都很少。。
    DelayNoMay
        4
    DelayNoMay  
       2020-09-01 12:25:54 +08:00
    @VoidChen 哪家公司，学好 sql，可以到一些传统企业养老了，运气好还可以进央企
    VoidChen
        5
    VoidChen  
    OP
       2020-09-01 12:52:35 +08:00
    @DelayNoMay 我还年轻啊。。。晚点再去吧。。。
    justgodlike1993
        6
    justgodlike1993  
       2020-09-01 14:27:14 +08:00
    能给个数据吗
    justgodlike1993
        7
    justgodlike1993  
       2020-09-01 14:59:42 +08:00   ❤️ 1
    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
        8
    VoidChen  
    OP
       2020-09-01 16:11:22 +08:00
    @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
        9
    VoidChen  
    OP
       2020-09-01 16:13:18 +08:00
    @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
        10
    VoidChen  
    OP
       2020-09-01 16:14:17 +08:00
    @justgodlike1993 我加了个日期来区分不同天的冲突，这个可以不管他。。默认都在同一天就行了。。
    justgodlike1993
        11
    justgodlike1993  
       2020-09-01 16:42:12 +08:00
    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
        12
    justgodlike1993  
       2020-09-01 16:42:24 +08:00
    @VoidChen
    justgodlike1993
        13
    justgodlike1993  
       2020-09-01 16:47:05 +08:00
    GROUP BY ct,date
    VoidChen
        14
    VoidChen  
    OP
       2020-09-01 17:09:30 +08:00
    @justgodlike1993 不行啦，DISTINCT 我上一把就在你那个 sql 上加上测试过了。
    "class1,class2","15,16,23",1,2
    "class1,class2,class3","17,18",1,3
    这个 class1,class2 其实是 class1,class2,class3 的子集，需要丢掉的
    justgodlike1993
        15
    justgodlike1993  
       2020-09-02 13:52:15 +08:00
    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
        16
    justgodlike1993  
       2020-09-02 13:53:55 +08:00
    貌似还是有问题
    VoidChen
        17
    VoidChen  
    OP
       2020-09-02 16:10:18 +08:00
    @justgodlike1993 有啊，还有 groupby 查询的字段必须出现在 groupby 里。。。
    justgodlike1993
        18
    justgodlike1993  
       2020-09-02 18:59:10 +08:00
    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
        19
    justgodlike1993  
       2020-09-02 19:09:02 +08:00
    4 个课程同时冲突会有多少记录啊
    VoidChen
        20
    VoidChen  
    OP
       2020-09-03 08:51:39 +08:00
    @justgodlike1993 理论上不多，不过我实际看数据就很多。。就是排课的人就一个课室一直选，弄完再慢慢改。。
    VoidChen
        21
    VoidChen  
    OP
       2020-09-03 09:01:31 +08:00
    @justgodlike1993 group by 有的才能 select
    ![w9xGGQ.png]( https://s1.ax1x.com/2020/09/03/w9xGGQ.png)
    justgodlike1993
        22
    justgodlike1993  
       2020-09-03 10:13:20 +08:00
    版本的问题吧，我这能跑 - -
    VoidChen
        23
    VoidChen  
    OP
       2020-09-03 10:15:03 +08:00
    @justgodlike1993 mysql 吗？什么版本啊，话说这种是默认取第一条？如果能搞到这个可以好很多，相当于排序之后取第一条就出来了。我这 sql 最大的问题就在这
    justgodlike1993
        24
    justgodlike1993  
       2020-09-03 10:32:19 +08:00
    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
        25
    justgodlike1993  
       2020-09-03 11:01:13 +08:00
    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
        26
    VoidChen  
    OP
       2020-09-03 11:10:07 +08:00
    @justgodlike1993 这次很近了，就差一个没去掉！
    ![wCteAI.png]( https://s1.ax1x.com/2020/09/03/wCteAI.png)
    我这多加了 2 条数据，一天内出现过的冲突都归入最多冲突那条就可以了
    VoidChen
        27
    VoidChen  
    OP
       2020-09-03 11:10:59 +08:00
    @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');
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   2600 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 28ms · UTC 04:48 · PVG 12:48 · LAX 20:48 · JFK 23:48
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.