當用select*from 表名 group by ‘字段名1’ 將選出來的內(nèi)容將按照字段1分組，其他列不盡相同，會以最前面的內(nèi)容顯示：
創(chuàng)新互聯(lián)公司2013年成立，先為中方等服務建站，中方等地企業(yè)，進行企業(yè)商務咨詢服務。為中方企業(yè)網(wǎng)站制作PC+手機+微官網(wǎng)三網(wǎng)同步一站式服務解決您的所有建站問題。
GROUP BY 后面可以跟多個列名，表示以多列作為分組依據(jù)：
mysql select `name`,`date`,count(*) from `employee_tbl` group by `name`,date;
+------+---------------------+----------+
| name | date? ? ? ? ? ? ? ? | count(*) |
+------+---------------------+----------+
| 小麗 | 2016-04-19 15:26:02 |? ? ? ? 1 |
| 小明 | 2016-04-04 15:26:54 |? ? ? ? 1 |
| 小明 | 2016-04-11 15:26:40 |? ? ? ? 1 |
| 小明 | 2016-04-22 15:25:33 |? ? ? ? 1 |
| 小王 | 2016-04-07 15:26:14 |? ? ? ? 1 |
| 小王 | 2016-04-20 15:25:47 |? ? ? ? 1 |
+------+---------------------+----------+
6 rows in set (0.05 sec)
group by 后可以跟with? rollup，表示在進行分組統(tǒng)計的基礎(chǔ)上再次進行匯總統(tǒng)計（在每個分組下都會有統(tǒng)計匯總）：
mysql select*from r;
+------+-----+------+
| name | cat | fish |
+------+-----+------+
| 小明 | x? | 1? ? |
| 小明 | x? | 2? ? |
| 小明 | x? | 4? ? |
| 小明 | y? | 2? ? |
| 小明 | y? | 2? ? |
| 小明 | z? | 1? ? |
| 小紅 | x? | 1? ? |
| 小紅 | x? | 3? ? |
| 小紅 | y? | 1? ? |
| 小紅 | y? | 2? ? |
| 小青 | y? | 3? ? |
| 小青 | z? | 2? ? |
| 小青 | z? | 1? ? |
+------+-----+------+
13 rows in set (0.05 sec)
mysql select name,cat,fish,count(*) from r group by name with rollup;
+------+-----+------+----------+
| name | cat | fish | count(*) |
+------+-----+------+----------+
| 小紅 | x? | 1? ? |? ? ? ? 4 |
| 小明 | x? | 1? ? |? ? ? ? 6 |
| 小青 | y? | 3? ? |? ? ? ? 3 |
| NULL | y? | 3? ? |? ? ? 13 |
+------+-----+------+----------+
4 rows in set (0.05 sec)
mysql select name,cat,fish,count(*) from r group by name,cat with rollup;
+------+------+------+----------+
| name | cat? | fish | count(*) |
+------+------+------+----------+
| 小紅 | x? ? | 1? ? |? ? ? ? 2 |
| 小紅 | y? ? | 1? ? |? ? ? ? 2 |
| 小紅 | NULL | 1? ? |? ? ? ? 4 |
| 小明 | x? ? | 1? ? |? ? ? ? 3 |
| 小明 | y? ? | 2? ? |? ? ? ? 2 |
| 小明 | z? ? | 1? ? |? ? ? ? 1 |
| 小明 | NULL | 1? ? |? ? ? ? 6 |
| 小青 | y? ? | 3? ? |? ? ? ? 1 |
| 小青 | z? ? | 2? ? |? ? ? ? 2 |
| 小青 | NULL | 2? ? |? ? ? ? 3 |
| NULL | NULL | 2? ? |? ? ? 13 |
+------+------+------+----------+
11 rows in set (0.06 sec)
mysql select name,cat,fish,count(*) from r group by name,cat,fish with rollup;
+------+------+------+----------+
| name | cat? | fish | count(*) |
+------+------+------+----------+
| 小紅 | x? ? | 1? ? |? ? ? ? 1 |
| 小紅 | x? ? | 3? ? |? ? ? ? 1 |
| 小紅 | x? ? | NULL |? ? ? ? 2 |
| 小紅 | y? ? | 1? ? |? ? ? ? 1 |
| 小紅 | y? ? | 2? ? |? ? ? ? 1 |
| 小紅 | y? ? | NULL |? ? ? ? 2 |
| 小紅 | NULL | NULL |? ? ? ? 4 |
| 小明 | x? ? | 1? ? |? ? ? ? 1 |
| 小明 | x? ? | 2? ? |? ? ? ? 1 |
| 小明 | x? ? | 4? ? |? ? ? ? 1 |
| 小明 | x? ? | NULL |? ? ? ? 3 |
| 小明 | y? ? | 2? ? |? ? ? ? 2 |
| 小明 | y? ? | NULL |? ? ? ? 2 |
| 小明 | z? ? | 1? ? |? ? ? ? 1 |
| 小明 | z? ? | NULL |? ? ? ? 1 |
| 小明 | NULL | NULL |? ? ? ? 6 |
| 小青 | y? ? | 3? ? |? ? ? ? 1 |
| 小青 | y? ? | NULL |? ? ? ? 1 |
| 小青 | z? ? | 1? ? |? ? ? ? 1 |
| 小青 | z? ? | 2? ? |? ? ? ? 1 |
| 小青 | z? ? | NULL |? ? ? ? 2 |
| 小青 | NULL | NULL |? ? ? ? 3 |
| NULL | NULL | NULL |? ? ? 13 |
+------+------+------+----------+
23 rows in set (0.07 sec)
mysql select name,cat,fish,sum(fish) from r group by name with rollup;
+------+-----+------+-----------+
| name | cat | fish | sum(fish) |
+------+-----+------+-----------+
| 小紅 | x? | 1? ? |? ? ? ? 7 |
| 小明 | x? | 1? ? |? ? ? ? 12 |
| 小青 | y? | 3? ? |? ? ? ? 6 |
| NULL | y? | 3? ? |? ? ? ? 25 |
+------+-----+------+-----------+
4 rows in set (0.05 sec)
mysql select name,cat,fish,sum(fish) from r group by name,cat with rollup;
+------+------+------+-----------+
| name | cat? | fish | sum(fish) |
+------+------+------+-----------+
| 小紅 | x? ? | 1? ? |? ? ? ? 4 |
| 小紅 | y? ? | 1? ? |? ? ? ? 3 |
| 小紅 | NULL | 1? ? |? ? ? ? 7 |
| 小明 | x? ? | 1? ? |? ? ? ? 7 |
| 小明 | y? ? | 2? ? |? ? ? ? 4 |
| 小明 | z? ? | 1? ? |? ? ? ? 1 |
| 小明 | NULL | 1? ? |? ? ? ? 12 |
| 小青 | y? ? | 3? ? |? ? ? ? 3 |
| 小青 | z? ? | 2? ? |? ? ? ? 3 |
| 小青 | NULL | 2? ? |? ? ? ? 6 |
| NULL | NULL | 2? ? |? ? ? ? 25 |
+------+------+------+-----------+
11 rows in set (0.06 sec)
mysql select name,cat,fish,sum(fish) from r group by name,cat,fish with rollup;
+------+------+------+-----------+
| name | cat? | fish | sum(fish) |
+------+------+------+-----------+
| 小紅 | x? ? | 1? ? |? ? ? ? 1 |
| 小紅 | x? ? | 3? ? |? ? ? ? 3 |
| 小紅 | x? ? | NULL |? ? ? ? 4 |
| 小紅 | y? ? | 1? ? |? ? ? ? 1 |
| 小紅 | y? ? | 2? ? |? ? ? ? 2 |
| 小紅 | y? ? | NULL |? ? ? ? 3 |
| 小紅 | NULL | NULL |? ? ? ? 7 |
| 小明 | x? ? | 1? ? |? ? ? ? 1 |
| 小明 | x? ? | 2? ? |? ? ? ? 2 |
| 小明 | x? ? | 4? ? |? ? ? ? 4 |
| 小明 | x? ? | NULL |? ? ? ? 7 |
| 小明 | y? ? | 2? ? |? ? ? ? 4 |
| 小明 | y? ? | NULL |? ? ? ? 4 |
| 小明 | z? ? | 1? ? |? ? ? ? 1 |
| 小明 | z? ? | NULL |? ? ? ? 1 |
| 小明 | NULL | NULL |? ? ? ? 12 |
| 小青 | y? ? | 3? ? |? ? ? ? 3 |
| 小青 | y? ? | NULL |? ? ? ? 3 |
| 小青 | z? ? | 1? ? |? ? ? ? 1 |
| 小青 | z? ? | 2? ? |? ? ? ? 2 |
| 小青 | z? ? | NULL |? ? ? ? 3 |
| 小青 | NULL | NULL |? ? ? ? 6 |
| NULL | NULL | NULL |? ? ? ? 25 |
+------+------+------+-----------+
23 rows in set (0.07 sec)
————————————————
指定測試結(jié)果表中根據(jù) case_id 和 sort 字段進行分組，取同一分組中的執(zhí)行最小時間作為該條用例的開始執(zhí)行時間，取同一分組中的執(zhí)行最大時間作為該條用例的結(jié)束執(zhí)行時間。
因為一個測試集可能有多條相同用例，用 sort 作為區(qū)分，case_id 和 sort 兩個字段一起可以唯一確定執(zhí)行的是哪一條用例，因此這里需要指定兩個字段。
執(zhí)行結(jié)果如下圖所示：
根據(jù) case_id 和 sort 字段進行分組，每個分組中取最大狀態(tài)值作為用例的最終狀態(tài)。
因為一條用例可能有多個設(shè)備執(zhí)行，每個設(shè)備返回的狀態(tài)值可能不同，這里取最大值作為整條用例的最終狀態(tài)值。
執(zhí)行結(jié)果如下圖所示：
mysql
與
oracle
中分組、聚合函數(shù)的區(qū)別！
今天需要這樣一句
sql
：先用
group by
進行分組，然后利用聚合函數(shù)
count
或者
sum
進行計算，并顯示
其它的輔助信息。
在
mysql
環(huán)境中，我模擬如下環(huán)境：
CREATE TABLE `room` (
`rid` varchar(5) default NULL,
`rname` varchar(5) default NULL,
`pid` int(11) default NULL,
`seq` int(11) NOT NULL auto_increment,
PRIMARY KEY
(`seq`)
) ENGINE=InnoDB DEFAULT
CHARSET=utf8
房間表，
seq
房間入住序號
（主鍵）
，
rname
為房間名，這里不考慮第三范式
情景：人住房間，
統(tǒng)計某個房間某個人住的次數(shù)
用戶表，客人的信息
CREATE TABLE `user1` (
`ID` int(11) NOT NULL auto_increment,
`USERNAME` varchar(50) default '',
`PASSWORD` varchar(50) default '',
PRIMARY KEY
(`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk
Mysql
中語句如下：
select count(u.username)
，
r.rname
,r.rid,r.pid
from room r,user1 u
where r.pid=u.id
group by r.rid,r.pid
這里
r.rname
并沒有出現(xiàn)在
group by
子句、聚合函數(shù)中，但是
MYSQL
中仍然能夠執(zhí)行、列
出數(shù)據(jù)。
但是，在
ORACLE
中，卻不能！
！
！
！
Oracle
環(huán)境中：
/*
--
顯示：
Ora-00979 not a ORDER BY expression
--
因為：
order by
后邊的
c.channel_code
不在
ORDER BY
子句中
select count(c.channel_name),m.media_name
from channel c,media m
where c.media_code = m.media_code
group by c.media_code,m.media_name
order by
c.channel_code
--
顯示：
Ora-00979 not a GROUP BY expression
--
因為：
group by
或者聚合函數(shù)中沒有包含
c.channel_name
select count(c.channel_name),m.media_name,
c.channel_name
from channel c,media m
▲ count(*)
統(tǒng)計查詢結(jié)果集返回的行數(shù).
▲ count(ve)
統(tǒng)計值表達式返回的非空值的個數(shù).
▲ count(distinct ve)
統(tǒng)計值表達式返回的非空不同值的個數(shù).
▲ sum(ve)
統(tǒng)計值表達式返回的非空值的和.
▲ sum(distinct ve)
統(tǒng)計值表達式返回的非空不同值的和.
▲ avg(ve)
統(tǒng)計值表達式返回的非空值的平均值.
▲ avg(distinct ve)
統(tǒng)計值表達式返回的非空不同值的平均值.
▲ max(ve)
統(tǒng)計值表達式返回的非空值的最大值.
▲ min(ve)
統(tǒng)計值表達式返回的非空值的最小值.
由group by子句來實現(xiàn)的.
語法:
(1)分組的引入
(2)分組定義
指定某列或某些列作為劃分的依據(jù),比較這些列值是否相同,具有相同列值的行放在同一組,這樣就可以將最初的結(jié)果集劃分為若干個子集,每個子集稱為一個分組.
一般來說有多少個不同的列值就可以分為多少組.
語法:
指定某列或計算列作為統(tǒng)計的對象,將統(tǒng)計函數(shù)作用在該對象上,統(tǒng)計函數(shù)按照分組自動對每一分組的列值進行統(tǒng)計,每一分組返回一個統(tǒng)計后的結(jié)果.
(1)MYSQL對GROUP BY的非ANSI標準擴展
是由having子句來實現(xiàn)的.
語法:
(1)having子句中的子查詢
如何分組數(shù)據(jù)，以便能匯總表內(nèi)容的子集。這涉及兩個新SELECT語句子句，分別是GROUP BY子句和HAVING子句。
分組允許把數(shù)據(jù)分為多個邏輯組，以便能對每個組進行聚集計算。
分組是在SELECT語句的GROUP BY 子句中建立的。
來看例子理解：
mysqlselect vend_id,COUNT(*) AS num_prods from products group by vend_id;
也就是不同的Id的商品總數(shù)都能分別查出來。
除了能用GROUP BY分組數(shù)據(jù)外，Mysql還允許過濾分組，規(guī)定包括哪些分組，排除哪些分組。
也就是HAVING子句。
mysqlselect cust_id,COUNT( /) AS orders from orders uGROUP BY/u cust_id uHAVING/u COUNT( /) =2;
注意：這里HAVING換成WHERE是不管用的。HAVING針對于分組。
WHERE在數(shù)據(jù)分組前進行過濾，HAVING在數(shù)據(jù)分組后進行過濾。
那么咱么看看怎么混合WHERE和HAVING。
mysqlselect vend_id, COUNT( / ) AS num_prods from products uwhere prod_price=10 group by/u vend_id HAVING COUNT( /) =2;
mysqlselect order_num,SUM(quantity*item_price) AS ordertotal
from orderitems
GROUP BY order_num
HAVING SUM(quantity*item_price) =50
order by ordertotal;
前兩天同事有個 MySQL 數(shù)據(jù)分組的需求，如下測試數(shù)據(jù)，需要找出每個 name 分組中 create_date 最近的記錄：
需要注意的是，此處用的 MySQL 是5.6，最初是使用這條語句：
pre class="custom" data-tool="mdnice編輯器" style="margin-top: 10px; margin-bottom: 10px; border-radius: 5px; box-shadow: rgba(0, 0, 0, 0.55) 0px 2px 10px;" select name, value, create_date, update_date from t1 group by name order by create_date desc; /pre
用這條 SQL 得到的其實只是每個 name 分組中最先插入的記錄，然后按照 create_date 進行了降序排列，和原始需求，完全不同。
此時可采用分而治之的策略，先做排序，再做分組：
pre class="custom" data-tool="mdnice編輯器" style="margin-top: 10px; margin-bottom: 10px; border-radius: 5px; box-shadow: rgba(0, 0, 0, 0.55) 0px 2px 10px;" select * from (select name, value, create_date, update_date from t1 order by create_date desc) t group by t.name; /pre
當然，針對此需求，可能有其他方法，有興趣的朋友，可以嘗試寫寫，共享一下。
可能有細心的朋友會發(fā)現(xiàn)個問題，就是上述 SQL 中的 group by ，好像有些奇怪，如果按照常規(guī)，select 中的字段需要出現(xiàn)在 group by 中，上述語句竟然沒報錯？
如果我們在 MySQL 5.7 執(zhí)行相同的語句：
pre class="custom" data-tool="mdnice編輯器" style="margin-top: 10px; margin-bottom: 10px; border-radius: 5px; box-shadow: rgba(0, 0, 0, 0.55) 0px 2px 10px;" select name, value, create_date, update_date from t1 group by name order by create_date desc; /pre
因此從5.6升級到5.7，很可能出現(xiàn)這種相同的 SQL 執(zhí)行結(jié)果不同的現(xiàn)象，這對兼容性測試的要求就會很高，究其原因，一方面是特性決定的，另一方面就是各種配置參數(shù)不同導致的。
可以在5.7的 sql_mode 中刪除這個 ONLY_FULL_GROUP_BY ，即可達到5.6相同效果了，或者改寫 SQL ，例如：
pre class="custom" data-tool="mdnice編輯器" style="margin-top: 10px; margin-bottom: 10px; border-radius: 5px; box-shadow: rgba(0, 0, 0, 0.55) 0px 2px 10px;" select * from t1 a where create_date = (select max(create_date) from t1 b where a.name = b.name); /pre
或者，
pre class="custom" data-tool="mdnice編輯器" style="margin-top: 10px; margin-bottom: 10px; border-radius: 5px; box-shadow: rgba(0, 0, 0, 0.55) 0px 2px 10px;" select * from t1 a where not exists (select * from t1 b where a.name = b.name and b.create_date a.create_date); /pre
MySQL 8.0支持 row_number()函數(shù)，操作應該和如下 Oracle 相近的。
Oracle 中可以使用 row_number()實現(xiàn)此需求：
pre class="custom" data-tool="mdnice編輯器" style="margin-top: 10px; margin-bottom: 10px; border-radius: 5px; box-shadow: rgba(0, 0, 0, 0.55) 0px 2px 10px;" select * from (select name, create_date, row_number() over (partition by name order by create_date desc) as r from t1) where r=1; /pre
重慶分公司
重慶分公司
