面試筆試常考的mysql 資料庫操作group by

來源:互聯網
上載者:User

標籤:面試   資料庫   mysql   

IT 面試中,資料庫的相關問題基本上屬於必考問題,而其中關於sql語句也是經常考察的一個重要知識點。


下面介紹下sql語句中一個比較重要的操作group by,他的重要行一方面體現在他的理解困難度,一方面體現應用中的長見性。


首先,給出一個studnet學生表:

CREATE TABLE `student` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(30) DEFAULT NULL,  `sex` tinyint(1) DEFAULT '0',  `score` int(10) NOT NULL,  `dept` varchar(10) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 


添加一些測試資料:


mysql> select * from student where id<10;+----+------+------+-------+---------+| id | name | sex  | score | dept    |+----+------+------+-------+---------+|  1 | a    |    1 |    90 | dev     ||  2 | b    |    1 |    90 | dev     ||  3 | b    |    0 |    88 | design  ||  4 | c    |    0 |    60 | sales   ||  5 | c    |    0 |    89 | sales   ||  6 | d    |    1 |   100 | product |+----+------+------+-------+---------+



給出需求,寫出sql:

給出各個部門最高學生的分數。

要想得到各個部門學生,首先就要分組,按照部門把他們分組,然後在各個部門中找到分數最高的就可以了。


所以sql語句為:

mysql> select *, max(score) as max  from student group by dept order by name;+----+------+------+-------+---------+------+| id | name | sex  | score | dept    | max  |+----+------+------+-------+---------+------+|  1 | a    |    1 |    90 | dev     |   90 ||  3 | b    |    0 |    88 | design  |   88 ||  4 | c    |    0 |    60 | sales   |   89 ||  6 | d    |    1 |   100 | product |  100 |+----+------+------+-------+---------+------+4 rows in set (0.00 sec)


這隻是個簡單的例子,我們可以再把這個例子複雜化,比如分數最高的必須是女生,即sex列值必須為1才挑選出,這時的sql語句應該為:

mysql> select *,max(score) as max from student group by dept having sex='1' order by name;+----+------+------+-------+---------+------+| id | name | sex  | score | dept    | max  |+----+------+------+-------+---------+------+|  1 | a    |    1 |    90 | dev     |   90 ||  6 | d    |    1 |   100 | product |  100 |+----+------+------+-------+---------+------+2 rows in set (0.46 sec)


這裡我們沒有用where語句而是用了having,這裡簡單說明一下,因為我們的條件是在分組後進行的,其實分組前挑選出sex=‘1‘,然後再按照dept部門分組,也是可行的,這裡就要看題目是怎麼要求的:

mysql> select *,max(score) as max from student where sex='1' group by dept order by name;+----+------+------+-------+---------+------+| id | name | sex  | score | dept    | max  |+----+------+------+-------+---------+------+|  1 | a    |    1 |    90 | dev     |   90 ||  6 | d    |    1 |   100 | product |  100 |+----+------+------+-------+---------+------+2 rows in set (0.05 sec)


查詢出的結果時一致的,如果把選擇條件改為必須部門所有人的分數之和大於150才能把分數最高的部門的人列出來,這裡就必須使用having了,因為 having 裡面可以使用彙總函式sum,並且也必須分完組我們才能得到這個組的總分數,才能比較是否該值大於150:

mysql> select *,max(score) as max from student   group by dept having sum(score)>150 order by name;+----+------+------+-------+---------+------+| id | name | sex  | score | dept    | max  |+----+------+------+-------+---------+------+|  1 | a    |    1 |    90 | dev     |   90 ||  6 | d    |    1 |   100 | product |  100 |+----+------+------+-------+---------+------+2 rows in set (0.00 sec)


額外增加一個例子,比如我要選出不重複的部門,我們可以使用

mysql> select distinct dept from student;+---------+| dept    |+---------+| dev     || design  || sales   || product |+---------+4 rows in set (0.02 sec)


但是如果我們還要列出他的id等一些其他資訊,我們如果這樣:

mysql> select name,distinct dept from student;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'distinct dept from student' at line 1

這是不行的,因為distinct只能放到開始位置,如果:

mysql> select distinct  dept,name from student;+---------+------+| dept    | name |+---------+------+| dev     | a    || dev     | b    || design  | b    || sales   | c    || product | d    || product | m    |+---------+------+6 rows in set (0.00 sec)


為什麼沒有達到預期的效果,因為distinct 作用到了2個欄位上,這時,我們就需要groub by 出場了。


mysql> select  dept,name from student group by dept;+---------+------+| dept    | name |+---------+------+| design  | b    || dev     | a    || product | d    || sales   | c    |+---------+------+4 rows in set (0.00 sec)


按照dept分組,自然就達到去重的目的了。所以有時候如果我們碰到了一個問題很難解決,比如用distinct去重,並帶上其他列值,我們就需要嘗試換個思路,可能答案自然就找到了。








相關文章

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.