sql 语句，先降后分组

最新推荐文章于 2023-08-08 23:15:10 发布

转载最新推荐文章于 2023-08-08 23:15:10 发布 · 117 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：https://my.oschina.net/u/2415642/blog/736212

文章标签：

#python

2019独角兽企业重金招聘Python工程师标准>>>

体温表：

-- 体温: Temperature
DROP TABLE IF EXISTS Temperature;
CREATE TABLE Temperature
(
ID_Card				VARCHAR(30) NOT NULL			COMMENT '身份证号',
Time			    DATETIME NOT NULL           	COMMENT '测量时间',
Temperature			FLOAT NOT NULL			        COMMENT '体温',
Dtype               VARCHAR(100)                    COMMENT '设备类型',
Did                 VARCHAR(100)                    COMMENT '设备编码',
Medic_Id            VARCHAR(100)                    COMMENT '医生ID',
Note				TEXT		 					COMMENT '备注',
CONSTRAINT PK_Temperature PRIMARY KEY(ID_Card,Time)
);
ALTER TABLE Temperature COMMENT  = '体温';

插入数据：

INSERT INTO temperature VALUES (11, '5329321990011', '2016-8-11 12:14:34', 11, NULL, NULL, NULL, NULL);
INSERT INTO temperature VALUES (13, '5329321990011', '2016-8-11 15:43:10', 21, NULL, NULL, NULL, NULL);
INSERT INTO temperature VALUES (14, '5329321990011', '2016-8-11 15:52:10', 11, NULL, NULL, NULL, NULL);
INSERT INTO temperature VALUES (15, '5329321990011', '2016-8-12 12:14:34', 2, NULL, NULL, NULL, NULL);
INSERT INTO temperature VALUES (16, '5329321990011', '2016-8-12 13:39:52', 333, NULL, NULL, NULL, NULL);
INSERT INTO temperature VALUES (17, '5329321990011', '2016-8-12 16:48:37', 1.2, NULL, NULL, NULL, NULL);
INSERT INTO temperature VALUES (21, '5329321990011', '2016-8-12 17:58:39', 42, NULL, NULL, NULL, NULL);
INSERT INTO temperature VALUES (24, '5329321990011', '2016-8-19 09:51:12', 100, NULL, NULL, NULL, NULL);
INSERT INTO temperature VALUES (24, '5329321990011', '2016-8-19 09:51:32', 101, NULL, NULL, NULL, NULL);
INSERT INTO temperature VALUES (24, '5329321990011', '2016-8-19 09:52:12', 103, NULL, NULL, NULL, NULL);
INSERT INTO temperature VALUES (24, '5329321990011', '2016-8-19 09:53:12', 107, NULL, NULL, NULL, NULL);
INSERT INTO temperature VALUES (25, '5301211998012', '2016-8-18 17:34:37', 55, NULL, NULL, NULL, NULL);
INSERT INTO temperature VALUES (26, '5329011995112', '2016-8-16 10:17:19', 12313, NULL, NULL, NULL, NULL);
INSERT INTO temperature VALUES (27, '5329011995112', '2016-8-18 11:47:24', 22, NULL, NULL, NULL, NULL);

需求：查询出8月里每天最新的一条数据 1、第一步先降序：

SELECT * FROM temperature ORDER BY Time DESC

2、第二步：排序后分组，GROUP BY time，用substr()函数截取时间后格式为yyyyy-mm-dd，这样就变成了按天进行分组，GROUP BY后默认显示每组中的第一条数据。如下

SELECT * FROM 
(SELECT * FROM temperature ORDER BY Time DESC) t1 WHERE t1.time>='2016-08-01 00:00:00' AND t1.time<'2016-09-01 00:00:00' GROUP BY substr(t1.time, 1, 10)

查询查来的数据如下：执行sql查询出来的数据