国产xxxx99真实实拍_久久不雅视频_高清韩国a级特黄毛片_嗯老师别我我受不了了小说

資訊專欄INFORMATION COLUMN

數據庫分組查詢最大值的問題

MkkHou / 2032人閱讀

摘要:現在執行如上查詢,結果為使用內連接和左連接的兩條語句,執行結果保持一致,都能顯示出各組最大值的多行記錄。子句常與聚集函數聯用,此時聚集函數以基本組為計算對象。

這里探討了分組查詢最大值(group-wise-max)的問題。涉及到 SQL 查詢語句中的 GROUP BY 子句及連接(JOIN)操作。

問題

本文緣起于 SegmentFault上 的一個問題:
http://segmentfault.com/q/1010000004138670

下面是提問者的表和測試數據:

create table test (
    id smallint unsigned not null auto_increment,
    name varchar(20) not null,
    age smallint unsigned not null,
    class smallint unsigned not null,
    primary key (id));

insert into test (name, age, class) values
("wang", 11, 3), ("qiu", 22, 1), ("liu", 42, 1), ("qian", 20, 2),
("zheng", 20, 2), ("li", 33, 3);

可以理解成學生信息,簡單的 SELECT 一下:

mysql> select * from test;
+----+-------+-----+-------+
| id | name  | age | class |
+----+-------+-----+-------+
|  1 | wang  |  11 |     3 |
|  2 | qiu   |  22 |     1 |
|  3 | liu   |  42 |     1 |
|  4 | qian  |  20 |     2 |
|  5 | zheng |  20 |     2 |
|  6 | li    |  33 |     3 |
+----+-------+-----+-------+

問題:如何選出每班中年齡最大者?

第一次嘗試

使用 GROUP BY 子句,這一點毫無疑問。

select class, max(age) from test group by class;
+-------+----------+
| class | max(age) |
+-------+----------+
|     1 |       42 |
|     2 |       20 |
|     3 |       33 |
+-------+----------+

結果按 class 分組了,最大年齡也選出來了,但是沒有 idname

第二次嘗試

添加其它列到 SELECT 子句。

select id, name, max(age), class from test group by class;
+----+------+----------+-------+
| id | name | max(age) | class |
+----+------+----------+-------+
|  2 | qiu  |       42 |     1 |
|  4 | qian |       20 |     2 |
|  1 | wang |       33 |     3 |
+----+------+----------+-------+

結果并不正確,各列發生"錯位",年齡 42 的應該是 liu 而不是 qiu,原因是它違反了下面這條規則:

包含 GROUP BY 的 SQL 語句,被 select 的列要么使用聚合函數,要么出現在GROUP BY 子句中。

上面的 SELECT 語句,idname 沒有出現在 GROUP BY 子句,也沒有使用聚合函數,所以它違反了規則,不是一條正確的SQL語句。

第三次嘗試
select t1.*
from test t1,
  (select class, max(age) as age from test group by class) t2
where t1.class = t2.class and t1.age = t2.age;
+----+-------+-----+-------+
| id | name  | age | class |
+----+-------+-----+-------+
|  3 | liu   |  42 |     1 |
|  5 | zheng |  22 |     2 |
|  6 | li    |  33 |     3 |
+----+-------+-----+-------+

結果正確。

這條語句引用了兩個表(t1t2),語義上相當于內連接(INNER JOIN)。

第四次嘗試

使用內連接改寫上面那條語句。

注意:關鍵字 JOIN,就是指 INNER JOIN。當然你也可以顯式地寫成 INNER JOIN。

select t1.*
from test t1
join (
  select class, max(age) as age
  from test
  group by class) t2
on t1.class = t2.class and t1.age = t2.age;
+----+-------+-----+-------+
| id | name  | age | class |
+----+-------+-----+-------+
|  3 | liu   |  42 |     1 |
|  5 | zheng |  22 |     2 |
|  6 | li    |  33 |     3 |
+----+-------+-----+-------+
第五次嘗試

使用左連接(LEFT JOIN)來實現。沒有用到 GROUP BY。

select t1.*
from test t1
left join test t2 on t1.class = t2.class and t1.age < t2.age
where t2.class is null;

根據定義,左連接會從左表那里返回所有的行,即使在右表中沒有匹配的行。

這條語句參考自:The Rows Holding the Group-wise Maximum of a Certain Column

原理在此:JOIN Syntax

摘錄如下:

If there is no matching row for the right table in the ON or USING part in a LEFT JOIN, a row with all columns set to NULL is used for the right table. You can use this fact to find rows in a table that have no counterpart in another table:

SELECT left_tbl.*
  FROM left_tbl LEFT JOIN right_tbl ON left_tbl.id = right_tbl.id
  WHERE right_tbl.id IS NULL;

This example finds all rows in left_tbl with an id value that is not present in right_tbl (that is, all rows in left_tbl with no corresponding row in right_tbl). This assumes that right_tbl.id is declared NOT NULL.

可見,這條語句中的 WHERE 子句,其實可以用任何列作為條件。下面這句也是一樣的效果:

select t1.*
from test t1
left join test t2 on t1.class = t2.class and t1.age < t2.age
where t2.id is null;
各組最大值不唯一的情況

把 zheng 的年齡改為 20,那么 class 2 中,qian 和 zheng 的年齡都是最大值 20。

update test set age=20 where name="zheng";

現在執行如上查詢,結果為:

+----+-------+-----+-------+
| id | name  | age | class |
+----+-------+-----+-------+
|  3 | liu   |  42 |     1 |
|  4 | qian  |  20 |     2 |
|  5 | zheng |  20 |     2 |
|  6 | li    |  33 |     3 |
+----+-------+-----+-------+

使用內連接和左連接的兩條語句,執行結果保持一致,都能顯示出各組最大值的多行記錄。

補充一些 GROUP BY 的理論知識

GROUP BY 子句將表按列的值分組,列的值相同的分在一組。如果 GROUP BY 后有多個列名,則先按第一列名分組,再按第二列名在組中分組,原則上可以一直分下去,直到在所有基本組中,GROUP BY 子句所指定的列都具有相同的值,HAVING 后的條件是選擇基本組的條件。GROUP BY 子句常與聚集函數聯用,此時聚集函數以基本組為計算對象。加了 GROUP BY 子句后,SELECT 子句所取的值必須在基本組中是唯一的,即只能是 GROUP BY 子句所指明的列或聚集函數。若無 GROUP BY 子句,則聚集函數以整個表為計算對象,此時 SELECT 子句只能取聚集函數,而不能取某一列。

王能斌,《數據庫系統教程》(第二版),3.4.3。

文章版權歸作者所有,未經允許請勿轉載,若此文章存在違規行為,您可以聯系管理員刪除。

轉載請注明本文地址:http://specialneedsforspecialkids.com/yun/17491.html

相關文章

  • 關系據庫SQL之基本數據查詢:子查詢分組查詢、模糊查詢

    摘要:連接查詢涉及兩個及以上的表查詢為連接查詢。查詢二班學生成績二班聚合函數查詢聚合函數是一個值的集合為輸入,返回單個值的函數。具體的數據庫還會預定義一些其他常用的函數,比如字符串相聚合函數時間聚合函數。 前言 上一篇關系數據庫常用SQL語句語法大全主要是關系型數據庫大體結構,本文細說一下關系型數據庫查詢的SQL語法。 showImg(http://upload-images.jiansh...

    VishKozus 評論0 收藏0

發表評論

0條評論

MkkHou

|高級講師

TA的文章

閱讀更多
最新活動
閱讀需要支付1元查看
<