MySQL 工作笔记

DROP TABLE dispatch_result;
CREATE TABLE dispatch_result (
dr_id INT NOT NULL AUTO_INCREMENT,
sheet_id INT,
check_next VARCHAR (20),
check_time DATE,
check_sn CHAR(8),
check_comment VARCHAR (255),
check_status INT,
PRIMARY KEY(dr_id)
);

插入数据：

insert into `dispatch_result` (`dr_id`, `sheet_id`, `check_next`, `check_time`, `check_sn`, `check_comment`, `check_status`) values('1','1','10001','2015-08-12','23','10000','1');
insert into `dispatch_result` (`dr_id`, `sheet_id`, `check_next`, `check_time`, `check_sn`, `check_comment`, `check_status`) values('2','1','10002','2015-09-15','24','10001','2');
insert into `dispatch_result` (`dr_id`, `sheet_id`, `check_next`, `check_time`, `check_sn`, `check_comment`, `check_status`) values('3','2','10001','2015-08-15','10','10000','1');
insert into `dispatch_result` (`dr_id`, `sheet_id`, `check_next`, `check_time`, `check_sn`, `check_comment`, `check_status`) values('4','2','10002','2015-09-18','23','10001','2');

SELECT * FROM dispatch_result;

#查询以SHEET_ID 分组取最大时间记录：
#第一种：子查询的方式
SELECT * FROM dispatch_result dr WHERE dr.check_time=
(SELECT MAX(check_time) FROM dispatch_result WHERE sheet_id=dr.sheet_id)

#第二种：group by方式：
SELECT t1.* FROM dispatch_result t1 RIGHT JOIN
(SELECT sheet_id,MAX(check_time) ct FROM dispatch_result GROUP BY sheet_id) t2
ON t2.sheet_id=t1.sheet_id AND t2.ct=t1.check_time
# 或者
SELECT * FROM dispatch_result
INNER JOIN (SELECT MAX(dr.check_time) AS check_time,dr.sheet_id FROM dispatch_result dr GROUP BY dr.sheet_id)t1
USING(check_time,sheet_id);

参考资料：

http://yingyu.100xuexi.com/VIEW/otdetail/20130103/2f09e72c-b406-4b92-99a5-7a3eb50d0efb.html

MySQL 自连接分组取每组最大N条记录
http://my.oschina.net/leejun2005/blog/78904

获取分组后取某字段最大一条记录（求每个类别中最大的值的列表）
http://www.2cto.com/database/201502/376690.html

研究一下这条 SQL 语句：

# 查询按照 sentence_id 分组以后 id 最大的那条记录
方法一：(效率最高)
SELECT * FROM u_user_sentence_repeat_recording AS a 
WHERE id = (SELECT MAX(b.id) 
FROM u_user_sentence_repeat_recording AS b 
WHERE a.sentence_id = b.sentence_id );

我根据这条 SQL 的思路

SELECT * FROM (SELECT * FROM t2 ORDER BY gid,col2 DESC) t GROUP BY gid;

写出了下面这条 SQL：

SELECT * FROM(
SELECT 
    rrs.id,
    rrs.article_id article_id,
    rrs.page_num pageNum,
    rrs.id sentence_id,
    rrs.pic,
    rrs.content,
    rrs.recording_path,
    uusrr.user_id,
    uusrr.repeat_recording_path,
    uusrr.speech_recognition_text,
    uusrr.diff_result,
    uusrr.correct_rate,
    uusrr.recording_time,
    uusrr.id record_id
    FROM r_resource_sentence rrs 
    LEFT JOIN u_user_sentence_repeat_recording uusrr ON rrs.id = uusrr.sentence_id
    WHERE rrs.article_id = '120' 
    ORDER BY sentence_id, record_id DESC) t GROUP BY  t.sentence_id

# SQL 语句分组查询高级功能学习 
# 参考资料：http://my.oschina.net/leejun2005/blog/78904

SELECT * FROM t2 ORDER BY gid,col2 DESC

SELECT * FROM t2 a
WHERE NOT EXISTS
(SELECT 1 FROM t2 WHERE gid=a.gid AND col2>a.col2);

SELECT * FROM (SELECT * FROM t2 ORDER BY gid,col2 DESC) t GROUP BY gid;

SELECT * FROM t2 a WHERE 
3>(SELECT COUNT(*) FROM t2 WHERE gid=a.gid AND col2>a.col2) 
ORDER BY a.gid,a.col2 DESC;

SELECT id ,user_id, FROM_UNIXTIME(recording_time) FROM u_user_sentence_repeat_recording;