关于一道mysql查询面试题的思考解决过程-tutorial mysql-php.cn

Rumah

pangkalan data

tutorial mysql

关于一道mysql查询面试题的思考解决过程

藏色散人

Apr 08, 2020 am 08:57 AM

mysql

啰嗦（可跳过）

前几天面试，笔试时遇到这道题，读了几遍题目都是懵懵懂懂，“一个段时间内至少N天，这N天中每天的分数总和要大于M”，好绕，最后没有写对。

今天想起来这道题，写出了答案并进行了sql语句的验证。

推荐：《mysql视频教程》，《mysql面试题2020》

问题

某游戏使用mysql数据库，数据表 scores 记录用户得分历史，uid 代表用户ID， score 表示分数， date 表示日期，每个用户每天都会产生多条记录。

数据结构以及数据行如下：

现在需要一份用户列表，这些用户在2017年3月份的31天中，至少要有16天，每天得分总和大于40分。使用一条sql语句表示。

思路

重新梳理需求,画出重点。

现在需要一份用户列表，这些用户在2017年3月份的31天中，至少要有16天，每天得分总和大于40分。使用一条sql语句表示。

用户列表

代表一个不重复的 uid 列表，可使用 DISTINCT uid 或 GROUP BY uid 来实现。

在2017年3月份的31天中

使用 where 语句限定时间范围。

至少要有16天

需要对天 date 进行聚合，使用聚合函数 COUNT(*) > 15来进行判断。

(每人)每天得分总和大于40

需要对每天分数 score 分数进行聚合，使用聚合函数对 SUM(score) > 40来进行判断。

此处有2处聚合函数，但是是针对不同维度的（天和每天里的分数），所以需要使用子查询，将2处聚合分别放置在内外层的sql语句上。

由“从内到外”的原则，我们先对每天的得分进行聚合，那就是对天进行聚合。

-- 在2017年3月份的31天中
select * from scores where `date` >= &#39;2017-03-01&#39; and `date` <= &#39;2017-03-31&#39;;
-- (每人)每天得分总和大于40
-- 使用 group by uid,date 实现对分数进行聚合，使用 having  sum() 过滤结果
select uid,date from scores where `date` >= &#39;2017-03-01&#39; and `date` <= &#39;2017-03-31&#39; group by uid, `date` having sum(score) > 40;
-- 至少要有16天
-- 以上条结果为基础，在对 group by uid 实现对天进行聚合，使用 having  count() 过滤结果
select uid from (
    select uid,date from scores where `date` >= &#39;2017-03-01&#39; and `date` <= &#39;2017-03-31&#39; group by uid, `date` having sum(score) > 40
) group by uid having count(*) > 15;

答案

SELECT uid FROM (
    SELECT uid,date FROM WHERE `date` >= &#39;2017-03-01&#39; AND `date` <= &#39;2017-03-31&#39; GROUP BY uid,`date` HAVING SUM(score) > 40
) WHERE GROUP BY uid HAVING count(*) > 15;

验证

-- 结构
CREATE TABLE `scores` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` int(11) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  `date` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 数据
INSERT INTO `scores` VALUES (&#39;1&#39;, &#39;1&#39;, &#39;1&#39;, &#39;2018-04-03&#39;);
INSERT INTO `scores` VALUES (&#39;2&#39;, &#39;1&#39;, &#39;2&#39;, &#39;2018-04-03&#39;);
INSERT INTO `scores` VALUES (&#39;3&#39;, &#39;1&#39;, &#39;1&#39;, &#39;2018-04-04&#39;);
INSERT INTO `scores` VALUES (&#39;11&#39;, &#39;1&#39;, &#39;4&#39;, &#39;2018-04-04&#39;);
INSERT INTO `scores` VALUES (&#39;12&#39;, &#39;1&#39;, &#39;3&#39;, &#39;2018-04-06&#39;);
INSERT INTO `scores` VALUES (&#39;4&#39;, &#39;1&#39;, &#39;3&#39;, &#39;2018-04-07&#39;);
INSERT INTO `scores` VALUES (&#39;5&#39;, &#39;2&#39;, &#39;2&#39;, &#39;2018-04-04&#39;);
INSERT INTO `scores` VALUES (&#39;6&#39;, &#39;2&#39;, &#39;4&#39;, &#39;2018-04-04&#39;);
INSERT INTO `scores` VALUES (&#39;7&#39;, &#39;2&#39;, &#39;1&#39;, &#39;2018-04-03&#39;);
INSERT INTO `scores` VALUES (&#39;8&#39;, &#39;3&#39;, &#39;3&#39;, &#39;2018-04-06&#39;);
INSERT INTO `scores` VALUES (&#39;9&#39;, &#39;3&#39;, &#39;1&#39;, &#39;2018-04-05&#39;);
INSERT INTO `scores` VALUES (&#39;10&#39;, &#39;3&#39;, &#39;2&#39;, &#39;2018-04-04&#39;);
-- 因为数据录入量有限，我们将结果改为修改改为：
-- 获取一个用户列表，时间范围是4号到6号，至少要有2天，每天分数总和大于2。
-- 查询
-- 非最精简语句，包含调试语句，可分段运行查看各个语句部分的效果。
SELECT
    uid
FROM
    (
        SELECT
            uid,
            `date`,
            sum(score) AS total_score
        FROM
            scores
        WHERE
            `date` > &#39;2018-04-03&#39;
        AND `date` < &#39;2018-04-07&#39;
        GROUP BY
            uid,
            `date`
        HAVING
            total_score > 2
        ORDER BY
            uid,
            date
    ) AS a
GROUP BY
    uid
HAVING
    count(*) > 1;
-- 答案是：
uid : 1

Atas ialah kandungan terperinci 关于一道mysql查询面试题的思考解决过程. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!

Kenyataan Laman Web ini

Kandungan artikel ini disumbangkan secara sukarela oleh netizen, dan hak cipta adalah milik pengarang asal. Laman web ini tidak memikul tanggungjawab undang-undang yang sepadan. Jika anda menemui sebarang kandungan yang disyaki plagiarisme atau pelanggaran, sila hubungi admin@php.cn

Alat AI Hot

Undress AI Tool

Gambar buka pakaian secara percuma

Undresser.AI Undress

Apl berkuasa AI untuk mencipta foto bogel yang realistik

AI Clothes Remover

Alat AI dalam talian untuk mengeluarkan pakaian daripada foto.

Clothoff.io

Penyingkiran pakaian AI

Video Face Swap

Tukar muka dalam mana-mana video dengan mudah menggunakan alat tukar muka AI percuma kami!

Tunjukkan Lagi

Artikel Panas

Skop pembolehubah PHP dijelaskan

1 bulan yang lalu By 百草

Petua untuk menulis komen php

4 minggu yang lalu By 百草

Mengulas kod dalam php

4 minggu yang lalu By 百草

<🎜>: Tumbuh Taman - Panduan Lengkap untuk Melancong Pedagang

3 minggu yang lalu By Jack chen

Destiny 2: The Edge of Fate - Bagaimana Pengubah Kesukaran Berfungsi

4 minggu yang lalu By DDD

Tunjukkan Lagi

Alat panas

Notepad++7.3.1

Editor kod yang mudah digunakan dan percuma

SublimeText3 versi Cina

Versi Cina, sangat mudah digunakan

Hantar Studio 13.0.1

Persekitaran pembangunan bersepadu PHP yang berkuasa

Dreamweaver CS6

Alat pembangunan web visual

SublimeText3 versi Mac

Perisian penyuntingan kod peringkat Tuhan (SublimeText3)

Tunjukkan Lagi

Topik panas

Tutorial Laravel

1604

Tutorial PHP

1510

276

Tunjukkan Lagi

Related knowledge

Mengoptimumkan MySQL untuk Penyimpanan Data Kewangan Jul 27, 2025 am 02:06 AM

MySQL perlu dioptimumkan untuk sistem kewangan: 1. Data kewangan mesti digunakan untuk memastikan ketepatan menggunakan jenis perpuluhan, dan DateTime digunakan dalam bidang masa untuk mengelakkan masalah zon waktu; 2. Reka bentuk indeks harus munasabah, mengelakkan kemas kini medan yang kerap untuk membina indeks, menggabungkan indeks dalam urutan pertanyaan dan indeks yang tidak berguna secara berkala; 3. Gunakan urus niaga untuk memastikan konsistensi, mengawal granulariti transaksi, elakkan urus niaga yang panjang dan operasi bukan teras yang tertanam di dalamnya, dan pilih tahap pengasingan yang sesuai berdasarkan perniagaan; 4. Partition Data Sejarah mengikut Masa, Arkib Data Sejuk dan Gunakan Jadual Mampat untuk meningkatkan kecekapan pertanyaan dan mengoptimumkan penyimpanan.

Mengoptimumkan MySQL untuk suapan data masa nyata Jul 26, 2025 am 05:41 AM

Tooptimizemysqlforreal-timedatafeeds, firstChoosetheinnodbstorageenginfortransactionsandrow-levellocking, usememoryorrocksdbforthfforatarydata, dan partitiontime-SeriesDataByTime.Second, orderstrategybybyapplydeSdeAndeLyPlyDeSdeLyPlyDeSdeLySdeSdeLySdeSdeSdeLySdeSdeLySdeSdeLySdExteSdExteSdExteSdExteSdExteSdExteSdExteSdExteSdExteSdeShin

Analisis kos-manfaat pangkalan data MySQL untuk penghijrahan awan Jul 26, 2025 am 03:32 AM

Sama ada MySQL bernilai bergerak ke awan bergantung kepada senario penggunaan tertentu. Jika perniagaan anda perlu dilancarkan dengan cepat, mengembangkan secara elastik dan memudahkan operasi dan penyelenggaraan, dan boleh menerima model bayar-as-you-go, kemudian bergerak ke awan adalah bernilai; Tetapi jika pangkalan data anda stabil untuk masa yang lama, latensi sensitif atau sekatan pematuhan, ia mungkin tidak kos efektif. Kekunci untuk mengawal kos termasuk memilih vendor dan pakej yang betul, mengkonfigurasi sumber yang munasabah, menggunakan contoh yang disediakan, menguruskan log sandaran dan mengoptimumkan prestasi pertanyaan.

Menjamin MySQL dengan keistimewaan tahap objek Jul 29, 2025 am 01:34 AM

Tosecuremysqleffectely, useObject-levelprivilygestestestigmilimitusAracCessBasedontheArtheirspecificNeeds.beginbyBySpelythatthatThatBje-levelprivileGeGsapplyTaDataBases, Tabel, orcolumns.

Amalan terbaik untuk menguruskan jadual mysql yang besar Aug 05, 2025 am 03:55 AM

Apabila berurusan dengan jadual besar, prestasi MySQL dan penyelenggaraan menghadapi cabaran, dan perlu bermula dari reka bentuk struktur, pengoptimuman indeks, strategi sub-meja jadual, dan sebagainya. 1. Gunakan indeks overlay untuk meningkatkan kecekapan pertanyaan; kerap menganalisis log pertanyaan perlahan dan memadam indeks tidak sah. 2. 3. Pertimbangkan pembacaan dan penulisan pemisahan dan pemisahan perpustakaan: Baca dan menulis pemisahan mengurangkan tekanan pada perpustakaan utama. Pemisahan perpustakaan dan pemisahan jadual sesuai untuk senario dengan sejumlah besar data. Adalah disyorkan untuk menggunakan middleware dan menilai masalah urus niaga dan masalah pertanyaan silang. Perancangan awal dan pengoptimuman berterusan adalah kunci.

Mengoptimumkan MySQL untuk Sistem Pengurusan Kandungan (CMS) Jul 28, 2025 am 03:19 AM

ToimprovemyMySffeForcMSplatformsLikewAwewewewordPress, firstimplemememememememememementerangkangkangkangkangkuNerGENGAgingPluginsLIKLIKLIKLIKeDIsdemeMemCached, enabheMySqlQueryCaching (ifipplicababable), andusePageCachingplugIsvestatiCatiCS

Melaksanakan Penapis Replikasi Pangkalan Data MySQL Jul 28, 2025 am 02:36 AM

Penapisan replikasi MySQL boleh dikonfigurasi di perpustakaan utama atau perpustakaan hamba. Perpustakaan utama mengawal generasi binlog melalui binlog-do-db atau binlog-ignore-db, yang sesuai untuk mengurangkan jumlah log; Aplikasi data dikawal oleh replication-db-db, replication-ignore-db, replicate-do-table, replicate-ignore-table dan wildcard rules meniru-liar-do-table dan replication-liar-ignore-meja. Ia lebih fleksibel dan kondusif untuk pemulihan data. Semasa mengkonfigurasi, anda perlu memberi perhatian kepada perintah peraturan, tingkah laku penyataan silang,

Apakah perbezaan antara Truncate, Padam, dan Drop dalam MySQL? Aug 05, 2025 am 09:39 AM

Deleteremovesspecificorallrows, keepstableStructure, membolehkan rollrackandtriggers, anddoesnotresetauto-increment; 2.truncatequicklyremovesallrows, resetsauto-increment, tidak boleh dibebaskan

See all articles