關于order by,group by 你需要注意這些

一、需求

我們先來說說，實際開發中遇到的需求，多個學校，一個學校多個班級，學校學生上學、放學經過校門，智能手環會自動打卡，上傳當前學生體溫，教育局需要查看某校某班當天的體溫詳情，若當天該學生體溫有出現例外情況，則顯示例外的體溫，若該學生體溫正常，則顯示最新的體溫資料，

二、資料庫

為了方便大家理解，在這里簡化了資料庫，資料庫欄位如下：

CREATE TABLE `oa_student_log` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `corp_id` varchar(24) DEFAULT NULL,
  `department_id` int(11) DEFAULT NULL COMMENT '班級ID',
  `student_id` bigint(20) DEFAULT NULL COMMENT '學生id',
  `student_name` varchar(64) DEFAULT NULL COMMENT '學生姓名',
  `rule_name` varchar(100) DEFAULT NULL COMMENT '規則名稱',
  `rule_item_id` bigint(20) DEFAULT NULL COMMENT '規則項id',
  `is_abnormal` int(2) DEFAULT NULL COMMENT '是否例外0-正常1-例外',
  `temperature` decimal(11,2) DEFAULT NULL COMMENT '溫度',
  `date` date DEFAULT NULL COMMENT '打卡日期',
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_student` (`student_id`) USING BTREE,
  KEY `idx_date` (`date`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COMMENT='學生考勤記錄表';

INSERT INTO `oa_student_log`(`id`, `corp_id`, `department_id`, `student_id`, `student_name`, `rule_name`, `rule_item_id`, `is_abnormal`, `temperature`, `date`, `create_time`, `update_time`) VALUES (1, '1', 34, 1, 'zzb', '上午進校考勤', 11, 0, 36.00, '2020-07-21', '2020-07-21 09:00:46', '2020-07-21 09:00:50');
INSERT INTO `oa_student_log`(`id`, `corp_id`, `department_id`, `student_id`, `student_name`, `rule_name`, `rule_item_id`, `is_abnormal`, `temperature`, `date`, `create_time`, `update_time`) VALUES (2, '1', 34, 1, 'zzb', '上午離校考勤', 12, 0, 36.50, '2020-07-21', '2020-07-20 11:59:46', '2020-07-21 11:59:50');
INSERT INTO `oa_student_log`(`id`, `corp_id`, `department_id`, `student_id`, `student_name`, `rule_name`, `rule_item_id`, `is_abnormal`, `temperature`, `date`, `create_time`, `update_time`) VALUES (3, '1', 34, 1, 'zzb', '下午進校考勤', 13, 1, 37.50, '2020-07-21', '2020-07-21 13:59:46', '2020-07-21 13:59:50');
INSERT INTO `oa_student_log`(`id`, `corp_id`, `department_id`, `student_id`, `student_name`, `rule_name`, `rule_item_id`, `is_abnormal`, `temperature`, `date`, `create_time`, `update_time`) VALUES (4, '1', 34, 1, 'zzb', '下午離校考勤', 14, 0, 36.40, '2020-07-21', '2020-07-21 16:59:46', '2020-07-21 16:59:50');
INSERT INTO `oa_student_log`(`id`, `corp_id`, `department_id`, `student_id`, `student_name`, `rule_name`, `rule_item_id`, `is_abnormal`, `temperature`, `date`, `create_time`, `update_time`) VALUES (5, '1', 34, 2, 'zzc', '上午進校考勤', 11, 0, 36.50, '2020-07-21', '2020-07-21 09:00:46', '2020-07-21 09:00:50');
INSERT INTO `oa_student_log`(`id`, `corp_id`, `department_id`, `student_id`, `student_name`, `rule_name`, `rule_item_id`, `is_abnormal`, `temperature`, `date`, `create_time`, `update_time`) VALUES (6, '1', 34, 2, 'zzc', '上午離校考勤', 12, 0, 36.59, '2020-07-21', '2020-07-20 11:59:46', '2020-07-21 11:59:50');
INSERT INTO `oa_student_log`(`id`, `corp_id`, `department_id`, `student_id`, `student_name`, `rule_name`, `rule_item_id`, `is_abnormal`, `temperature`, `date`, `create_time`, `update_time`) VALUES (7, '1', 34, 2, 'zzc', '下午進校考勤', 13, 0, 36.80, '2020-07-21', '2020-07-21 13:59:46', '2020-07-21 13:59:50');
INSERT INTO `oa_student_log`(`id`, `corp_id`, `department_id`, `student_id`, `student_name`, `rule_name`, `rule_item_id`, `is_abnormal`, `temperature`, `date`, `create_time`, `update_time`) VALUES (8, '1', 34, 2, 'zzc', '下午離校考勤', 14, 0, 36.40, '2020-07-21', '2020-07-21 16:59:46', '2020-07-21 16:59:50');

篩選后表中資料如下：

實作目標：
同時取出zzb中的發熱的資料與取出zzc中的最新資料即下午離校考勤的資料，

三、sql陳述句及踩坑

SELECT
	* 
FROM
	oa_student_log o 
WHERE
	o.corp_id = 1 AND o.department_id = 34 AND o.date = '2020-07-21'
GROUP BY
	o.student_id
ORDER BY
    o.is_abnormal DESC,o.update_time DESC 

運行結果：

發現報錯了，大概意思就是說啟用了only_full_group_by SQL模式，那么MySQL就會拒絕選擇串列、條件或順序串列參考的查詢，這些查詢將參考組中未命名的非聚合列，而不是在功能上依賴于它們，也就是說，使用了group by,那么select的列都要在group中，或者是其他聚合列（SUM,MAX等等）
原因是：
MySQL 5.7.5和up實作了對功能依賴的檢測，(在5.7.5之前，MySQL沒有檢測到功能依賴項，only_full_group_by在默認情況下是不啟用的，）
找到了問題的所在，我們開始解決問題：
（1） 首先我們查看sql_mode的值，在sql中執行下列命令：

SHOW SESSION VARIABLES
SHOW GLOBAL VARIABLES

結果：我們發現執行緒變數跟全域變數sql_mode都是為only_full_group_by

（2）接下來，我們只需要去掉默認的only_full_group_by即可，在sql中執行下面陳述句：

set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

（3）再次執行，先前的代碼，結果如下：

發現！！結果錯了！再來分析一下SQL陳述句：
思考：Order By沒有起作用，SQL陳述句去掉Order By之后重新執行陳述句，發現結果相同！
原因：SQL陳述句的執行順序，是from,where,Group By,然后才執行Select,Order By.

解決：先排序，再分組，

1. 我們很容易想到，直接把排序好的資料作為查詢的表，然后再進行分組

select *
from (SELECT * 
	  FROM oa_student_log o 
	  WHERE
	  o.corp_id = 1 AND o.department_id = 34 AND o.date = '2020-07-21'
      OrDer  BY
	  o.is_abnormal DESC,o.update_time DESC ) AS a
GROUP BY a.student_id

結果：

我們發現：結果還是錯誤的！！！
原因：5.7版本的MySql在執行這條sql時缺少了一個derived操作，通過查閱相關資料了解到MySql 5.7對子查詢進行了優化，認為子查詢中的order by可以進行忽略，只要Derived table里不包含如下條件就可以進行優化：

1、UNION clause
2、GROUP BY
3、DISTINCT
4、Aggregation
5、LIMIT or OFFSET

2. 通過第一種方法我們可以知道，超過5.7版本的MySql我們可以通過增加限制條件來實作，執行代碼如下：

select *
from (SELECT * 
	  FROM
		oa_student_log o 
	  WHERE
		o.corp_id = 1 AND o.department_id = 34 AND o.date = '2020-07-21'
      OrDer BY o.is_abnormal DESC,o.update_time DESC limit 9999) AS a
GROUP BY a.student_id

結果：

結果正確！

3. 其實還有一種方法，但是這種方法不是通用的！具體問題具體分析

select *
from (SELECT * 
	  FROM
		oa_student_log o 
	  WHERE
		o.corp_id = 1 AND o.department_id = 34 AND o.date = '2020-07-21'
	  GROUP BY
		o.is_abnormal DESC,o.student_id,o.update_time DESC) AS a
GROUP BY a.student_id

先根據體溫是否例外進行降序分組，這樣的話例外體溫會優先在前面顯示，再對學生id進行分組，這樣可以將學生區分開，最后對同一個學生同體溫狀態的情況，進行按時間排序，輸出結果為：

結果正確