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
Aha1
V2EX  ›  MySQL

一道 SQL 面试题

  •  
  •   Aha1 · 2018-10-24 20:24:11 +08:00 · 4795 次点击
    这是一个创建于 2240 天前的主题,其中的信息可能已经有所发展或是发生改变。
    • 编写一个 SQL 查询,与上一次成绩相比成绩更好的考试日期
      ID Date Score
    19 条回复    2018-10-25 19:44:24 +08:00
    markgor
        1
    markgor  
       2018-10-24 20:50:00 +08:00
    SELECT Date FROM table ORDER BY Score DESC LIMIT 1
    xx19941215
        2
    xx19941215  
       2018-10-24 21:00:41 +08:00
    select `s2`.`date` from `score` `s2` left join `score` `s1` on `s2`.`score` > `s1`.`score` where `s2`.`id` = `s1`.`id` + 1;
    xx19941215
        3
    xx19941215  
       2018-10-24 21:11:10 +08:00
    DROP TABLE IF EXISTS `score`;

    CREATE TABLE `score` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `date` timestamp NULL DEFAULT NULL,
    `score` int(11) DEFAULT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

    LOCK TABLES `score` WRITE;
    /*!40000 ALTER TABLE `score` DISABLE KEYS */;

    INSERT INTO `score` (`id`, `date`, `score`)
    VALUES
    (1,'2018-10-10 00:00:00',90),
    (2,'2018-10-11 00:00:00',100),
    (3,'2018-10-16 00:00:00',89),
    (4,'2018-10-17 00:00:00',91),
    (5,'2018-10-18 00:00:00',92);

    /*!40000 ALTER TABLE `score` ENABLE KEYS */;
    UNLOCK TABLES;

    表结构
    x66
        4
    x66  
       2018-10-24 21:39:41 +08:00   ❤️ 1
    2 楼的 SQL 如果 id 不连续就 GG 了。

    SELECT t1.date FROM test t1
    WHERE t1.scope > ( SELECT t2.scope from test t2 where t2.date = ( SELECT max( t3.date ) FROM test t3 WHERE t3.date < t1.date ))
    x66
        5
    x66  
       2018-10-24 21:40:34 +08:00
    @x66 #4 scope =============> score 建表的时候写错了
    lasuar
        6
    lasuar  
       2018-10-24 21:44:58 +08:00
    先求出上一次考试日期
    set @last_date = SELECT MIN(Date) FROM tb ORDER BY Date DESC LIMIT 2 (我理解上一次考试之后还有一次考试哈)
    再求出上一次考试成绩
    set @last_score = SELECT score FROM tb WHERE Date =last_date
    最后求出比[上一次考试的成绩]还要好的[那一 /几次考试的日期]
    SELECT Date FROM tb WHERE score>last_score
    结果可能有多个。写成一条 SQL 即可
    carlclone
        7
    carlclone  
       2018-10-24 22:38:21 +08:00
    有个思路, 两个相同的表按 date 排序,有一张表去掉最新的一条,用他们的 order 进行关联后比较大小,sql 应该能实现吧?

    ID DATE Score Order ID Date Score Order
    3 0923 95 1 2 0918 50 1
    2 0918 50 2 1 0917 90 2
    1 0917 90 3
    carlclone
        8
    carlclone  
       2018-10-24 22:38:46 +08:00
    ....发出来格式变了
    Alexhohom
        9
    Alexhohom  
       2018-10-24 22:43:18 +08:00
    select x.Date from table as x left join (select top 1 * from table order by dtTime desc)x1 on 1=1 where x.Score>x1.Score
    Alexhohom
        10
    Alexhohom  
       2018-10-24 22:43:52 +08:00
    @Alexhohom #9 dtTime 是 Date...用的自己的库测试的
    liprais
        11
    liprais  
       2018-10-24 22:48:20 +08:00
    这个题只要把考试的顺序搞出来再来个自关联就行了
    mysql 没有窗口函数可能麻烦一点
    sutra
        12
    sutra  
       2018-10-24 22:50:05 +08:00
    create table t(id integer, date timestamp with time zone, score float);
    insert into t(id, date, score);
    INSERT INTO t (id, date, score)
    VALUES
    (1,'2018-10-10 00:00:00',90),
    (2,'2018-10-11 00:00:00',100),
    (3,'2018-10-16 00:00:00',89),
    (4,'2018-10-17 00:00:00',91),
    (5,'2018-10-18 00:00:00',92);

    select * from t;

    id | date | score
    ----+------------------------+-------
    1 | 2018-10-10 00:00:00+08 | 90
    2 | 2018-10-11 00:00:00+08 | 100
    3 | 2018-10-16 00:00:00+08 | 89
    4 | 2018-10-17 00:00:00+08 | 91
    5 | 2018-10-18 00:00:00+08 | 92
    (5 rows)

    select t.id, t.date, t.score
    from (
    select full_cmp.id1 from (
    select t0.id id0, t1.id id1
    from t t0, t t1
    where
    t1.date > t0.date
    and t1.score > t0.score
    order by t1.date
    ) full_cmp
    group by full_cmp.id1
    ) cmp, t
    where cmp.id1 = t.id
    order by t.date;
    sutra
        13
    sutra  
       2018-10-24 22:50:42 +08:00
    上面一个回复漏了结果:

    id | date | score
    ----+------------------------+-------
    2 | 2018-10-11 00:00:00+08 | 100
    4 | 2018-10-17 00:00:00+08 | 91
    5 | 2018-10-18 00:00:00+08 | 92
    (3 rows)
    sutra
        14
    sutra  
       2018-10-24 22:52:43 +08:00
    再补充下,上面这个是在 PostgreSQL 下测试的,没注意看节点是 MySQL ……
    reus
        15
    reus  
       2018-10-25 00:20:54 +08:00
    换了有窗口函数的,一个 lag 拍上去就行
    select date from (
    select score - lag(score, 1) over (order by date asc) as diff, date
    from scores
    ) t0
    where diff > 0

    PostgreSQL 和 MySQL 8 都支持的。
    liprais
        16
    liprais  
       2018-10-25 00:22:38 +08:00
    @sutra pg 为啥不用 window function
    sutra
        17
    sutra  
       2018-10-25 01:23:49 +08:00 via iPhone
    @reus @liprais 👍
    zhuawadao
        18
    zhuawadao  
       2018-10-25 13:19:16 +08:00
    SELECT t1.date from
    (
    select (@rowNO := @rowNo+1) AS rowno,a.date ,a.score
    from (SELECT date,score FROM score ORDER BY date desc) a,(select @rowNO :=0) b) t1,
    (
    select (@rowNO := @rowNo+1) AS rowno,a.date ,a.score
    from (SELECT date,score FROM score ORDER BY date desc) a,(select @rowNO :=0) b) t2
    where t1.rowno=t2.rowno+4 and t1.score>t2.score
    Gathaly
        19
    Gathaly  
       2018-10-25 19:44:24 +08:00
    id date score
    1 2018-10-10 00:00:00 90
    2 2018-10-11 00:00:00 100
    3 2018-10-16 00:00:00 89
    4 2018-10-17 00:00:00 91
    5 2018-10-18 00:00:00 92

    先做自连接,找出右边比左边分数高,且日期晚的项,然后再 group by 右表去除重复行

    SELECT

    t2.`id`,

    MAX(t2.`date`)

    FROM score t1 ,score t2

    WHERE

    t2.`score` > t1.`score` AND

    t2.`date` > t1.`date`

    GROUP BY

    t2.`id`, t2.`date`
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   5074 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 26ms · UTC 07:43 · PVG 15:43 · LAX 23:43 · JFK 02:43
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.